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.

回答1:

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

 SELECT ID
       ,TheXmlColumn
 FROM YourTable

This returns the ID and a value out of your XML

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

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

 SELECT ID
       ,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:

 SELECT ID
       ,TheXmlColumn.value('(Parameters/Parameter/Value)[1]','nvarchar(max)') AS SomeValueFromXML
 FROM YourTable