SQL Variable in XML Node

Question

I have the following code:

DECLARE @x TABLE (item XML (document Galeries))
DECLARE  @schemaname     VARCHAR(100)
SET @schemaname  = 'GaleriesSchem2'

INSERT into @x
SELECT  '
<GaleriesSchem2>
  <Image_1 OriginalName="Image">4814111.jpg</Image_1>
  <Image_2 OriginalName="Image2">481411.jpg</Image_2>
</GaleriesSchem2>'

SELECT rref.value('.', 'varchar(MAX)') AS 'Value'
FROM @x
  CROSS APPLY   
    item.nodes('//GaleriesSchem2/node()') AS Results(rref)

result:

1 | 4814111.jpg

2 | 481411.jpg

But I want to change the root element dynamically, for example:

 item.nodes('//[local-name()=sql:variable("@schemaname")]/node()') AS Results(rref)

But this code doesn't work.

Luke Girvin · Accepted Answer · 2016-06-29 12:57:14Z

0

Use asterisk instead of double slash

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

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

See demo on SQLFiddle

edited Jun 29, 2016 at 12:57

Luke Girvin

13.5k10 gold badges69 silver badges87 bronze badges

answered Aug 29, 2013 at 11:16

Oleksandr Fedorenko

17k6 gold badges43 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

SQL Variable 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