SQL Variables in XML Node

Question

Reavluating the example given here: SQL Variable in XML Node

Given:

DECLARE @x TABLE(item XML)
DECLARE  @schemaname VARCHAR(100)
SET @schemaname = 'Bla'

INSERT into @x
SELECT  '
<GaleriesSchem2>
    <Hello>
        <Bla>
            <Image_1 OriginalName="Image">12.jpg</Image_1>
            <Image_2 OriginalName="Image2">45.jpg</Image_2>
        </Bla>
    </Hello>
</GaleriesSchem2>
<GaleriesSchem3>
  <Image_1 OriginalName="Image">67.jpg</Image_1>
  <Image_2 OriginalName="Image2">89.jpg</Image_2>
</GaleriesSchem3>
'
SELECT rref.value('.', 'varchar(MAX)') AS 'Value'
FROM @x
  CROSS APPLY     
    item.nodes('GaleriesSchem2/Hello/*[local-name()=sql:variable("@schemaname")]/node()') AS Results(rref)

When the variable @schemaname is only 'Bla' the Select statements returns 12.jpg and 45.jpg but when the variable @schemaname is 'GaleriesSchem2/Hello/Bla' it returns nothing. When using

SET @schemaname = 'GaleriesSchem2/Hello/Bla'
SELECT rref.value('.', 'varchar(MAX)') AS 'Value'
FROM @x
  CROSS APPLY     
    item.nodes('*[local-name()=sql:variable("@schemaname")]/node()') AS Results(rref)

Where is the conflict here? How can i access a xml node with a variable? In this case the variable is @schemaname = 'GaleriesSchem2/Hello/Bla'.

Update To get the job done in this case i tried dynamic sql like the two suggested could be one solution to the problem. For that I made the @x variable to a real table and put the XML fragment into it.

DECLARE  @schemaname VARCHAR(100)
Declare @sql nvarchar(500)
Create Table x(item XML)
INSERT into x
SELECT  '
<GaleriesSchem2>
    <Hello>
        <Bla>
            <Image_1 OriginalName="Image">12.jpg</Image_1>
            <Image_2 OriginalName="Image2">45.jpg</Image_2>
        </Bla>
    </Hello>
</GaleriesSchem2>
<GaleriesSchem3>
  <Image_1 OriginalName="Image">67.jpg</Image_1>
  <Image_2 OriginalName="Image2">89.jpg</Image_2>
</GaleriesSchem3>
'
SET @schemaname = 'GaleriesSchem2/Hello/Bla/node()'
Set @sql = N'SELECT rref.value(' + '''.''' + ', ' + '''varchar(MAX)''' + ') AS Value FROM x CROSS APPLY item.nodes(''' + @schemaname + ''') AS Results(rref)';

Exec sp_executesql @sql

You cannot evaluate paths dynamically this way. XQuery does not allow variable path names, and T-SQL does not allow variables for XQuery. You can work around this by building the whole query dynamically, but that's no picnic. You will have to rethink your approach (exactly how depends on what you're trying to achieve with these dynamic paths). — Jeroen Mostert
– Jeroen Mostert, Commented Oct 15, 2019 at 12:44
@JeroenMostert thanks for the comment. At least it is nice to know what can´t be done, so you don´t need to put more effort into this direction. — N-OW
– N-OW, Commented Oct 15, 2019 at 15:11

Gottfried Lesigang · Accepted Answer · 2019-10-15 14:53:59Z

As Jeroen Mostert told you in a comment, this is not possible. You can use local-name() in connection with sql:variable() to test for a specific element, but you cannot introduce a XPath-variable.

You can build the whole statement dynamically and use EXEC() or sp_executesql() for the execution, or you can use a recursive CTE to walk down the XML and return each element with it's path.

DECLARE @x TABLE(item XML)
DECLARE  @schemaname VARCHAR(100)
SET @schemaname = 'Bla'

INSERT into @x
SELECT  
'<GaleriesSchem2>
    <Hello>
        <Bla>
            <Image_1 OriginalName="Image">12.jpg</Image_1>
            <Image_2 OriginalName="Image2">45.jpg</Image_2>
        </Bla>
    </Hello>
</GaleriesSchem2>
<GaleriesSchem3>
  <Image_1 OriginalName="Image">67.jpg</Image_1>
  <Image_2 OriginalName="Image2">89.jpg</Image_2>
</GaleriesSchem3>';

WITH recCTE AS
(
    SELECT CONCAT('/',CAST(TopLevelNode.value('local-name(.)','nvarchar(max)') AS NVARCHAR(MAX)),'/') AS NodePath
          ,TopLevelNode.query('./*') AS SubNodes
          ,TopLevelNode.value('text()[1]','nvarchar(max)') AS Content
    FROM @x 
    CROSS APPLY item.nodes('*') A(TopLevelNode)

    UNION ALL

    SELECT CONCAT(r.NodePath,CAST(TheNextLevel.value('local-name(.)','nvarchar(max)') AS NVARCHAR(MAX)),'/')
          ,TheNextLevel.query('./*')
          ,TheNextLevel.value('text()[1]','nvarchar(max)') AS Content
    FROM recCTE r
    OUTER APPLY r.SubNodes.nodes('*') A(TheNextLevel)
    WHERE r.SubNodes.exist('*')=1
)
SELECT *
FROM recCTE 
WHERE Content IS NOT NULL;

The result

NodePath                            Content
/GaleriesSchem3/Image_1/            67.jpg
/GaleriesSchem3/Image_2/            89.jpg
/GaleriesSchem2/Hello/Bla/Image_1/  12.jpg
/GaleriesSchem2/Hello/Bla/Image_2/  45.jpg

Against this set you might use your XPath in a simple LIKE...

But - to be honest - this is very limited and rather slow...

Collectives™ on Stack Overflow

SQL Variables in XML Node

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