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.
No comments:
Post a Comment