Sql Variable In Xml Node

I have the following code: DECLARE @x TABLE (item XML (document Galeries)) DECLARE @schemaname VARCHAR(100) SET @schemaname = 'GaleriesSchem2' INSERT into @x SELECT '

Solution 1:

Use asterisk instead of double slash

DECLARE@xTABLE(item XML)
DECLARE@schemanameVARCHAR(100)
SET@schemaname='GaleriesSchem3'INSERTinto@xSELECT'
<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@xCROSS APPLY     
    item.nodes('*[local-name()=sql:variable("@schemaname")]/node()') AS Results(rref)

See demo on SQLFiddle

Share