Parse xmltype into table after pivot xml

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"]') xtCOUNTRY   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.