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:

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….

• Anonymous
  May 21, 2014
  can you help me in creating dynamic menu from my sql database using xml and mvc

• Anonymous
  June 26, 2017
  This is cool, thanks! Is there a method to squish xml data like this back into the sql db it came from? (assuming that only the data changed outside the system and there have been no changes to the schema)