How do I PIVOT on an XML column with XQuery?

Question

How do I PIVOT on an XML column with XQuery?

JF Landry 41

Hello,
I would like to pivot the attributes and values of an SQL XML column and display it in an ordered columns and row 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>

…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 result I get looks like this:

Then I transform this result to rotate it using the Pivot method but I find my result in are unordered:

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

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:

Yitzhak Khabinsky 26,586 Reputation points

2021-05-25T23:57:38+00:00

@JF Landry ,

Your XML is not well-formed, and also missing a namespace with the prefix dsc.
Please fix it.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
EchoLiu-MSFT 14,621 Reputation points

2021-05-26T09:48:12.96+00:00

Hi @JF Landry ,

Welcome to the microsoft TSQL Q&A forum!
Please provide the correct XML format and provide a minimal example that is sufficient to illustrate all aspects of the problem.
Yitzhak Khabinsky 26,586 Reputation points

2021-05-26T14:29:58.16+00:00

@JF Landry ,

I updated the answer after you provided ##1-3 (#4 is still missing).
Check it out.

Accepted answer

1 additional answer

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2021-05-25T23:57:38+00:00

@JF Landry ,

Your XML is not well-formed, and also missing a namespace with the prefix dsc.
Please fix it.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
EchoLiu-MSFT 14,621 Reputation points

2021-05-26T09:48:12.96+00:00

Hi @JF Landry ,

Welcome to the microsoft TSQL Q&A forum!
Please provide the correct XML format and provide a minimal example that is sufficient to illustrate all aspects of the problem.
Yitzhak Khabinsky 26,586 Reputation points

2021-05-26T14:29:58.16+00:00

@JF Landry ,

I updated the answer after you provided ##1-3 (#4 is still missing).
Check it out.

Answer 1

Hi @JF Landry ,

I took a liberty and fixed your XML based on my limited understanding of your data.
Check it out.

SQL

-- DDL and sample data population, start  
DECLARE @xml XML =  
N'<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>';  
  
WITH XMLNAMESPACES ('ModelDescriptive' AS dsc)  
SELECT c.value('@ref', 'VARCHAR(100)') AS [ref]  
	, c.value('@baseRef', 'VARCHAR(100)') AS [baseRef]  
	, c.value('@type', 'VARCHAR(100)') AS [type]  
	, c.value('@productionGroup', 'VARCHAR(100)') AS [productionGroup]  
FROM @xml.nodes('/Model/dsc:Model/dsc:Defs/dsc:Materials/dsc:Material') AS t(c);

Output

+---------------+---------+-------+-----------------+  
|      ref      | baseRef | type  | productionGroup |  
+---------------+---------+-------+-----------------+  
| E-HD-AnoAno   | E-HD    | rod   | OPTION          |  
| E-3L44A-CLEAR | E-3L44A | piece | OPTION          |  
| R-TH01        | R-TH-TH | rod   | ASSEMBLY        |  
| M-TH05        | M-TH    | meter | MOLDING         |  
+---------------+---------+-------+-----------------+

Answer 2

Hi @Yitzhak Khabinsky ,
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 had failed to give any attributes so by taking a closer look at your answer, I found my solution.

Using your sample, I found the answer. It would look more like this:

DECLARE @xml XML =  
 N'<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>';  
  
 -- DDL and sample data population, end  
  ;WITH XMLNAMESPACES ('ModelDescriptive' as dsc, 'ModelDraw' as draw, 'Display' as display)  
  SELECT c.value('@ref', 'VARCHAR(100)') AS [ref]  
     , c.value('@baseRef', 'VARCHAR(100)') AS [baseRef]  
     , c.value('@type', 'VARCHAR(100)') AS [type]  
     , c.value('@productionGroup', 'VARCHAR(100)') AS [productionGroup]  
  FROM @xml.nodes('descendant::dsc:Model/dsc:Defs/dsc:Materials/dsc:Material') AS t(c);

While looking on the web, I also found that a part was missing : descendant::dsc:Model/dsc:Defs/dsc:Materials/dsc:Material

The end result is now ordered.

Many thanks for your help!

Yitzhak Khabinsky 26,586 Reputation points

2021-05-26T14:37:17.33+00:00

Hi @JF Landry ,

Please take a look at my modified answer more closely.

Notable points:

The .nodes() XPath expression.

No need to declare all namespaces if they are not needed.

P.S. Please connect with me on LinkedIn.

Share via

How do I PIVOT on an XML column with XQuery?

1 additional answer

Your answer