title: SQL Variable Containing Path in XML Node
body: <p>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 <code>local-name</code> and <code>sql:variable</code>. The example below works as expected:</p>
  
  <pre><code>DECLARE @XML_Path VARCHAR(MAX) 
        , @XML_In XML
  
  
  SET @XML_Path = 'GetData'
  
  SET @XML_In = 
  '
  &lt;GetData&gt;
    &lt;test&gt;234&lt;/test&gt;
  &lt;/GetData&gt;
  '
  
  --THIS WORKS
  SELECT Item_Idx = Nodes.value('(test)[1]' ,'INT')
  FROM   @XML_In.nodes('/*[local-name()=sql:variable("@XML_Path")]') Results(Nodes)
  </code></pre>
  
  <p>However, it does not work when I try to pass a path instead of a single node:</p>
  
  <pre><code>SET @XML_Path = 'GetData/Hello'
  
  SET @XML_In = 
  '
  &lt;GetData&gt;
    &lt;Hello&gt;
      &lt;test&gt;234&lt;/test&gt;
    &lt;/Hello&gt;
  &lt;/GetData&gt;
  '
  
  --THIS DOES NOT WORK
  SELECT Item_Idx = Nodes.value('(test)[1]' ,'INT')
  FROM   @XML_In.nodes('/*[local-name()=sql:variable("@XML_Path")]') Results(Nodes)
  </code></pre>
  
  <p>I need to be able to pass a path preceding another part of the path that is always constant. So, in my example, the "test" part would always be the same but the path above it would vary.</p>
  
  <p>I suspect that <code>local-name</code> is not the way to do this. But is there a different way I can do this?</p>
  
  <p>Any help would be appreciated.</p>
  
  <p><strong><em>I've thought about using dynamic SQL, but this code would be used inside a UDF returning a table of results.</em></strong></p>
  
  <hr>
  
  <p><strong>Edit:</strong> </p>
  
  <p>I believe I was unclear about what I am trying to do. I need to be able to pass different XML paths into a function that uses them to shred XML. </p>
  
  <p><strong>Example 1</strong></p>
  
  <pre><code>DECLARE @XML_In XML
  
  SELECT @XML_In = 
  '
  &lt;RootNode1&gt;
    &lt;ExtraNode1&gt;
      &lt;Items&gt;
        &lt;ExampleItem&gt;
          &lt;SomeNode&gt;100&lt;/SomeNode&gt;
          &lt;Attributes&gt;
            &lt;ID&gt;1&lt;/ID&gt;
            &lt;Name&gt;a&lt;/Name&gt;
            &lt;Value&gt;123a&lt;/Value&gt;
          &lt;/Attributes&gt;
        &lt;/ExampleItem&gt;
        &lt;ExampleItem&gt;
          &lt;SomeNode&gt;200&lt;/SomeNode&gt;
          &lt;Attributes&gt;
            &lt;ID&gt;2&lt;/ID&gt;
            &lt;Name&gt;b&lt;/Name&gt;
            &lt;Value&gt;234&lt;/Value&gt;
          &lt;/Attributes&gt;
        &lt;/ExampleItem&gt;
        &lt;ExampleItem&gt;
          &lt;SomeNode&gt;300&lt;/SomeNode&gt;
          &lt;Attributes&gt;
            &lt;ID&gt;3&lt;/ID&gt;
            &lt;Name&gt;c&lt;/Name&gt;
            &lt;Value&gt;345&lt;/Value&gt;
          &lt;/Attributes&gt;
        &lt;/ExampleItem&gt;
      &lt;/Items&gt;
    &lt;/ExtraNode1&gt;
  &lt;/RootNode1&gt;
  '
  
      SELECT      SomeNode  = Nodes.value('(../SomeNode)[1]' ,'INT')  
                , ID        = Nodes.value('(ID)[1]'       ,'INT')         
                , Name      = Nodes.value('(Name)[1]'     ,'VARCHAR(100)')     
                , Value     = Nodes.value('(Value)[1]'    ,'NVARCHAR(MAX)')
      FROM        @XML_In.nodes('/RootNode1/ExtraNode1/Items/ExampleItem/Attributes') Results(Nodes)
  </code></pre>
  
  <p><strong>Example 2</strong></p>
  
  <pre><code>DECLARE @XML_In XML
  
  SELECT @XML_In = 
  '
  &lt;Example2Root&gt;
    &lt;Entries&gt;
      &lt;Entry&gt;
        &lt;SomeNode&gt;100&lt;/SomeNode&gt;
        &lt;Fields&gt;
          &lt;ID&gt;1&lt;/ID&gt;
          &lt;Name&gt;a&lt;/Name&gt;
          &lt;Value&gt;123a&lt;/Value&gt;
        &lt;/Fields&gt;
      &lt;/Entry&gt;
      &lt;Entry&gt;
        &lt;SomeNode&gt;200&lt;/SomeNode&gt;
        &lt;Fields&gt;
          &lt;ID&gt;2&lt;/ID&gt;
          &lt;Name&gt;b&lt;/Name&gt;
          &lt;Value&gt;234&lt;/Value&gt;
        &lt;/Fields&gt;
      &lt;/Entry&gt;
      &lt;Entry&gt;
        &lt;SomeNode&gt;300&lt;/SomeNode&gt;
        &lt;Fields&gt;
          &lt;ID&gt;3&lt;/ID&gt;
          &lt;Name&gt;c&lt;/Name&gt;
          &lt;Value&gt;345&lt;/Value&gt;
        &lt;/Fields&gt;
      &lt;/Entry&gt;
    &lt;/Entries&gt;
  &lt;/Example2Root&gt;
  '
  
      SELECT      SomeNode  = Nodes.value('(../SomeNode)[1]' ,'INT')  
                , ID        = Nodes.value('(ID)[1]'       ,'INT')         
                , Name      = Nodes.value('(Name)[1]'     ,'VARCHAR(100)')     
                , Value     = Nodes.value('(Value)[1]'    ,'NVARCHAR(MAX)')
      FROM        @XML_In.nodes('/Example2Root/Entries/Entry/Fields') Results(Nodes)
  </code></pre>
  
  <hr>
  
  <p>I want to be able to pass the path to the function so it can do this and the return it as a table:</p>
  
  <pre><code>SELECT      SomeNode  = Nodes.value('(../SomeNode)[1]' ,'INT')  
            , ID        = Nodes.value('(ID)[1]'       ,'INT')         
            , Name      = Nodes.value('(Name)[1]'     ,'VARCHAR(100)')     
            , Value     = Nodes.value('(Value)[1]'    ,'NVARCHAR(MAX)')
  FROM        @XML_In.nodes('SOME_PATH') Results(Nodes)
  </code></pre>
  
  <p>But I don't know how to go about using a parameter for the path. The path will be different most times.</p>