xml querying with variable

Question

I am trying to pass the xpath as parameter to the query.

   declare @test as nvarchar(1000) = '(ns1:Book/Authors)[1]'
   ;with XMLNAMESPACES ('MyNameSpace:V1' as ns1)
   select 
    b.XmlData.value(
     '@test'
     , 'nvarchar(100)') as QueriedData 
   from Books b
   where b.BookID = '1'

The above statement gave the following error.

XQuery [Books.XmlData.value()]: Top-level attribute nodes are not supported

Tried it as @test, instead of '@test'. And got the following error:

The argument 1 of the XML data type method "value" must be a string literal.

Tried it using 'sql:variable(@test)' and get this error:

XQuery [Books.XmlData.value()]: A string literal was expected

Tried it as 'sql:variable("@test")' and it shows the value in @test as QueriedData, which is wrong

Please tell me what am I missing here

You can not use a variable instead of the xQuery expression. One option you have is to build and execute the query dynamically as in this answer. — Mikael Eriksson
– Mikael Eriksson, Commented Mar 4, 2013 at 13:38

Ben · Accepted Answer · 2013-03-04 14:47:07Z

1

You cannot use a variable as the XQuery expression, but the expression can refer to variables.

set @ix = 2
with XMLNAMESPACES ('MyNameSpace:V1' as ns1)
select 
 b.XmlData.value(
  '((ns1:Book/ns1:Authors)[sql:variable("@ix")])[1]'
  , 'nvarchar(100)') as QueriedData 
    from Books b
    where b.BookID = '1'

This includes the element names. For example, to put the node name in a parameter:

declare @elementName nvarchar(20) set @elementName = 'Authors'


    with XMLNAMESPACES ('MyNameSpace:V1' as ns1)
    select 
     b.XmlData.value(
      '((//ns1:*)[ local-name()=sql:variable("@elementName") ] )[1]'
      , 'nvarchar(100)') as QueriedData

This means "Find elements in the namespace ns, which has a local-name of @elementName, then return the first."

edited Mar 4, 2013 at 14:47

answered Mar 4, 2013 at 14:35

Ben

35.8k6 gold badges81 silver badges117 bronze badges

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

Collectives™ on Stack Overflow

xml querying with variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related