Export SQL table records to XML form

  • Article

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.

clip_image001

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

clip_image002

Or

SELECT * from Saurabh.dbo.Customers

FOR XML RAW

clip_image003

Or

SELECT * from Saurabh.dbo.Customers

FOR XML PATH

Or

clip_image004

…..

Or

SELECT * from Saurabh.dbo.Customers

FOR XML PATH('Customer')

clip_image005

…….

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

clip_image006

….

….

clip_image007

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

Reference:

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

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

till next time….