Shred XML For Each Row in SQL Table

Question

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.

if you want to access this repeatedly, you can speed it up by adding a computed column to your table that pre-shreds the value. msdn.microsoft.com/en-us/library/bb500236(v=sql.120).aspx — Zabi
– Zabi, Commented Nov 4, 2016 at 10:14

Gottfried Lesigang · Accepted Answer · 2016-11-04 09:26:17Z

2

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

answered Nov 4, 2016 at 9:26

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MPJ567 Over a year ago

The final suggestion was the one that work.

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

Collectives™ on Stack Overflow

Shred XML For Each Row in SQL Table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related