Sql-server – Properly pivot an XML column with XQuery

pivotsql server 2014xmlxquery

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:

<Model xmlns:dsc="ModelDescriptive" xmlns:draw="ModelDraw" xmlns:display="Display" xmlns:thd="Model3D">
  <dsc:Model name="15DE">
    <dsc:Defs>
      <dsc:Materials>
        <dsc:Material ref="E-HD-AnoAno" baseRef="E-HD" type="rod" productionGroup="OPTION" />
        <dsc:Material ref="E-3L44A-CLEAR" baseRef="E-3L44A" type="piece" productionGroup="OPTION" />
        <dsc:Material ref="R-TH01" baseRef="R-TH-TH" type="rod" productionGroup="ASSEMBLY" />
        <dsc:Material ref="M-TH05" baseRef="M-TH" type="meter" productionGroup="MOLDING" />
      </dsc:Materials>
    </dsc:Defs>
  </dsc:Model>
</Model>

…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:

enter image description here

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:

enter image description here

I am a little confused. I would like to have my result formatted this way but ordered. What is wrong with my reasoning?

I would like my result to be presented like this:

enter image description here

Best Answer

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.