在具有名称空间的函数中查询XML数据 [英] Query XML data in a function with namespace

问题描述

从XML检索数据时遇到了问题.

with xmlnamespaces ('x-elements' as x)
select 
tb.[Profile].value('(x:ppr/x:static/refId)[1]', 'varchar(22)') testCol
from table1 tb

上面的代码工作得很好.但是，当我在函数中传递XML路径时，它可以正确编译，但是在调用时不返回数据，而只是返回xml路径(传递给它).

CREATE FUNCTION testFunc
(
    @p varchar(22)
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @Rs nvarchar(max);
    with xmlnamespaces ('x-elements' as x)
    select 
        @Rs = tb.[Profile].value('(sql:variable("@p_path"))[1]', 'nvarchar(max)') 
    from table1 tb
    RETURN (@Rs)

END

我得到的结果是"x:ppr/x:sta"(这不是值的路径)，而它应该返回类似"aJxk9pGntc5V"的值 请提出解决方案！

解决方案

value()函数的参数必须是字符串文字.您不能将其替换为包含要执行的XQuery的变量/参数.

从 value()方法(xml数据类型)

语法

值(XQuery，SQLType)

XQuery

是XQuery表达式，它是字符串文字，用于检索数据 在XML实例中.

您尝试使用sql:variable("@p_path")会将@p_path的值作为字符串插入XQuery.它不会替换整个XQuery表达式.它将翻译为value('("x:ppr/x:sta")', 'varchar(100)')

I am facing problems while retrieving data from XML.

with xmlnamespaces ('x-elements' as x)
select 
tb.[Profile].value('(x:ppr/x:static/refId)[1]', 'varchar(22)') testCol
from table1 tb

The above code works perfectly fine. But, when I pass the XML path in a function it compiles correctly but doesn't return data upon calling but just the xml path (that is passed to it).

CREATE FUNCTION testFunc
(
    @p varchar(22)
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @Rs nvarchar(max);
    with xmlnamespaces ('x-elements' as x)
    select 
        @Rs = tb.[Profile].value('(sql:variable("@p_path"))[1]', 'nvarchar(max)') 
    from table1 tb
    RETURN (@Rs)

END

The result I am getting is "x:ppr/x:sta" (which is a path not the value) whereas it should return a value like "aJxk9pGntc5V" Please suggest a solution!

解决方案

The parameter to the value() function has to be a string literal. You can not replace it with a variable/parameter that contains the XQuery you want to execute.

From value() Method (xml Data Type)

Syntax

value (XQuery, SQLType)

XQuery

Is the XQuery expression, a string literal, that retrieves data inside the XML instance.

Your attempt to use sql:variable("@p_path") will insert the value of @p_path as a string to the XQuery. It will not replace the entire XQuery expression. It will translate to something like value('("x:ppr/x:sta")', 'varchar(100)')

这篇关于在具有名称空间的函数中查询XML数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！