title: How to extract values from XML without hard coding names of elements using XQuery in SQL Server?
body: <p>I have xml stored in a table and table with names/paths of the elements I am interested in. </p>
  
  <p>I would like to extract values of these elements without hard coding paths and/or names of the elements. Structure of my xml will not change, it will always have parent/child/element. </p>
  
  <p>Is it possible to join xml and table to get to the values of the elements?</p>
  
  <p>Below is example of what I was able to get. I think it is possible to extend this solution to JOIN on ChildNode and Element but not sure how to use <code>.query()</code> and <code>.value()</code> to get to ChildNode and Element.</p>
  
  <p>Thank you for your help. </p>
  
  <pre><code>DECLARE @xml xml
  SET @xml = 
  '&lt;Products&gt;
      &lt;RedProduct&gt;
        &lt;Details_RedProduct&gt;
          &lt;Width&gt;1&lt;/Width&gt;
          &lt;Depth&gt;2&lt;/Depth&gt;
          &lt;Weight&gt;3&lt;/Weight&gt;
        &lt;/Details_RedProduct&gt;
      &lt;/RedProduct&gt;
      &lt;GreenProduct&gt;
        &lt;Details_GreenProduct&gt;
          &lt;Width&gt;4&lt;/Width&gt;
          &lt;Depth&gt;5&lt;/Depth&gt;
          &lt;Height&gt;6&lt;/Height&gt;
        &lt;/Details_GreenProduct&gt;
        &lt;/GreenProduct&gt;
      &lt;BlueProduct&gt;
        &lt;Details_BlueProduct&gt;
          &lt;Width&gt;7&lt;/Width&gt;
          &lt;Depth&gt;8&lt;/Depth&gt;
          &lt;Lenght&gt;9&lt;/Lenght&gt;
        &lt;/Details_BlueProduct&gt;
      &lt;/BlueProduct&gt;
  &lt;/Products&gt;'
  
  DECLARE @ProductElement table (ProductNode nvarchar(100), ChildNode nvarchar(100), Element nvarchar(20))
  INSERT INTO @ProductElement SELECT 'RedProduct','','Width'
  INSERT INTO @ProductElement SELECT 'GreenProduct','','Width'
  INSERT INTO @ProductElement SELECT 'GreenProduct','','Height'
  UPDATE @ProductElement SET ChildNode = 'Details_' + ProductNode
  
  SELECT ProductsCollection.query('local-name(.)').value('.','nvarchar(100)') as TestOutput
  FROM @xml.nodes('//Products/*') productsXml (ProductsCollection)
  INNER JOIN @ProductElement el ON el.ProductNode = ProductsCollection.query('local-name(.)').value('.','nvarchar(100)')
  </code></pre>