3

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

1
  • 1
    @Mikael Eriksson : As you may have seen, I am not an expert with XML and XQuery. I believed that by providing a small sample of the XML file, I could get some insight into the problem. I found the answer.
    – JF Landry
    May 26 at 14:38
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.