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;
