Sql Variable Containing Path In Xml Node

I need help with passing an XML path through a variable to the nodes() method. I have looked at several different posts and found that a node can be passed by using local-name and

Solution 1:

You do not want to change the whole function, just the path in XQuery. This is - as you know - not possible.

But: It seems that you are finding the same data in differing structures. At least you seem to know, that you will find nodes named ID, Name and Value below a node SomeNode...

So this approach might solve your issue in a completely different way... It works - at least - with your two given examples...

SELECTp.*
FROM
(
    SELECT Nd.value('.','int') AS SomeNode
          ,Deeper.value('local-name(.)','nvarchar(max)') AS NodeName
          ,Deeper.value('.','nvarchar(max)') AS NodeValue
    FROM @XML_In.nodes('//SomeNode') AS Sm(Nd)
    OUTER APPLY Nd.nodes('parent::*/*[local-name(.)!="SomeNode"]/*') AS TwoLevels(Deeper)
) AStblPIVOT
(
    MIN(NodeValue) FOR NodeName IN(ID,Name,Value)
) ASp

In this solution your first working example shows, how you might use SomeNode as variable...

Solution 2:

Add in a second forward slash to signify a deep search and only look for the parent node of the value you are after in your @XML_Path variable:

DECLARE@XML_Path VARCHAR(MAX) 
      , @XML_In XML

SET@XML_Path ='Hello'SET@XML_In ='
<GetData>
  <Hello>
    <test>234</test>
    <test>567</test>
  </Hello>
</GetData>
'SELECT Item_Idx = Nodes.value('(test)[1]' ,'INT')
FROM@XML_In.nodes('//*[local-name()=sql:variable("@XML_Path")]') Results(Nodes)

SELECT Item_Idx = Nodes.value('(test)[2]' ,'INT')
FROM@XML_In.nodes('//*[local-name()=sql:variable("@XML_Path")]') Results(Nodes)

Alternatively, you can specify several levels and return all values:

declare@Nodevarchar(max)
declare@Attributevarchar(max)

set@Attribute='World'set@Node='Hello'select@XML_In.value('(/GetData
                       /*[local-name() = sql:variable("@Node")]
                       /*[local-name() = sql:variable("@Attribute")])[1]',  'int')