Export SQL table records to XML form

I am not a SQL guy. But while working on something I found this which seemed cool to me. So thought of sharing it with folks in case you are not aware. Using SQL Management studio and running T-SQL command we can export the Database table entries into an XML form.

Let's say you run this SQL command in the SQL editor.

SELECT * from Saurabh.dbo.Customers

And we get the following output in the table.

Now in order to export the table content into an XML form we need to use FOR XML PATH as below:

SELECT * from Saurabh.dbo.Customers

FOR XML AUTO

Or

SELECT * from Saurabh.dbo.Customers

FOR XML RAW

Or

SELECT * from Saurabh.dbo.Customers

FOR XML PATH

Or

…..

Or

SELECT * from Saurabh.dbo.Customers

FOR XML PATH('Customer')

…….

If you want to wrap the content under a specific ROOT node use the following:

….

….

It may not be something new for the SQL folks but this was something new to me.

Reference:

http://msdn.microsoft.com/en-us/library/ms189885.aspx

http://theengineroom.provoke.co.nz/archive/2007/04/27/using-for-xml-path-a-primer.aspx

till next time….