Here we are going to look into the different ways to generate an XML output with the results of a query.
Generally the query output is resulted as Table. Those results can be transformed to XML format by using the FOR XML TSQL keyword. FOX XML need to be used with following keywords PATH, AUTO, RAW or EXPLICIT
The basic way to return values in xml format is to use FOR XML with AUTO keyword
QUERY:
SELECT OrderId, ProductId, Quantity FROM [Order Details] FOR XML AUTO
OUTPUT:
<Order_x0020_Details OrderId="10248" ProductId="11" Quantity="12"/>
<Order_x0020_Details OrderId="10248" ProductId="42" Quantity="10"/>
<Order_x0020_Details OrderId="10248" ProductId="72" Quantity="5"/>
There are various attributes to be used along with FOR XML <AUTO/RAW/PATH/EXPLICIT>
TYPE:
Above the output is XML format, however the output datatype is nvarchar, TYPE keyword is use dto return the results as an XML data type
ELEMENTS:
In the above output all the column values are returned as attributes, with table name as tag name, ELEMENTS tag helps in getting the column values as nodes.
ROOT:
Syntax ROOT [('RootName')]
Every XML document need to have a root element, ROOT attribute helps in defining a root node in the output
QUERY:
SELECT OrderId, ProductId, Quantity FROM [Order Details]
FOR XML AUTO, TYPE, ELEMENTS, ROOT('Orders')
OUTPUT:
<Orders>
<Order_x0020_Details>
<OrderId>10248</OrderId>
<ProductId>11</ProductId>
<Quantity>12</Quantity>
</Order_x0020_Details>
<Order_x0020_Details>
<OrderId>10248</OrderId>
<ProductId>42</ProductId>
<Quantity>10</Quantity>
</Order_x0020_Details>
<Order_x0020_Details>
<OrderId>10248</OrderId>
<ProductId>72</ProductId>
<Quantity>5</Quantity>
</Order_x0020_Details>
</Orders>
|
No responses found. Be the first to respond and make money from revenue sharing program.
|