Querying XML Data
XML data (stored as CLOB ) can be parsed and queried using XMLTABLE shown below. The country, state/province is setup as header and the other phone number details is parsed as lines or detail records.

SELECT xh.country, xh.state, xh.province,
  xd.area_code, xd.zones, xd.commercial, xd.residential, xd.others
FROM (SELECT XMLTYPE('
  < XRECD xmlns:xn="http://www.w3.org" >
   < xn:XREC >
    < xn:Country > US < /xn:Country >
    < xn:State > California < /xn:State >
    < xn:Province > < /xn:Province >
    < xn:Phone_Info >
     < xn:Area_Code > 213 < /xn:Area_Code >
     < xn:Zones > 281 < /xn:Zones >
     < xn:Residential > 0.23 < /xn:Residential >
     < xn:Commercial > 0.72 < /xn:Commercial >
     < xn:Others > 0.05 < /xn:Others >
    < /xn:Phone_Info >
    < xn:Phone_Info >
     < xn:Area_Code > 415 < /xn:Area_Code >
     < xn:Zones > 219 < /xn:Zones >
     < xn:Residential > 0.29 < /xn:Residential >
     < xn:Commercial > 0.65 < /xn:Commercial >
     < xn:Others > 0.06 < /xn:Others >
    < /xn:Phone_Info >
   < /xn:XREC >
   < xn:XREC >
    < xn:Country > CA < /xn:Country >
    < xn:State > < /xn:State >
    < xn:Province > Quebec < /xn:Province >
    < xn:Phone_Info >
     < xn:Area_Code > 514 < /xn:Area_Code >
     < xn:Zones > 117 < /xn:Zones >
     < xn:Residential > 0.37 < /xn:Residential >
     < xn:Commercial > 0.53 < /xn:Commercial >
     < xn:Others > 0.10 < /xn:Others >
    < /xn:Phone_Info >
   < /xn:XREC >
  < /xn:XRECD > ') as xml_out FROM dual
  ) q_xml
    ,XMLTABLE(XMLNAMESPACES('http://www.w3.org' as "xn"),
      '/XRECD/xn:XREC'
      PASSING q_xml.xml_out
        COLUMNS country VARCHAR2(7) PATH '/xn:XREC/xn:Country'
        ,state VARCHAR2(15) PATH '/xn:XREC/xn:State'
        ,province VARCHAR2(15) PATH '/xn:XREC/xn:Province'
        ,phone_info XMLTYPE PATH '/xn:XREC/*'
        ) xh
    ,XMLTABLE(XMLNAMESPACES('http://www.w3.org' as "xn"),
      '/xn:Phone_Info'
      PASSING xh.phone_info
      COLUMNS area_code NUMBER PATH '/xn:Phone_Info/xn:Area_code'
      ,zones NUMBER PATH '/xn:Phone_Info/xn:Zones'
      ,Commercial NUMBER PATH '/xn:Phone_Info/xn:Commercial'
      ,residential NUMBER PATH '/xn:Phone_Info/xn:Residential'
      ,others NUMBER PATH '/xn:Phone_Info/xn:Others'
   ) xd;



Back

Oracle registered trademark of Oracle Corporation.

Last Revised On: November 17, 2013

  1397