SQL Server T-SQL中如何连接XML节点？

问题说明 #

现有SQL Server表，包含Reference(VARCHAR类型)和XmlData(XML类型)列。需要将XmlData中的FirstGroup节点与SecondGroup节点关联：通过FirstGroup的PartOne和PartTwo字段组合值，匹配SecondGroup的Title字段，从而获取对应的OutputTwo值。

示例数据 #

Declare     @MyTable    Table
                      ( Reference   VarChar(20)
                      , XmlData     Xml
                      )


Insert      @MyTable
Values    ( 'Reference1'
          , '<MainStuff>
                <FirstGroups>
                    <FirstGroup>
                        <PartOne>ABC</PartOne>
                        <PartTwo>123</PartTwo>
                        <OutputOne>Get this value ABC-123 First</OutputOne>
                    </FirstGroup>
                    <FirstGroup>
                        <PartOne>ABC</PartOne>
                        <PartTwo>456</PartTwo>
                        <OutputOne>Get this value ABC-456 First</OutputOne>
                    </FirstGroup>
                    <FirstGroup>
                        <PartOne>DEF</PartOne>
                        <PartTwo>123</PartTwo>
                        <OutputOne>Get this value DEF-123 First</OutputOne>
                    </FirstGroup>
                </FirstGroups>
                <SecondGroups>
                    <SecondGroup>
                        <Title>ABC-456</Title>
                        <OutputTwo>Get this value ABC-456 Second</OutputTwo>
                    </SecondGroup>
                    <SecondGroup>
                        <Title>ABC-123</Title>
                        <OutputTwo>Get this value ABC-123 Second</OutputTwo>
                    </SecondGroup>
                    <SecondGroup>
                        <Title>DEF-123</Title>
                        <OutputTwo>Get this value DEF-123 Second</OutputTwo>
                    </SecondGroup>
                </SecondGroups>
            </MainStuff>
          '
          )

期望输出 #

已实现的部分查询 #

Select      Reference
          , FG.value('(PartOne)[1]', 'VarChar(100)' ) + '-' + FG.value('(PartTwo)[1]', 'VarChar(100)' )     As  Title
          , FG.value('(OutputOne)[1]', 'VarChar(100)' )                                                     As  OutputOne
From        @MyTable                MT
Outer Apply XmlData.nodes('//*/FirstGroup')     As  FirstGroup(FG)

完整解决方案 #

在原有查询基础上，通过OUTER APPLY解析SecondGroup节点，并利用sql:column()引用已计算的Title值进行匹配，即可获取对应的OutputTwo：

Select      MT.Reference
          , FG.value('(PartOne)[1]', 'VarChar(100)' ) + '-' + FG.value('(PartTwo)[1]', 'VarChar(100)' )     As  Title
          , FG.value('(OutputOne)[1]', 'VarChar(100)' )                                                     As  OutputOne
          , SG.value('(OutputTwo)[1]', 'VarChar(100)' )                                                     As  OutputTwo
From        @MyTable                MT
Outer Apply XmlData.nodes('//*/FirstGroup')     As  FirstGroup(FG)
Outer Apply XmlData.nodes('//*/SecondGroup[Title/text() = sql:column("Title")]') As SecondGroup(SG)

说明 #

• sql:column("Title")用于在XPath表达式中引用SQL查询中已计算的Title列值，实现节点间的关联匹配
• 使用OUTER APPLY确保即使没有匹配的SecondGroup节点，也能保留FirstGroup的行数据（若需仅保留匹配行，可替换为CROSS APPLY）

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