Query XML data in a function with namespace

Question

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!

SHOW US the XML ! And also: WHAT error do you get? We can't read your screen, nor your mind - you'll have to tell us! — marc_s
– marc_s, Commented Sep 5, 2014 at 10:19
I replaced with this and now error gone but its not fetching correct value but just the path @Rs = tb.[Profile].value('(sql:variable("@p_path"))[1]', 'nvarchar(max)') — talaa123
– talaa123, Commented Sep 5, 2014 at 10:24
Please see the updated code. Now I am not getting error but the value returned is not correct as it just returns the xml path that has been passed to this function. — talaa123
– talaa123, Commented Sep 5, 2014 at 10:30

Mikael Eriksson · Accepted Answer · 2014-09-05 11:34:59Z

1

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)')

edited Sep 5, 2014 at 11:34

answered Sep 5, 2014 at 11:29

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mikael Eriksson Over a year ago

@talaa123 You can't. One option is to build the entire query dynamically and use sp_executesql but that is not allowed in a function.

talaa123 Over a year ago

But there must be a work around to deal it. Any other method to retrieve value?

Collectives™ on Stack Overflow

Query XML data in a function with namespace

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related