SQL Server .nodes() XML parent nodes by name

Question

    declare @xmlsample xml =
'<root>
    <solution>
        <solutionnumber>1</solutionnumber>
            <productgroup>
                <productcategory>
                    <price>100</price>
                    <title>Some product</title>
                    <tax>1</tax>
                </productcategory>
            </productgroup>
            <productcategory2>
                    <price>200</price>
                    <title>Some other product</title>
                    <tax>2</tax>
            </productcategory2>
    </solution>
    <solution>
        <solutionnumber>2</solutionnumber>
            <productcategory2>
                    <price>200</price>
                    <title>Some other product</title>
                    <tax>2</tax>
            </productcategory2>
    </solution>
</root>'

SELECT 
    --T.C.value('(./ancestor::ns1:solutionNumber)[1]', 'varchar(50)') AS solutionnumber ?? no clue
    T.C.value('(price)[1]', 'numeric(18,2)') AS price
    ,T.C.value('(title)[1]', 'varchar(50)') AS title
    ,T.C.value('(tax)[1]', 'numeric(18,2)') AS tax
FROM @xmlsample.nodes('//node()[title]') AS T(C)

A representation of the XML I am attempting to shred in SQL Server 2008 r2. I find the "title" node and grab the values I need that are in the product category. Now I would like to get the "solution number" however this could be one or more parent nodes above the product as there are certain product "groups."

How would I go about check the parent nodes by name ("solutionnumber") until I find it? Thanks for any assistance.

Answer 1

There is no direct way to my knowledge. However, you can use COALESCE to search your way up:

SELECT
    COALESCE(T.C.value('../solutionnumber[1]', 'INT'),
             T.C.value('../../solutionnumber[1]', 'INT'),
             T.C.value('../../../solutionnumber[1]', 'INT')) solutionnumber,
    T.C.value('(price)[1]', 'numeric(18,2)') AS price,
    T.C.value('(title)[1]', 'varchar(50)') AS title,
    T.C.value('(tax)[1]', 'numeric(18,2)') AS tax
  FROM
    @xmlsample.nodes('//node()[title]') AS T ( C )

Note that <solutionnumber> is really a sibling of one of the ancestors and not an ancestor itself.

This solution requires you to know the maximum depth ahead of time.

---

You can also use this solution, if you rather go forward than backwards:

SELECT solutionNodes.solutionNode.value('solutionnumber[1]','INT') AS solutionnumber,
    T.C.value('(price)[1]', 'numeric(18,2)') AS price,
    T.C.value('(title)[1]', 'varchar(50)') AS title,
    T.C.value('(tax)[1]', 'numeric(18,2)') AS tax
FROM @xmlsample.nodes('//solution') AS solutionNodes (solutionNode)
CROSS APPLY (SELECT solutionNodes.solutionNode.query('.')) solutions(solutionXML)
CROSS APPLY solutions.solutionXML.nodes('//node()[title]') T ( C )

It uses the fact that the <solutionnumber> tag is a direct child of a <solution> tag. First all <solution> tags are found. Than all its title descendants are found with a cross apply. Because you cannot use the nodes function on a node there is the calculation of "query('.')" in between.

Other than above solution, this one can handle any distance between the <solution> tag abd the <title> tag.

Answer 2

Perhaps I was going about this backwards. Multiple cross applies will do the job. Thanks to some assistance on another forum.

SELECT 
    --T.C.value('(./ancestor::ns1:solutionNumber)[1]', 'varchar(50)') AS solutionnumber ?? no clue
    m.c.value('(solutionnumber)[1]', 'int') as solutionnumber
    ,T.C.value('(price)[1]', 'numeric(18,2)') AS price
    ,T.C.value('(title)[1]', 'varchar(50)') AS title
    ,T.C.value('(tax)[1]', 'numeric(18,2)') AS tax
FROM  @xmlsample.nodes ('//solution') as m (c)
cross apply m.c.nodes ('.//node()[title]') as t(C)