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.


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:


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

Comments (2)
  1. sourav says:

    can you help me in creating dynamic menu from my sql database using xml and mvc

  2. Bruce says:

    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)

Comments are closed.

Skip to main content