MPJ567 MPJ567 - 1 year ago 95
SQL Question

Shred XML For Each Row in SQL Table

I have a table that contains two columns, and ID, and XML data. I'd like to shred the XML for each ID. I'm pulling out a single value within the XML and all the XML is structured the same I'm just not sure how to loop through the table and apply XML query to each row.

The query I need to apply is as follows:

Select top 1
Element1 = XV.value('(.)[1]','nvarchar(32)')
from @xml.nodes('Parameters/Parameter/Value') as x(XV)

So the end results would have two columns, ID and shredded value from XML.

Answer Source

Without any knowledge about your actual XML and how you want to shred it to get some values it is impossible to answer in completness, but this shoudl point you in the right direction:

Returns the ID and the XML as is

 FROM YourTable

This returns the ID and a value out of your XML

       ,TheXmlColumn.value('Some XPaht','SomeType') AS SomeValueFromXML
 FROM YourTable

And if there are more embedded rows it would be something like this

       ,nd.value('Some XPaht','SomeType') AS SomeValueFromXMLRow
 FROM YourTable
 OUTER APPLY TheXmlColumn.nodes('SomeXPath') AS A(nd)

My magic glass bulb tells me, that you might need something like this:

       ,TheXmlColumn.value('(Parameters/Parameter/Value)[1]','nvarchar(max)') AS SomeValueFromXML
 FROM YourTable
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download