T-SQL中如何提取嵌套XML节点，获取所有Serial对应的数据行？

问题原因 #

你当前的查询仅在Product节点层级拆分XML，每个Product对应1行结果，value()方法中加[1]是强制取该Product下第一个Serial节点的字段值，自然漏掉了ProductCode=100下的第二个Serial记录。

解决方案 #

调整nodes()方法的路径到Serial节点层级，按Serial拆分数据即可，有两种常用写法：

写法1：直接定位到Serial节点拆分

SELECT
    SerialNode.value('../../@ProductCode','varchar(50)') AS ProductCode
    ,SerialNode.value('(YearNumber)[1]','int') AS YearNumber
    ,SerialNode.value('(SerialNumbers)[1]','nvarchar(max)') AS SerialNumbers
FROM
@Products_XML.nodes('/Products/Product/Serilas/Serial')AS TEMPTABLE(SerialNode)

写法2：分层拆分，可读性更高

SELECT
    Product.value('@ProductCode','varchar(50)') AS ProductCode
    ,SerialNode.value('(YearNumber)[1]','int') AS YearNumber
    ,SerialNode.value('(SerialNumbers)[1]','nvarchar(max)') AS SerialNumbers
FROM
@Products_XML.nodes('/Products/Product')AS TEMPTABLE(Product)
CROSS APPLY Product.nodes('Serilas/Serial') AS SerialTbl(SerialNode)

最终查询结果 #

两种写法都会返回预期的3行数据：

ProductCode | YearNumber  | SerialNumbers
-----------------------------------------
100         | 2020        | 1,2,3,4,5
-----------------------------------------
100         | 2021        | 6,7,8,9,10
-----------------------------------------
200         | 2021        | 600

内容的提问来源于stack exchange，提问作者Muhammad Musavi