Parsing XML attributes in PL/SQL query

Question

Please check for XML below in PL/SQL below and output should like this:

CITY_NAME   POPULATION
MUMBAI  6780000
DELHI   5100000
HYDERABAD   2480000
CHENNAI 6100000

I'm using the query below but is there a better way to handle instead of joining on rownum. I acknowledge that XML format could be better in handling XML attributes.

WITH t as (select XMLTYPE('
<ROOT>
    <CITY_INFO>
        <CITY>
          <CITY_NAME Value="MUMBAI"/>
          <POPULATION Value="6780000" OldValue=""/>
        </CITY>
        <CITY>
          <CITY_NAME Value="DELHI"/>
          <POPULATION Value="5100000" OldValue=""/>
        </CITY>
    </CITY_INFO>
    <CITY_INFO>
        <CITY>
          <CITY_NAME Value="HYDERABAD"/>
          <POPULATION Value="2480000" OldValue=""/>
        </CITY>
        <CITY>
          <CITY_NAME Value="CHENNAI"/>
          <POPULATION Value="6100000" OldValue=""/>
        </CITY>
    </CITY_INFO>
</ROOT>
') as xml from dual)
select a.CITY_NAME, b.population from (select c.*, rownum as row_num
from t
           ,XMLTABLE('/ROOT/CITY_INFO/CITY/CITY_NAME'
              PASSING t.xml
              COLUMNS CITY_NAME VARCHAR2(100) PATH '/CITY_NAME/@Value'
             ) c) a,
             (select pop.*, rownum as row_num
             from t
           ,XMLTABLE('/ROOT/CITY_INFO/CITY/POPULATION'
              PASSING t.xml
              COLUMNS POPULATION NUMBER PATH '/POPULATION/@Value'
             ) pop) b where a.row_num = b.row_num

Are you getting any error or is your current output not what it "should" be? Add these details to your question. If your current output is different than add the current output. — Ram
– Ram, Commented Feb 11, 2015 at 15:03
The output from the query is same as expected but innerjoin on rownum didn't look clean. Answer posted below achieves the output without parsing twice and joining. — Krish Y
– Krish Y, Commented Feb 11, 2015 at 15:43

Boneist · Accepted Answer · 2015-02-11 15:12:31Z

2

By changing the "root path" in the xmltable to be the level before the CITY_NAME and POPULATION nodes, and referencing the CITY_NAME and POPULATION nodes via //, the following works for me:

WITH t as (select XMLTYPE('
<ROOT>
    <CITY_INFO>
        <CITY>
          <CITY_NAME Value="MUMBAI"/>
          <POPULATION Value="6780000" OldValue=""/>
        </CITY>
        <CITY>
          <CITY_NAME Value="DELHI"/>
          <POPULATION Value="5100000" OldValue=""/>
        </CITY>
    </CITY_INFO>
    <CITY_INFO>
        <CITY>
          <CITY_NAME Value="HYDERABAD"/>
          <POPULATION Value="2480000" OldValue=""/>
        </CITY>
        <CITY>
          <CITY_NAME Value="CHENNAI"/>
          <POPULATION Value="6100000" OldValue=""/>
        </CITY>
    </CITY_INFO>
</ROOT>
') as xml from dual)
select a.CITY_NAME, a.population 
from   t,
       XMLTABLE('/ROOT/CITY_INFO/CITY'
              PASSING t.xml
              COLUMNS CITY_NAME VARCHAR2(100) PATH '//CITY_NAME/@Value',
              POPULATION NUMBER PATH '//POPULATION/@Value'
             ) a;

CITY_NAME            POPULATION
-------------------- ----------
MUMBAI                  6780000
DELHI                   5100000
HYDERABAD               2480000
CHENNAI                 6100000

answered Feb 11, 2015 at 15:12

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Krish Y Over a year ago

Thank you that works. Sorry can't vote up as I am a newb

Collectives™ on Stack Overflow

Parsing XML attributes in PL/SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related