Parse xmltype into table after pivot xml

Question

i have return XMLTYPE after pivot xml that looks like

<PivotSet>
<item>
        <column name = "country">Ukraine</column>
        <column name = "population">12345</column>
    </item>
    <item>
        <column name = "country">Germany</column>
        <column name = "population">67891</column>
    </item>
...
</PivotSet>

i need to get table that will look like

Ukraine_population|Germany_population|...

12345             |67891             |...

Answer 1

You can get the country value with this XPath expression:

/PivotSet/item/column[@name="country"]

And a similar one for the population. Giving:

with x as (
  select xmltype ( 
'<PivotSet>
<item>
        <column name = "country">Ukraine</column>
        <column name = "population">12345</column>
    </item>
    <item>
        <column name = "country">Germany</column>
        <column name = "population">67891</column>
    </item>
</PivotSet>' 
) x from dual
)
select xt.* from x, xmltable (
  '/PivotSet/item'
  passing x.x 
  columns 
    country varchar2(100) 
      path 'column[@name="country"]',
    population int
      path 'column[@name="population"]'
) xt

COUNTRY   POPULATION   
Ukraine        12345         
Germany        67891     

But if you want a column for each country, from here you still need to pivot the data to get the result!

Which begs the question:

Why use an XML pivot?

If it's because you don't know the country names and are doing something like:

pivot xml ( 
  min ( population ) 
    for country in ( any ) 
)

This hasn't saved you anything! You still need to know the country names to get the output as columns.