Query an xmltype column

Question

Lets say I have a table as the following structure :

create table Temp( ID numeric, data xmltype );

and the content for id 1 is

<PivotSet>
<item>
    <column name = "ATTRIBUTENAME">test</column>
    <column name = "MAX(ATTRIBUTEVALUE)">testVal</column>
</item>
<item>
    <column name = "ATTRIBUTENAME">test1</column>
    <column name = "MAX(ATTRIBUTEVALUE)">test1Val</column>
</item>
<item>
    <column name = "ATTRIBUTENAME">test2</column>
    <column name = "MAX(ATTRIBUTEVALUE)">test2Val</column>
</item>

Now what I have been dying to achieve is to flatten the elements in that xml, to get to this result :

  id | test    | test 1   | test 2   |
  1  |testval  |testval1  |testval2  |

So what I want is for each item displayed have the first <column> to be columnName and the second as the value of that column.

Answer 1

SQL> select * from t;

        ID                                                                      
----------                                                                      
DATA                                                                            
--------------------------------------------------------------------------------
         1                                                                      
<PivotSet>                                                                      
  <item>                                                                        
    <column name="ATTRIBUTENAME">test</column>                                  
    <column name="MAX(ATTRIBUTEVALUE)">testVal</column>                         
  </item>                                                                       
  <item>                                                                        
    <column name="ATTRIBUTENAME">test1</column>                                 
    <column name="MAX(ATTRIBUTEVALUE)">test1Val</column>                        
  </item>                                                                       
  <item>                                                                        
    <column name="ATTRIBUTENAME">test2</column>                                 
    <column name="MAX(ATTRIBUTEVALUE)">test2Val</column>                        
  </item>                                                                       
</PivotSet>    

SQL> select 'ID' cn,
  2  max(decode(rn,1,column_name,null)) a,
  3  max(decode(rn,2,column_name,null)) b,
  4  max(decode(rn,3,column_name,null)) c,
  5  id,
  6  max(decode(rn,1,value,null)) d,
  7  max(decode(rn,2,value,null)) e,
  8  max(decode(rn,3,value,null)) f
  9  from (
 10  select id, extractValue(value(r),'item/column[@name = "ATTRIBUTENAME"]') column_name
 11  , extractValue(value(r),'item/column[@name = "MAX(ATTRIBUTEVALUE)"]') value
 12  , row_number() over(partition by id order by null) rn
 13  from t,
 14  table(XMLSequence((t.data).extract('PivotSet/item'))) r
 15  ) group by id
 16  /

CN A        B        C          ID D        E        F                          
-- -------- -------- -------- ---- -------- -------- --------                   
ID test     test1    test2       1 testVal  test1Val test2Val   

or (for new edition of the question)

SQL> select id, a, b, c
  2  from (
  3  select 'ID' id, max(decode(rn, 1, column_name)) a, max(decode(rn, 2, column_name)) b, max(decode(rn, 3, column_name)) c, 1 s
  4  from (
  5  select id, extractValue(value(r),'item/column[@name = "ATTRIBUTENAME"]') column_name
  6  , extractValue(value(r),'item/column[@name = "MAX(ATTRIBUTEVALUE)"]') value
  7  , row_number() over(partition by id order by null) rn
  8  from t,
  9  table(XMLSequence((t.data).extract('PivotSet/item'))) r
 10  )
 11  group by id
 12  union all
 13  select to_char(id), max(decode(rn, 1, value)), max(decode(rn, 2, value)), max(decode(rn, 3, value)) c, 2 s
 14  from (
 15  select id, extractValue(value(r),'item/column[@name = "ATTRIBUTENAME"]') column_name
 16  , extractValue(value(r),'item/column[@name = "MAX(ATTRIBUTEVALUE)"]') value
 17  , row_number() over(partition by id order by null) rn
 18  from t,
 19  table(XMLSequence((t.data).extract('PivotSet/item'))) r
 20  )
 21  group by id
 22  ) order by id, s
 23  /

ID                                       A        B        C                    
---------------------------------------- -------- -------- --------             
ID                                       test     test1    test2                
1                                        testVal  test1Val test2Val   

(Can't use the factoring clause due to ORA-00600 on 11.2.3)