Create XML using SQL Query in SQL Server 2008

Here, we will learn how to create XML using Query:

SELECTTOP1000 [Code]
      ,[Name]
      ,[Price]
  FROM[Temp].[dbo].[Product]
  FORXML 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.
Results:
<Products>
<ProductCode="1"Name="Computer"Price="100.0000"/>
<ProductCode="2"Name="Mobile"Price="250.0000"/>
</Products>
SQL ELEMENTS create Elements instead of Attributes in xml
SELECTTOP1000 [Code]
      ,[Name]
      ,[Price]
  FROM[Temp].[dbo].[Product]
  FORXML RAW ('Product'),
ROOT ('Products'),
ELEMENTS;
RESULTS :
<Products>
<Product>
  <Code>1</Code>
  <Name>Computer</Name>
  <Price>100.0000</Price>
</Product>
<Product>
  <Code>2</Code>
  <Name>Mobile</Name>
  <Price>250.0000</Price>
</Product>
</Products>

 

Leave a Reply

Your email address will not be published. Required fields are marked *