How to extract values from XML without hard coding names of elements using XQuery in SQL Server?

Question

I have xml stored in a table and table with names/paths of the elements I am interested in.

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.

Is it possible to join xml and table to get to the values of the elements?

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 .query() and .value() to get to ChildNode and Element.

Thank you for your help.

DECLARE @xml xml
SET @xml = 
'<Products>
    <RedProduct>
      <Details_RedProduct>
        <Width>1</Width>
        <Depth>2</Depth>
        <Weight>3</Weight>
      </Details_RedProduct>
    </RedProduct>
    <GreenProduct>
      <Details_GreenProduct>
        <Width>4</Width>
        <Depth>5</Depth>
        <Height>6</Height>
      </Details_GreenProduct>
      </GreenProduct>
    <BlueProduct>
      <Details_BlueProduct>
        <Width>7</Width>
        <Depth>8</Depth>
        <Lenght>9</Lenght>
      </Details_BlueProduct>
    </BlueProduct>
</Products>'

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)')

Answer 1

You can use sql:column() in your xquery expression and compare against local-name(.) to get the nodes you want.

select PE.ProductNode,
       PE.ChildNode,
       PE.Element,
       T.Col.value('.', 'int') as ElementValue
from @ProductElement as PE
  cross apply @xml.nodes('/Products/*[local-name(.) = sql:column("PE.ProductNode")]
                                   /*[local-name(.) = sql:column("PE.ChildNode")]
                                   /*[local-name(.) = sql:column("PE.Element")]') as T(Col)

Result:

ProductNode          ChildNode            Element              ElementValue
-------------------- -------------------- -------------------- ------------
RedProduct           Details_RedProduct   Width                1
GreenProduct         Details_GreenProduct Width                4
GreenProduct         Details_GreenProduct Height               6

Edit: Another version that uses a join on fields instead. It might have a better performance for you depending on what your data looks like. The first version parses the XML for each row in @ProductElement and the second version shreds the XML and uses that to join against @ProductElement.

select PE.ProductNode,
       PE.ChildNode,
       PE.Element,
       X.ElementValue
from @ProductElement as PE
  inner join (
               select T1.Col.value('local-name(.)', 'varchar(100)') as ProductNode,
                      T2.Col.value('local-name(.)', 'varchar(100)') as ChildNode,
                      T3.Col.value('local-name(.)', 'varchar(100)') as Element,
                      T3.Col.value('.',             'varchar(100)') as ElementValue
               from @xml.nodes('/Products/*') as T1(Col)
                 cross apply T1.Col.nodes('*') as T2(Col)
                 cross apply T2.Col.nodes('*') as T3(Col)
             ) as X
    on PE.ProductNode = X.ProductNode and
       PE.ChildNode = X.ChildNode and
       PE.Element = X.Element