SQL Server .nodes() XML parent nodes by name

sql-server, sql-server-2008-r2, xml

Solution

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)

Problem

``` 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.

Original source