SQL & PL/SQL

Guys,

I have already posted this in the XML DB forum, but am relying that some of you might help me.


I have been searching for a solution for this and couldn't quite get it. I'm trying to get attribute values from multiple entries in a Node. It workes for a single node, but for multiple nodes, it fails. Could you please give me a solution?

SQL> select extractvalue(column_value, '/WORLD/COUNTRY/@NAME') COUNTRY_NAME
  2        ,extractvalue(column_value, '/WORLD/COUNTRY/CITY/@NAME') CITY_NAME
  3        ,extractvalue(column_value, '/WORLD/COUNTRY/CITY/@POPULATION') POPULATION
  4  from xmltable('WORLD' passing xmltype('
  5  <WORLD>
  6  <COUNTRY NAME="INDIA">
  7  <CITY NAME="MUMBAI" POPULATION="10000"></CITY>
  8  </COUNTRY>
  9  </WORLD>'
 10  ));
 
COUNTRY_NAME CITY_NAME POPULATION
---------------------- ------------------ -------------------
INDIA MUMBAI 10000

When I execute the same for multiple nodes, it fails, please guide me. Please also let me know how to handle if I have an XML file containing this data.

SQL> select extractvalue(column_value, '/WORLD/COUNTRY/@NAME') COUNTRY_NAME
  2        ,extractvalue(column_value, '/WORLD/COUNTRY/CITY/@NAME') CITY_NAME
  3        ,extractvalue(column_value, '/WORLD/COUNTRY/CITY/@POPULATION') POPULATION
  4  from xmltable('WORLD' passing xmltype('
  5  <WORLD>
  6  <COUNTRY NAME="INDIA">
  7  <CITY NAME="MUMBAI" POPULATION="10000"></CITY>
  8  <CITY NAME="CHENNAI" POPULATION="2000"></CITY>
  9  <CITY NAME="BANGALORE" POPULATION="13000"></CITY>
 10  <CITY NAME="HYDERABAD" POPULATION="14000"></CITY>
 11  </COUNTRY>
 12  </WORLD>'
 13  ));
select extractvalue(column_value, '/WORLD/COUNTRY/@NAME') COUNTRY_NAME
                                                                  *
ERROR at line 1:
ORA-19025: EXTRACTVALUE returns value of only one node

Cheers
Sarma.