Tuesday, November 20, 2012

Getting data out of XML as table in T-SQL


I had XML output of a table in XML variable and wanted to get the same table back again. You would find lots of questions and articles on this subject that all assume that you already know the column names, path etc to get the column values.

But what if you don't? There is hardly any reference of examples. And MS Documentation is just too much text without any value.

So here is a simple example of doing that.

declare @x xml
set @x = (select top 10 col1, col2, col3 From My.SampleTable for xml auto)
--PRINT convert(varchar(max), @x)






select t.c1.value('data(attribute::node()[1])', 'varchar(max)'), t.c1.value('data(attribute::node()[2])', 'varchar(max)')
from @x.nodes('child::node()') t(c1)

In above we don't have to know what the table name is and what the column names are. Here its assumed that the XML is a simple XML that has all nodes at the same level. Still what you have to do know is the number of columns that you want out of it. In above example, it has 3 columns in source table, but the result tries to fetch only two columns out of XML.

Following links were referred:
http://msdn.microsoft.com/en-us/library/ms190687.aspx
http://msdn.microsoft.com/en-us/library/ms177470.aspx
http://msdn.microsoft.com/en-us/library/ms191541.aspx

If you go through these MS document, you will have to read through too much text to find out what you need and their examples were not useful.