SQL中如何使用变量获取XML元素的指定层级节点值？

方法一：使用sql:variable()适配XML value方法 #

XML的value()方法要求XPath参数必须是字符串字面量，无法直接拼接变量，但可以通过sql:variable()函数在XPath中引用TSQL变量，实现动态指定层级：

DECLARE @ProjectID int,
        @Level int
SET @ProjectID = 58
SET @Level = 2

SELECT               
    CAST('<t>' + REPLACE(ParentId1 , '.','</t><t>') + '</t>' AS XML).value('(/t[sql:variable("@Level")])[1]','varchar(50)')
FROM 
    @tmptbl
WHERE
    linked_task = @ProjectID

为什么之前的写法报错 #

• 尝试写法1：value()方法的XPath参数不支持动态拼接的字符串，必须是编译期确定的字面量，直接拼接@Level会触发语法错误。
• 尝试写法2：/t["@Level"]是把@Level当成字符串常量匹配，而XPath的索引需要数值类型，不符合语法要求。

方法二：使用STRING_SPLIT（SQL Server 2016+） #

如果你的SQL Server版本支持STRING_SPLIT，可以通过拆分字符串并结合行号提取对应层级的值：

DECLARE @ProjectID int,
        @Level int
SET @ProjectID = 58
SET @Level = 2

SELECT s.value
FROM @tmptbl t
CROSS APPLY (
    SELECT 
        value, 
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
    FROM STRING_SPLIT(t.ParentId1, '.')
) s
WHERE t.linked_task = @ProjectID AND s.rn = @Level

注意：SQL Server 2022及以上版本明确保证STRING_SPLIT的输出顺序与原字符串一致；低版本实际执行也会保持顺序，但官方文档未明确承诺，若需要绝对稳妥的顺序，可改用递归CTE或自定义字符串拆分函数。

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