Cannot pass variable to sql:variable() function within xml.Nodes() in SQL

Question

I'm trying to pass in a value to xml.nodes so that I can genericize:

DECLARE @xml xml = 
'
<root>
    <level1>
        <sublevel1>
            <val>this is a value</val>
            <val>this is also a value</val>
        </sublevel1>
    </level1>
</root>
'

declare @xmlPath nvarchar(250) = '/root/level1/sublevel1'

Select @xmlPath as [path], t.c.query('.') as data
from @xml.nodes ('/root/level1/sublevel1') as t(c)  --this line find the nodes
--from @xml.nodes ('*[local-name()=sql:variable("@xmlPath")]') as t(c)  --This one doesn't

I got to this possible solution when I tried to pass in a variable .Nodes(@xmlPath) or concatenate the path and the Nodes() function required a string literal.

Expected result:

<sublevel1>
  <val>this is a value</val>
  <val>this is also a value</val>
</sublevel1>

I'm getting no error, but also no data returned when I run this.

References for this method:

https://codegumbo.com/index.php/2013/10/04/sql-server-xquery-functions-sqlvariable-sqlcolumn/

Get SQL xml attribute value using variable

Select nodes in T-SQL XML by name given in a variable

https://learn.microsoft.com/en-us/sql/xquery/xquery-extension-functions-sql-variable?view=sql-server-ver16

Can you limit it to a particular level? Or a fixed number of possible levels? — Charlieface
– Charlieface, Commented May 9, 2023 at 20:16
@Charlieface Possibly, I'm not sure what that would look like. — MAW74656
– MAW74656, Commented May 9, 2023 at 20:19
Ie can you split up the paths into (a maximum of) a fixed number of nodes @xmlPath1 @xmlPath2 @xmlPath3 or do you need to have aribitrary depth? — Charlieface
– Charlieface, Commented May 9, 2023 at 20:30
@lptr, add this as an answer. openxml, as usual, is the king of xml operations in sql server — siggemannen
– siggemannen, Commented May 10, 2023 at 8:04

Charlieface · Accepted Answer · 2023-05-09 20:52:57Z

You cannot do dynamic node descent. Each variable is only compared against a single node's name.

If you have a fixed number of nodes to descend then you can just query each level independently.

Select
  t.c.query('.') as data
from @xml.nodes('
  *[local-name()=sql:variable("@xmlPath1")]
  /*[local-name()=sql:variable("@xmlPath2")]
  /*[local-name()=sql:variable("@xmlPath3")]
') as t(c);

If you have a dynamic, but maximum, number of nodes to descend then you can use some nested if and for expressions

Select
  t.c.query('.') as data
from @xml.nodes('
  for $n1 in *[local-name()=sql:variable("@xmlPath1")]
  return
    if (sql:variable("@xmlPath2") > "")
    then
      for $n2 in $n1/*[local-name()=sql:variable("@xmlPath2")]
      return
        if (sql:variable("@xmlPath3") > "")
        then
          $n2/*[local-name()=sql:variable("@xmlPath3")]
        else $n2
    else $n1
') as t(c);

You can nest it as deep as you want, but it's likely to be pretty inefficient. XQuery in SQL Server is designed to have a fixed path to query, not to use dynamic matching.

Getting the full path is going to be even more complex, and probably impossible with this design.

Another option is to use multiple levels of OUTER APPLY nodes to get the correct results. This allows you to get the path as well, using CONCAT_WS to combine the node names with a separator.

Select
  concat_ws('/',
    t1.c.value('local-name(.)', 'nvarchar(100)'),
    t2.c.value('local-name(.)', 'nvarchar(100)'),
    t3.c.value('local-name(.)', 'nvarchar(100)')
  ) as path,
  case when @xmlPath3 is not null
       then t3.c.query('.')
       when @xmlPath2 is not null
       then t2.c.query('.')
       else
            t1.c.query('.')
  end as data
from        @xml.nodes('*[local-name()=sql:variable("@xmlPath1")]') t1(c)
outer apply t1.c.nodes('*[local-name()=sql:variable("@xmlPath2")]') t2(c)
outer apply t2.c.nodes('*[local-name()=sql:variable("@xmlPath3")]') t3(c);

db<>fiddle

abolfazl sadeghi · Accepted Answer · 2023-05-09 21:26:48Z

1

you can use dynamic sql to solve problem


DECLARE @xml xml = 
'
<root>
    <level1>
        <sublevel1>
            <val>this is a value</val>
            <val>this is also a value</val>
        </sublevel1>
    </level1>
</root>
'

declare @xmlPath nvarchar(250) = '/root/level1/sublevel1'

declare @SQL nvarchar(max)
set @SQL = 'select '''+@xmlPath+''' as [path], t.c.query(''.'') as dataa
from  @xml.nodes('''+@xmlPath+''') as t(c)'
exec sp_executesql @SQL, N'@xml xml', @xml

dbfiddle

answered May 9, 2023 at 21:26

abolfazl sadeghi

2,4182 gold badges15 silver badges23 bronze badges

3 Comments

Charlieface Over a year ago

Not injection safe, but I suppose it works

MAW74656 Over a year ago

I don't want to use this because its production code. Although no user entry is directly involved, so its probably ok...

abolfazl sadeghi Over a year ago

I did not understand the problem. You don't want to use it because it is dynamic

Collectives™ on Stack Overflow

Cannot pass variable to sql:variable() function within xml.Nodes() in SQL

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related