SQL Server解析XML同层级同名节点拆分为多行数据问题

场景说明 #

现有公开方案大多处理嵌套结构下，不同父节点对应子节点生成多行的场景，参考XML结构如下：

<products>
  <product>
    <image>url1</image>
  </product>
  <product>
    <image>url1</image>
  </product>
</products>

实际业务场景中，数据库表包含XML类型字段，同时存在整数类型PLU字段，待解析XML在同层级下存在多个同名<image>节点，结构如下：

<product>
  <images>
    <image>url1</image>
    <image>url2</image>
    <image>url3</image>
  </images>
</product>

预期查询效果为每个image节点的URL值单独拆为一行：

image
-----
url1
url2
url3

问题复现 #

首次编写的查询语句：

select a.image.value('image','nvarchar(max)') as image 
from products r
cross apply r.xml.nodes('/product/images') a(image) where PLU='8019'

执行抛出错误：

XQuery [products.xml.value()]: 'value()' requires a singleton (or empty sequence), 
found operand of type 'xdt:untypedAtomic *'

调整为使用.提取节点值后，仅返回1行结果，所有URL被拼接为url1url2url3格式的单字符串：

select a.image.value('.','nvarchar(max)') as image 
from products r
cross apply r.xml.nodes('/product/images') a(image) where PLU='8019'

后续尝试指定提取第一个image节点，仅返回url1单条结果，无法满足拆行需求：

select a.image.value('image[1]','nvarchar(max)') as image 
from products r
cross apply r.xml.nodes('/product/images') a(image) where PLU='8019'

正确写法 #

问题根源是nodes()方法的XPath路径仅定位到了<images>父节点，没有下沉到单个<image>层级，导致返回的是多节点集合而非单个节点。将XPath路径直接匹配到所有<image>节点即可实现拆行，代码如下：

SELECT a.image.value('.', 'nvarchar(max)') AS image
FROM products r
CROSS APPLY r.xml.nodes('/product/images/image') a(image)
WHERE PLU = '8019'

逻辑说明 #

• 路径/product/images/image会匹配<images>节点下所有的<image>子节点，nodes()方法会为每个匹配到的独立节点返回单独一行
• 每行返回的上下文已经是单个image节点，使用.提取节点文本值时，不会触发单例校验错误，也不会出现多值拼接问题
• 如果XML带有命名空间，只需在查询前通过WITH XMLNAMESPACES声明对应命名空间，同步调整XPath路径前缀即可正常解析。

内容的提问来源于stack exchange，提问作者Leif Neland