Archive for January, 2015

For one of my projects I had to get the results of the sql query in an XML format.  I knew SQL Server supported the feature to get results in XML but I didn’t know how to do that. Googled it and found the easy solution.  Append  “FOR XML PATH(‘Path Name’), ROOT (‘root name’)”  

My actual query is a rather convoluted one but suppose there is a table called tbl_documents and I have to get all the data in that table in an xml format. I would use the query

select * from tbl_document FOR XML PATH(‘Document’), ROOT (‘Documents’)

The result would look like this

 

<Documents>
<Document>
<document_id>1</document_id>
<document_order>82</document_order>
<document_parent_id>0</document_parent_id>
<document_display>0</document_display>
<document_name>Doc1</document_name>
</Document>
<Document>
<document_id>2</document_id>
<document_order>81</document_order>
<document_parent_id>0</document_parent_id>
<document_display>0</document_display>
<document_name>Doc2</document_name>
</Document>

</Documents>

 

 

Advertisements