Properly pivot an XML column with XQuery

I would like to pivot the attributes and values of an SQL XML column and display it in an ordered columns and rows format.

To get started, this is what the XML column looks like in SQL:














…and here is my query :

SELECT
      x.y.value('local-name(..)', 'VARCHAR(MAX)') ParentElementName
     ,x.y.value('local-name(.)', 'VARCHAR(MAX)') Attribut
     ,x.y.value('.', 'VARCHAR(MAX)') Value
     ,Row_number() Over(Partition by x.y.value('local-name(..)', 'VARCHAR(MAX)') order by x.y.value('local-name(..)', 'VARCHAR(MAX)') ASC)  as rn
 FROM @xmlDescriptive.nodes('//*[text()], //@*') AS x(y)
 WHERE x.y.value('local-name(..)', 'VARCHAR(MAX)')= 'Material'
 ORDER BY x.y.value('local-name(..)', 'VARCHAR(MAX)')

I get an interesting result using the Nodes method of XQuery and filtering only on the material from the bill of material (BOM).

However, I would like to present the data in a standardized fashion.

The first result I get looks like this:

Then I transform this result to rotate it using the Pivot method. Here is my method of trying to pivot my information:

SELECT [ref], [baseRef], [type], [productionGroup]
 FROM 
 (
     SELECT
          x.y.value('local-name(..)', 'VARCHAR(MAX)') ParentElementName
         ,x.y.value('local-name(.)', 'VARCHAR(MAX)') Attribut
         ,x.y.value('.', 'VARCHAR(MAX)') Valeur
         ,CAST(Row_number() Over(Partition by x.y.value('local-name(.)', 'VARCHAR(MAX)') order by x.y.value('local-name(..)', 'VARCHAR(MAX)') ASC) as varchar(10)) as rn
     FROM @xmlDescriptive.nodes('//@*') AS x(y)
     WHERE x.y.value('local-name(..)', 'VARCHAR(MAX)')= 'Material'
    
 ) d
 pivot
 (
   max(Valeur)
   FOR Attribut in ([ref], [baseRef], [type], [productionGroup])
 ) piv

And I find my result in are unordered. The column ref does not match my baseRef, type and productGroup columns:

I am a little confused. I would like to have my result formatted this way but orde

I'm probably missing the point of your question but here is what I would do to get the result you are looking for.

with xmlnamespaces('ModelDescriptive' as dsc)
select M.X.value('@ref', 'varchar(max)') as ref,
       M.X.value('@baseRef', 'varchar(max)') as baseRef,
       M.X.value('@type', 'varchar(max)') as type,
       M.X.value('@productionGroup', 'varchar(max)') as productionGroup
from @xmlDescriptive.nodes('//dsc:Material') as M(X);

Your use of local-name() and //[text()], //@' suggests you want some kind of generic pivot but you use Material and the attribute names hard coded in the query so I don't know why you went down that road.

StackExchange Database Administrators Q#292290, answer score: 5