如何在 SQL Server 中创建参数化 XPath 查询?

问题描述

我正在尝试在 SQL 服务器中编写一个参数化查询，该查询使用参数值作为 XPath 的一部分，但是它似乎并没有像我期望的那样工作.这是我的示例:

I am trying to write a parametrized query in SQL server that uses a parameter value as part of the XPath, however it does not seem to work the way I would expect it to. Here is my sample:

create table ##example (xmltest xml)

declare @LanguagePath varchar(75)

set @LanguagePath = '(/languages/language[@id="en-US"])[1]'
insert into ##example
values ('<languages>
            <language id="en-US">c</language>
            <language id="es-ES">c</language>
        </languages>')

insert into ##example
values ('<languages>
            <language id="en-US">b</language>
            <language id="es-ES">b</language>
        </languages>')


insert into ##example
values ('<languages>
            <language id="en-US">a</language>
            <language id="es-ES">a</language>
        </languages>')

--This is a working statement:
--select *  from ##example
--order by xmltest.value('(/languages/language[@id="en-US"])[1]', 'varchar')


declare @SQL nvarchar(4000)
set @SQL = '
select *  from ##example
order by xmltest.value(@LanguagePath1, ''varchar'')
'

exec sp_executesql @SQL, N'@LanguagePath1 varchar(75)',  @LanguagePath1 = @LanguagePath;

drop table ##example

此代码导致错误:xml 数据类型方法value"的参数 1 必须是字符串字面量.

This code results in the error:The argument 1 of the xml data type method "value" must be a string literal.

关于如何让它发挥作用的任何想法?我想尝试使我的 xpath 查询免受 SQL 注入的影响.

Any ideas on how I can get this to work? I would like to try to make my xpath query safe from SQL injection.

推荐答案

你应该使用 sql:variable("@LanguagePath1") 而不是 @LanguagePath1.在此处阅读更多相关信息.虽然我不确定动态 xpath 是否可以工作 :) 但是类似于 xmltest.value('(/languages/language[@id=sql:variable("@languageCode")])[1] 应该可以工作.

You should use sql:variable("@LanguagePath1") instead of just @LanguagePath1. Read more about it here. Though I'm not sure if dynamic xpath will work :) However something like xmltest.value('(/languages/language[@id=sql:variable("@languageCode")])[1] should work.

这篇关于如何在 SQL Server 中创建参数化 XPath 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！