Retrieving multiple xml child node values

Question

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.

I can't show any data because it's PCI. I've tried declaring an xml variable and setting it to the column data (with the intention to loop through and shred while it contained characters '<' and '>') but because I need to do this on all rows in the column I got caught on multiple values being stored in it. I've considered modify() but due to the variable nature of the node path neither delete nor value will work because I'd need to supply a set path (and delete doesn't preserve values). My experience with xml is fairly limited so I'm not aware of other possible solutions. — Dan Mills
– Dan Mills, Commented Jan 22, 2014 at 21:41

Mikael Eriksson · Accepted Answer · 2014-01-23 06:58:31Z

1

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

answered Jan 23, 2014 at 6:58

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Collectives™ on Stack Overflow

Retrieving multiple xml child node values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related