如何用SQL查询提取XML子节点值以生成指定结果？

我在SQL中存储了如下XML数据，希望通过查询得到包含TabSeq、Path、SubTabSeq的指定结果表格，请问是否可以实现该需求？

期望结果表格 #

XML数据 #

<Tabs>
    <Tab sequence="001">
        <Family sequence="001">
            <Data>Yes</Data>
        </Family>
        <Location sequence="002">
            <Data>USA</Data>
        </Location>
    </Tab>
    <Tab sequence="002">
        <Address sequence="001">
            <Data>XYZ</Data>
        </Address>
        <Insurance sequence="002">
            <Data>Yes</Data>
        </Insurance>
    </Tab>
</Tabs>

尝试的SQL代码 #

declare @xml xml='<Tabs><Tab sequence="001"><Family sequence="001"><Data>Yes</Data></Family><Location sequence="002"><Data>USA</Data></Location></Tab><Tab sequence="002"><Address sequence="001"><Data>XYZ</Data></Address><Insurance sequence="002"><Data>Yes</Data></Insurance></Tab></Tabs>'

SELECT  t.c.value('@sequence', 'nvarchar(100)') As TabSeq
FROM @xml.nodes('//Tabs/child::node()') as t(c)

SELECT '//Tab' + '/' + c.value('local-name(.)[1]','nvarchar(100)') AS Path,  t.c.value('@sequence', 'nvarchar(100)') As SubTabSeq
FROM @xml.nodes('//Tab/child::node()') as t(c)

解决方案 #

可以实现，需要通过嵌套节点查询关联父节点（Tab）和子节点（Family/Location等）的信息，同时处理序列值的格式（去掉前导零转为数字）。以下是修正后的SQL代码：

declare @xml xml='<Tabs><Tab sequence="001"><Family sequence="001"><Data>Yes</Data></Family><Location sequence="002"><Data>USA</Data></Location></Tab><Tab sequence="002"><Address sequence="001"><Data>XYZ</Data></Address><Insurance sequence="002"><Data>Yes</Data></Insurance></Tab></Tabs>'

SELECT
    -- 提取Tab的sequence并转为整数（去除前导零）
    CAST(tab.c.value('@sequence', 'int') AS varchar(10)) AS TabSeq,
    -- 拼接Path路径
    '//Tab/' + child.c.value('local-name(.)', 'nvarchar(100)') AS Path,
    -- 提取子节点的sequence并转为整数
    CAST(child.c.value('@sequence', 'int') AS varchar(10)) AS SubTabSeq
FROM 
    @xml.nodes('/Tabs/Tab') AS tab(c)
CROSS APPLY 
    tab.c.nodes('*') AS child(c)

代码说明 #

1. @xml.nodes('/Tabs/Tab')：先定位到所有Tab节点，获取每个Tab的sequence属性。
2. CROSS APPLY tab.c.nodes('*')：通过交叉应用，关联每个Tab下的所有子节点（Family、Location等）。
3. CAST(... AS int)：将带前导零的sequence值转为整数，自动去除前导零，得到期望的1、2格式。
4. local-name(.)：获取子节点的本地名称（如Family、Address），用于拼接Path路径。

执行上述代码后，即可得到与期望完全一致的结果表格。

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