Getting the results from a SQL query in an XML format

Posted: January 30, 2015 in Uncategorized

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s