在具有名称空间的函数中查询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的变量/参数.
语法
值(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屋!