CODEWITHSUNDEEP
sqlsql-serverxmlstored-procedures
Dan Mills
Dan Mills

Reputation: 13

Retrieving multiple xml child node values

I have a column of type varchar(max) populated with xml nodes and values; as an example, the column data starts with <tag1> <tag2>value1</tag2><tag3>value2</tag3>... </tag1>. What I need to get out of this string is "value1 value2 value3... valueN" within one cell for every row in the table using static SQL or a stored procedure. The node tree isn't always the same, sometimes the path is <tagX><tagY>valueY</tagY>...</tagX>.

All of my experience with shredding xml is only used to get one specific value, property, or tag, not all values while retaining the column and row count. Currently I query then loop through the result set on my product's end and shred everything, but that's no longer an option due to recent changes.

It's possible to change the column to be of type xml, but if possible I'd like to avoid having to do so.

Upvotes: 1

Views: 1220

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Cast the column to XML (or change it in the table to XML) and shred the xml on //* to get all nodes in a table. Then you can use for xml path to concat the values back together.

select (
       select ' '+X.N.value('text()[1]', 'varchar(max)')
       from (select cast(T.XMLCol as xml)) as T1(XMLCol)
         cross apply T1.XMLCol.nodes('//*') as X(N)
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)')
from T

SQL Fiddle

Upvotes: 1

Related Questions