如何在SQL Server中按Type后缀关联XML同级节点？

数据结构与初始化SQL #

CREATE TABLE [dbo].[MyObjects]
(
    [Id] [bigint] identity,
    [Details] [xml]
)

INSERT MyObjects(Details) 
VALUES('<List>
    <e>
        <Name>Street bike 1</Name>
        <Type>Object1</Type>
    </e>
    <e>
        <Name>Mountain bike 1</Name>
        <Type>Object2</Type>
    </e>
     <e>
        <Value>350</Value>
        <Type>Value1</Type>
    </e>
    <e>
        <Value>300</Value>
        <Type>Value2</Type>
    </e>
</List>')

期望查询结果 #

Street bike 1, 350
Mountain bike 1, 300

现有查询语句 #

SELECT 
    objects.e.value('(Name/text())[1]','varchar(100)') ObjectName, 
    '0' ObjectValue
FROM 
    MyObjects mo
CROSS APPLY   
    mo.Details.nodes('(List/e[Type[contains(.,"Object")]])') objects(e)

解决方案 #

通过提取Object类型节点的数字后缀，匹配对应Value类型节点的方式实现关联查询，最终拼接结果：

SELECT 
    CONCAT(
        obj.e.value('(Name/text())[1]', 'varchar(100)'),
        ', ',
        mo.Details.value('(List/e[Type = concat("Value", substring(sql:column("obj_type"), 7))]/Value/text())[1]', 'varchar(10)')
    ) AS Result
FROM 
    MyObjects mo
CROSS APPLY   
    mo.Details.nodes('List/e[Type[starts-with(., "Object")]]') obj(e)
CROSS APPLY
    (SELECT obj.e.value('(Type/text())[1]', 'varchar(20)') AS obj_type) AS type_extract

思路说明 #

1. 用CROSS APPLY筛选出所有Type以Object开头的节点，获取名称和类型字符串
2. 从ObjectN类型字符串中提取数字部分（substring(obj_type,7)跳过"Object"6个字符）
3. 通过concat("Value", 数字)匹配对应的ValueN类型节点，提取其Value值
4. 拼接名称和数值得到最终结果

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