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:

{3d2699c4-3159-4e8b-b48c-c2c4c9b5bd77}
    
      
        
        
        
        
        
        
        <----THIS IS THE COLUMN ON WHICH I WOULD LIKE TO PIVOT
      
    
    
      
        
        
        
        
        
        
        
      
    
  DefaultKey12500.00

....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.

ZLK · Accepted Answer

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;

How do I PIVOT on an XML column?

Answers (2)

Related Questions