检索多个 xml 子节点值 [英] Retrieving multiple xml child node values

问题描述

我有一列 varchar(max) 填充了 xml 节点和值；例如，列数据以 开头.<tag2>value1</tag2><tag3>value2</tag3>... </tag1>.对于使用静态 SQL 或存储过程的表中的每一行，我需要从这个字符串中获得一个单元格中的value1 value2 value3... valueN".节点树并不总是相同的，有时路径是valueY....

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>.

我在粉碎 xml 方面的所有经验仅用于获取一个特定的值、属性或标记，而不是在保留列和行数的同时获取所有值.目前我查询然后循环遍历我产品端的结果集并粉碎所有内容，但由于最近的变化，这不再是一个选项.

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.

可以将列更改为 xml 类型，但如果可能，我希望避免这样做.

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

推荐答案

Cast the column to XML (or the table in the table to change to XML) and shred the xml on //*表中的节点.然后您可以使用 for xml path 将值重新连接在一起.

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 小提琴

这篇关于检索多个 xml 子节点值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！