CODEWITHSUNDEEP
sql-serverxml
N-OW
N-OW

Reputation: 37

SQL Variables in XML Node

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

Upvotes: 0

Views: 1669

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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...

Upvotes: 1

Related Questions