次の方法で共有

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:

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

Comments

  • 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)