SQL XML query（）使用sql：variable无法正常工作

I am trying to write a scalar value function in SQL Server 2008 R2 that will take a chunk of XML and an XQuery path, and return the result as a string. Looking at just the T-SQL to perform the XML XQuery for the time being, the following example shows two statements (1 & 2). The first one should work apparently, but doesn't. The second is the same thing but without the use of sql:variable().

我试图在SQL Server 2008 R2中编写一个标量值函数,它将获取一大块XML和一个XQuery路径,并将结果作为字符串返回。仅查看T-SQL以执行XML XQuery,以下示例显示了两个语句(1和2)。第一个应该显然工作,但不是。第二个是同样的事情,但没有使用sql:variable()。

declare @xml xml

set @xml = '<?xml version="1.0" encoding="UTF-8"?>
<Response version="1.0">
    <Transaction>
        <Processing>
            <Return>Some text that I need to extract.</Return>
        </Processing>
    </Transaction>
</Response>'

declare @xquery varchar(100)
set @xquery = '/Response/Transaction/Processing/Return'

-- Statement 1 : This should work, but it doesn't
select 
    CAST(@xml.query('data(*[local-name(.) = sql:variable("@xquery")])') as varchar(max))

-- Statement 2 : This does work
select 
    CAST(@xml.query('data(/Response/Transaction/Processing/Return)') as varchar(max))

Can anyone tell me why this is not working? What am I doing wrong, if indeed whether it is at all possible?

谁能告诉我为什么这不起作用?我做错了什么,如果确实是否可能的话?

I have looked at numerous supposed working examples of this on this site, which is how I arrived at the above code, but I can't figure out why my implementation is not working.

我在这个网站上看了很多假设的工作示例,这就是我如何得到上面的代码,但我无法弄清楚为什么我的实现不起作用。

1 个解决方案

#1

This is not possible... sql:variable is meant to deliver a certain value, not a full path...

这是不可能的... sql:variable意味着提供一定的值,而不是一个完整的路径......

The only chance I know of is dynamic SQL:

我所知道的唯一机会是动态SQL:

(btw: To retrieve a value you should not use .query() and cast it, better take .value()

(顺便说一句:要检索一个值,你不应该使用.query()并强制转换它,最好采用.value()

declare @xml xml
set @xml = '<?xml version="1.0" encoding="UTF-8"?>
<Response version="1.0">
    <Transaction>
        <Processing>
            <Return>Some text that I need to extract.</Return>
        </Processing>
    </Transaction>
</Response>';

-- Statement "direct value"
select @xml.value('(/Response/Transaction/Processing/Return)[1]','varchar(max)');

--dynamic SQL
declare @xquery varchar(100)='(/Response/Transaction/Processing/Return)[1]'
declare @dynamicSQL NVARCHAR(100)='select @xml.value(''' + @xquery + ''',''varchar(max)'')';
exec sp_executesql  @dynamicSQL, N'@xml XML',@xml;

#1