如何通过SELECT查询提取XML列中的父子关系数据？

完全可以通过SELECT查询实现这个需求，利用SQL Server的XML数据类型提供的.nodes()和.value()方法就能提取嵌套的XML数据，同时自动过滤掉没有关联设备的记录。

具体查询语句 #

SELECT 
    g.ID AS [Group Id],
    p.person.value('@name', 'NVARCHAR(100)') AS PersonName,
    d.device.value('@name', 'NVARCHAR(100)') AS DeviceName
FROM 
    [Group] g
CROSS APPLY 
    g.Content.nodes('/root/person') AS p(person)
CROSS APPLY 
    p.person.nodes('device') AS d(device)

语句说明 #

• g.Content.nodes('/root/person') AS p(person)：将XML中的每个<person>节点拆分为独立的行数据
• p.person.nodes('device') AS d(device)：针对每个<person>节点，进一步拆分其下属的<device>节点，没有<device>子节点的<person>会被自动排除
• .value('@name', 'NVARCHAR(100)')：提取节点的name属性值，并转换为指定的字符串类型

执行该查询后，会得到符合要求的结果：

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