Is there a quick way to convert Xml into a Table?

Hello,

I have an Xml column containing some Xml downloaded off the web (which comes from a Sql Server 2000 FOR XML query). I want to quickly convert the Xml back into a table with the relevant columns. Is there a quick way to do this?

Many thanks!

Ben S.

SELECT ncol.value('@.someAttrib')

,ncol.value('../@.AttribOfParent')

FROM yourtable

CROSS APPLY yourtable.yourxmlcolumn.nodes('/xPathThatLeads/toWhatYouWant/') AS T(ncol)

This is the a quick way to shred your xml data back to relational data

|||

I did it this way in the end (using SQL Server 2005):

DECLARE @.xmlDoc XML, @.xmlDocHandle INT

--Get xml off the web and put it into the @.xmlDoc variable here

EXEC sp_xml_preparedocument @.xmlDocHandle OUTPUT, @.xmlDoc;

SELECT * INTO #temp_table FROM OPENXML(@.xmlDocHandle, N'/root/element', 2) WITH myTable;

EXEC sp_xml_removedocument @.xmlDocHandle;

This will put the contents of the @.xmlDoc variable into the #temp_table provided the xml matches the structure of the existing myTable table. The '/root/element' bit discribes the xml elements you want to put into the table. Eg.

<root>
<element>
<id>1</id>
<name>Fred Smith</name>
</element>
<element>
<id>2</id>
<name>Joe Bloggs</name>
</element>
</root>

The above example will return two records assuming myTable has id and name columns.

Hope this helps other people.

sql