SQL中无法将变量传入xml.Nodes()内sql:variable()函数的问题

问题原因 #

你尝试的*[local-name()=sql:variable("@xmlPath")]逻辑有误：这个XPath是在当前节点下寻找节点名称等于完整路径字符串的节点，而非按照路径定位节点，所以无法匹配到目标内容。

解决方案1：动态SQL（通用方案） #

由于nodes()函数要求XPath必须是字符串字面量，最直接的方式是通过动态SQL拼接完整的查询语句：

DECLARE @xml xml = 
'
<root>
    <level1>
        <sublevel1>
            <val>this is a value</val>
            <val>this is also a value</val>
        </sublevel1>
    </level1>
</root>
'

declare @xmlPath nvarchar(250) = '/root/level1/sublevel1'
declare @sql nvarchar(max)

SET @sql = N'
Select @xmlPath as [path], t.c.query(''.'') as data
from @xml.nodes(''' + REPLACE(@xmlPath, '''', '''''') + ''') as t(c)
'

EXEC sp_executesql @sql, N'@xml xml, @xmlPath nvarchar(250)', @xml = @xml, @xmlPath = @xmlPath

解决方案2：使用XQuery的evaluate方法（需特定配置） #

如果你的SQL Server版本为2016及以上，可以开启ALLOW_XQUERY_EVALUATE服务器配置选项后，使用evaluate方法动态执行XPath：

1. 先开启配置（需管理员权限）：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'ALLOW_XQUERY_EVALUATE', 1;
RECONFIGURE;

2. 执行查询：

DECLARE @xml xml = 
'
<root>
    <level1>
        <sublevel1>
            <val>this is a value</val>
            <val>this is also a value</val>
        </sublevel1>
    </level1>
</root>
'

declare @xmlPath nvarchar(250) = '/root/level1/sublevel1'

Select @xmlPath as [path], t.c.query('.') as data
from @xml.nodes('sql:variable("@xmlPath")') as t(c)

注意事项 #

• 动态SQL需注意SQL注入风险，如果@xmlPath来自不可信输入，要先做校验或转义（示例中用REPLACE处理了单引号）。
• ALLOW_XQUERY_EVALUATE是高级配置，开启前需评估安全风险，部分环境可能限制使用。

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