Beginning SQL , SQL Server Scripts : Get XML of Table's Data using SQL Query

You can seen that XML is now every where to exchange data between multiple system and platform.
Here are sample query which retrieve XML of particular Table's data.
XML support is available on SQL Server 2000 with SQLXML 3.0 and its XML extensions, and There are in built  native XML data type support in SQLServer 2005 and 2008. 

With the help of FOR XML AUTO clause you can get XML of table's data.

Here are sample queries.

SQL Query :

SELECT * FROM product_master FOR XML AUTO 

XML AUTO returns XML fragments it does not returns a full XML document with a document element. Each row in the database becomes one element and each column in the database becomes one attribute on the element. You can see that each element in the result set is name product_master because the select clause is from product_master table.

Output XML File :

<product_master product_id="1" product_name="LCD" />
<product_master product_id="2" product_name="Processor" />
<product_master product_id="3" product_name="Cabinet" />
<product_master product_id="4" product_name="Headphone" />
<product_master product_id="5" product_name="USB" />

If you want result in Element and you also want "Product" as main element. So you have to use ELEMENT Query. Here is element Query.

SQL Query :

select * from product_master as Product FOR XML AUTO , ELEMENTS

In FOR XML AUTO , ELEMENTS return XML with a document element.

 Output XML File :

<Product>
  <product_id>1</product_id>
  <product_name>LCD</product_name>
</Product>
<Product>
  <product_id>2</product_id>
  <product_name>Processor</product_name>
</Product>
<Product>
  <product_id>3</product_id>
  <product_name>Cabinet</product_name>
</Product>
<Product>
  <product_id>4</product_id>
  <product_name>Headphone</product_name>
</Product>
<Product>
  <product_id>5</product_id>
  <product_name>USB</product_name>
</Product>

In Previous queries Return XML does not have Root Element. If you want XML structure like Root Element after that child elements.

SQL Query :

select * from product_master as Products FOR XML PATH ('Product'), TYPE, ROOT ('Products') 

In this query we are using PATH and ROOT element to make a full XML document.

Output XML File : 

<Products>
  <Product>
    <product_id>1</product_id>
    <product_name>LCD</product_name>
  </Product>
  <Product>
    <product_id>2</product_id>
    <product_name>Processor</product_name>
  </Product>
  <Product>
    <product_id>3</product_id>
    <product_name>Cabinet</product_name>
  </Product>
  <Product>
    <product_id>4</product_id>
    <product_name>Headphone</product_name>
  </Product>
  <Product>
    <product_id>5</product_id>
    <product_name>USB</product_name>
  </Product>
</Products>

In above all queries you can also use INNER JOIN , OUTER JOIN and other any type of select queries.

If you know any other way to implement this solution, Insert that in comment so I can add in this blog.