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 (1)

  1. sourav says:

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