How do I PIVOT on an XML column?

Question

Is it possible, using SQL Server 2014 Standard Edition, to PIVOT on the value of an XML column name? Ultimately I'd like to take this:

<table>
  <id>{3d2699c4-3159-4e8b-b48c-c2c4c9b5bd77}</id>
  <rows>
    <row>
      <columns>
        <column name="DESC" value="DACS" type="System.String" />
        <column name="ec_amount" value="5000" type="System.Decimal" />
        <column name="ec_exrate" value="1" type="System.Decimal" />
        <column name="ec_total" value="5000.00" type="System.Decimal" />
        <column name="ItemNo" value="PVT-C30" type="System.String" />
        <column name="UOM" value="EA" type="System.String" />
        <column name="DefaultKey" value="1" type="System.Int32" /><----THIS IS THE COLUMN ON WHICH I WOULD LIKE TO PIVOT
      </columns>
    </row>
    <row>
      <columns>
        <column name="DESC" value="DACS" type="System.String" />
        <column name="ec_amount" value="1500" type="System.Decimal" />
        <column name="ec_exrate" value="5" type="System.Decimal" />
        <column name="ec_total" value="7500.00" type="System.Decimal" />
        <column name="ItemNo" value="PVT-C30" type="System.String" />
        <column name="UOM" value="EA" type="System.String" />
        <column name="DefaultKey" value="2" type="System.Int32" />
      </columns>
    </row>
  </rows>
  <key>DefaultKey</key>
  <total>12500.00</total>
  <data />
  <parameters />
</table>

....and create these results:

DefaultKey  DESC    ec_amount   ec_exrate   ec_total    ItemNo      UOM
1           DACS    5000        1           5000        PVT-C30     EA
2           DACS    1500        5           7500        PVT-C30     EA

I've searched the Stack Overflow site and of everything I came across, these two posts came close, but they don't quite get me there:

SQL Pivot using an XML column

How do I Pivot on an XML column's attributes in T-SQL

My apologies if this has already been addressed and I simply gave up too soon.

I don't think you need to pivot anything here. You just need to get the values. — ZLK, Nov 16 '17 at 23:31
The problem with that is, there can be any number of DefaultKeys (the field on which I’d like to pivot). If you can explain how I can just “get the values”, I’d certainly give it a shot. — TheDude, Nov 17 '17 at 00:11
Never mind my previous comment, it appears it might be easiest just to do a PIVOT. I'll explain one possible method in an answer, but essentially you need something to differentiate the rows from each other in the XML. — ZLK, Nov 17 '17 at 00:53

score 1 · Accepted Answer · answered Nov 17 '17 at 00:57

You essentially need to assign a row number to each "row" of XML (or figure out some other way of distinguishing each "row"). One way you could go about this is by extracting the default key from each "row" (by using a window function and partitioning):

SELECT *
FROM
(
    SELECT Name = C2.X.value('@name', 'varchar(max)'),
           Val = C2.X.value('@value', 'varchar(max)'),
           DefaultKey = MAX(CASE WHEN C2.X.value('@name', 'varchar(max)') = 'DefaultKey' THEN C2.X.value('@value', 'varchar(max)') END) OVER(PARTITION BY C1.X)
    FROM myTable
    CROSS APPLY myXMLColumn.nodes('table/rows/row/columns') AS C1(X)
    CROSS APPLY C1.X.nodes('column') AS C2(X)
) AS T
PIVOT (MAX(Val) FOR Name IN ([DESC], [ec_amount], [ec_exrate], [ec_total], [ItemNo], [UOM])) AS P;

If DefaultKey weren't unique, you could use a row number or rank window function instead to each a similar result:

SELECT *
FROM
(
    SELECT Name = C2.X.value('@name', 'varchar(max)'),
           Val = C2.X.value('@value', 'varchar(max)'),
           RowInXML = DENSE_RANK() OVER (ORDER BY C1.X)
    FROM myTable
    CROSS APPLY myXMLColumn.nodes('table/rows/row/columns') AS C1(X)
    CROSS APPLY C1.X.nodes('column') AS C2(X)
) AS T
PIVOT (MAX(Val) FOR Name IN ([DefaultKey], [DESC], [ec_amount], [ec_exrate], [ec_total], [ItemNo], [UOM])) AS P;

Thank you ZLK. This worked beautifully. Also thank you to Shnugo, but since I already had the PIVOT structure in place, I went with ZLK's solution — TheDude, Nov 17 '17 at 17:36

Shnugo · Answer 2 · 2017-11-18T21:34:31.077

No need for pivoting... Try this

DECLARE @xml XML=N'<table>
  <id>{3d2699c4-3159-4e8b-b48c-c2c4c9b5bd77}</id>
  <rows>
    <row>
      <columns>
        <column name="DESC" value="DACS" type="System.String" />
        <column name="ec_amount" value="5000" type="System.Decimal" />
        <column name="ec_exrate" value="1" type="System.Decimal" />
        <column name="ec_total" value="5000.00" type="System.Decimal" />
        <column name="ItemNo" value="PVT-C30" type="System.String" />
        <column name="UOM" value="EA" type="System.String" />
        <column name="DefaultKey" value="1" type="System.Int32" />
      </columns>
    </row>
    <row>
      <columns>
        <column name="DESC" value="DACS" type="System.String" />
        <column name="ec_amount" value="1500" type="System.Decimal" />
        <column name="ec_exrate" value="5" type="System.Decimal" />
        <column name="ec_total" value="7500.00" type="System.Decimal" />
        <column name="ItemNo" value="PVT-C30" type="System.String" />
        <column name="UOM" value="EA" type="System.String" />
        <column name="DefaultKey" value="2" type="System.Int32" />
      </columns>
    </row>
  </rows>
  <key>DefaultKey</key>
  <total>12500.00</total>
  <data />
  <parameters />
</table>';

SELECT r.value(N'(columns/column[@name="DefaultKey"]/@value)[1]',N'int') AS DefaultKey
      ,r.value(N'(columns/column[@name="DESC"]/@value)[1]',N'nvarchar(max)') AS [DESC]
      ,r.value(N'(columns/column[@name="ec_amount"]/@value)[1]',N'decimal(10,4)') AS ec_amount
      --similar with your other elements
FROM @xml.nodes(N'/table/rows/row') AS A(r)

How do I PIVOT on an XML column?

2 Answers2