SQL Server 2017 XML nodes()查询：如何动态匹配子节点无需硬编码列表

你之前遇到的硬编码节点痛点、nodes()触发的报错，本质是两个认知偏差：

• nodes()本身根本没有“必须传固定节点名列表”的硬限制，之前触发的报错是XPath路径规则没写对，不是方法本身有功能限制。
• 你要匹配的所有目标描述节点，都有统一的结构特征，完全可以用结构过滤代替手动列节点名的硬编码方式。

你尝试写的CROSS APPLY p.r.nodes('/Submission/Measures/*')为什么会抛“需要节点集合”的错？
因为XPath语法里只要路径以/开头，就永远从XML文档根节点开始解析，不会顺着p.r当前指向的节点上下文（也就是每个Measures下的指标根节点，比如A100、B200）往下遍历，最终解析出来的内容不符合节点上下文的类型要求，自然就触发校验报错。只要换成不以/开头的相对路径写法，这个问题直接解决。

把原来硬编码子节点列表的那行CROSS APPLY，替换成按结构特征匹配的规则就行，核心逻辑是：匹配当前指标节点下，所有直接下级里同时包含Stratification和Data两个子节点的元素，不管这个元素本身叫什么名字，都能被识别到。

完全动态的长表输出（推荐） #

这种写法连Data下的统计字段名都不用硬编码，不管未来XML结构怎么新增指标、新增统计值，都能自动匹配，不需要改查询代码，且完全兼容SQL Server 2017环境：

DECLARE @xml XML = 
N'<Submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Measures>
        <A100 versionID="A01">
            <Metadata>
                <TY_Year>2020</TY_Year>
                <Benefit>true</Benefit>
            </Metadata>
            <A100_Description>
                <Stratification>
                    <Pool>11-14</Pool>
                </Stratification>
                <Data>
                    <RegTotal>13071</RegTotal>
                    <CountA>542</CountA>
                </Data>
            </A100_Description>
            <A100_Description>    
                <Stratification>     
                    <Pool>4-6</Pool>         
                </Stratification>
                <Data>   
                    <RegTotal>8995</RegTotal>     
                    <CountA>366</CountA>     
                </Data>
            </A100_Description>
            <A100_Description>   
                <Stratification>     
                    <Pool>7-10</Pool>         
                </Stratification>
                <Data>   
                    <RegTotal>11818</RegTotal>   
                    <CountA>546</CountA>      
                </Data>
            </A100_Description>
        </A100>
        <B200 versionID="B12">
            <Metadata>
                <TY_Year>2020</TY_Year>
                <Benefit>true</Benefit>
            </Metadata>
            <B200_Description>
                <Stratification>
                    <Pool>12-18</Pool>
                </Stratification>
                <Data>
                    <RegTotal>349</RegTotal>
                    <X5>12</X5>
                    <CountA>269</CountA>
                    <CountB>0</CountB>
                </Data>
            </B200_Description>
        </B200>
        <z300 versionID="B33">
            <z300_Descr>
                <Stratification>
                    <Pool>19-50</Pool>
                </Stratification>
                <Data>
                    <RegTotal>33333</RegTotal>
                    <X5>3333</X5>
                    <CountA>333</CountA>
                    <CountB>33</CountB>
                </Data>
            </z300_Descr>
        </z300>
    </Measures>
</Submission>';

SELECT 
    -- 取当前所属的指标根节点名
    r.value('local-name(.)', 'VARCHAR(30)') AS [Msr]  
    -- 取分层的Pool值
    , c.value('(Stratification/Pool/text())[1]', 'VARCHAR(30)') AS [Pool]
    -- 动态取Data下的字段名和值，不用硬编码RegTotal、CountA这些
    , d.value('local-name(.)', 'VARCHAR(30)') AS [DataItemName]
    , d.value('.', 'INT') AS [DataItemValue]
 FROM @xml.nodes('/Submission/Measures/*') AS p(r)
 -- 动态匹配所有带Stratification和Data的下级描述节点，不用列具体节点名
 CROSS APPLY p.r.nodes('./*[Stratification and Data]') AS t(c)
 -- 动态拆Data下的所有子节点
 CROSS APPLY t.c.nodes('./Data/*') AS s(d);

和原格式一致的宽表输出 #

如果一定要保持原来“每个统计值单独占一列”的宽表结构，只需要把原来硬编码的CROSS APPLY行替换成下面这行就行，其余查询逻辑完全不用动：

CROSS APPLY p.r.nodes('./*[Stratification and Data]') AS t(c)

这行代码的XPath逻辑：

• . 代表顺着当前p.r的节点上下文走，不会跳回XML根节点
• * 匹配当前节点下所有直接子元素
• [Stratification and Data] 是过滤条件，只保留那些同时把Stratification、Data作为直接子节点的元素

不管未来XML里把描述节点改成叫X123_Desc、Y456_View还是别的任意名字，只要结构符合要求，都能被匹配到，完全不需要每次新增节点就回头改硬编码列表。

补充：这种写法不存在nodes()方法的参数限制——只要你传的XPath能解析出符合要求的节点集合，不管是固定节点名还是动态结构匹配，都能正常运行。

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