In this blog I will show how to create XML using Query:

SELECT TOP 1000 [Code]
      ,[Name]
      ,[Price]
  FROM [Temp].[dbo].[Product]
  FOR XML RAW ('Product'),
  ROOT ('Products');

Here SQL statement “FOR XML RAW” creates one XML node for every corresponding row in database and columns will be created as attributes of that XML node.
Output:

<Products>
<Product Code="1" Name="Tea" Price="10.0000"/>
<Product Code="2" Name="Coffee" Price="25.0000"/>
</Products>

SQL ELEMENTS create Elements instead of Attributes in xml

SELECT TOP 1000 [Code]
      ,[Name]
      ,[Price]
  FROM [Temp].[dbo].[Product]
  FOR XML RAW ('Product'),
ROOT ('Products'),
ELEMENTS;

Output:

<Products>
<Product>
  <Code>1</Code>
  <Name>Tea</Name>
  <Price>10.0000</Price>
</Product>
<Product>
  <Code>2</Code>
  <Name>Coffee</Name>
  <Price>25.0000</Price>
</Product>
</Products>