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:
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>