oracle - parse a XML and store into a table -
i have xml below
<?xml version="1.0" encoding="utf-8"?> <cus:request xsi:schemalocation="http://www.bt.com/btgs/solutions/message/customerrequest c:\salesschema_latest\requestmanagementv1.xsd" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xmlns:sortarg="http://www.bt.com/btgs/solutions/library/sourceandtarget" xmlns:sitetype="http://www.bt.com/btgs/solutions/library/sitetype" xmlns:reqtype="http://www.bt.com/btgs/solutions/library/requesttype" <sourcesystem name="resign tool" type="lqtwebsite" /> <targetsystem name="messia" type="sales tools" /> <changerequest customerrequestref="" btprojectmanagementref=""> <customerorganisation orgid="id000001" orginternalid="29823" resigninstancetype="source"> <organisationname>bt plc</organisationname> <contactinfo contactid="id000002"> <role>customer</role> <position /> <fullname /> <telephone /> <fax /> <email /> <contactaddress> <room /> <floor /> <building /> <streetnumber /> <streetname /> <locality /> <city /> <county-state /> <country isocountrycode="" /> <postcode /> </contactaddress> </contactinfo> </customerorganisation> <customerorganisation orgid="id000003" orginternalid="29823" resigninstancetype="target"> <organisationname>bt plc</organisationname> <contactinfo contactid="id000004"> <role>customer</role> <position /> <fullname /> <telephone /> <fax /> <email /> <contactaddress> <building /> <streetnumber /> <streetname /> <city /> <county-state /> <country /> <postcode /> </contactaddress> </contactinfo> </customerorganisation> <billto siteid="id000005"> <organisation> <organisationref>id000003</organisationref> </organisation> <bilingcontact contactid="id000006"> <role>billing</role> <position /> <fullname /> <telephone /> <fax /> <email /> <contactaddress> <room /> <floor /> <building /> <streetnumber /> <streetname /> <city /> <county-state /> <country isocountrycode="" /> <postcode /> </contactaddress> </bilingcontact> </billto> <defaultcontract contractid="id000007" contractinternalid="547386" resigninstancetype="source"> <contractreference>btim789456</contractreference> <contractduration>1</contractduration> <billingfrequency>monthly</billingfrequency> </defaultcontract> <defaultcontract contractid="id000008" contractinternalid="547386" resigninstancetype="target"> <contractreference>btim789457</contractreference> <contractduration>1</contractduration> <billingfrequency>monthly</billingfrequency> </defaultcontract> <billofmaterials> <bomsiteitem siteid="id000009" siteinternalid="1163642" btsitetype="host"> <organisation> <organisationref>id000003</organisationref> </organisation> <sitecontact> <role /> <firstname /> <lastname /> <telephone /> <mobile /> <fax /> <email /> </sitecontact> <sitename>birmingham_001</sitename> <sitereference>id000009</sitereference> <siteaddress> <room /> <floor /> <building>bt building</building> <streetnumber>5</streetnumber> <streetname>brindley place</streetname> <locality>birmingham</locality> <city>birmingham</city> <county-state>west midlands</county-state> <country isocountrycode="">united kingdom</country> <postcode>b1 2bl</postcode> </siteaddress> <coordinates gridsystem="easting-and-northing"> <x-coordinate /> <y-coordinate /> </coordinates > <installationlocation locid="id000010"> <subpremise>floor 3 room 6</subpremise> </installationlocation> <siteitem lineitemid="id000011"> <itemcontract contractid="id000012"> <matrixtype>fixed link wan</matrixtype> <contractnumber /> <contractreference>btim789456</contractreference> <contractduration>1</contractduration> <contractsigneddate>2015-07-31</contractsigneddate> </itemcontract> <orderitemaction majororderaction="resign" minororderaction="resign" /> <itemquantity>1</itemquantity> <prod:product xsi:type="prod:cpeproducttype" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" instanceid="id000013" internalinventoryid="7236799"> <productid>803624</productid> <productdescription>nortel cs1000 / meridian</productdescription> <aendsite> <siteref>id000009</siteref> <locref>id000010</locref> <installationlocation> <cabinet /> <room /> <floor /> </installationlocation> </aendsite> <cpeproductspecificdetails> <param>test</param> <stringvalue>test</stringvalue> </cpeproductspecificdetails> </prod:product> </siteitem> <siteitem lineitemid="id000014"> <itemcontract contractid="id000015"> <matrixtype>fixed link wan</matrixtype> <contractnumber /> <contractreference>btim789456</contractreference> <contractduration>1</contractduration> <contractsigneddate>2015-07-31</contractsigneddate> </itemcontract> <orderitemaction majororderaction="resign" minororderaction="resign" /> <itemquantity>1</itemquantity> <prod:product xsi:type="prod:cpeitemtype" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" instanceid="id000016" internalinventoryid="7236800"> <productid>720056</productid> <producttype /> <productdescription>tmp-00003</productdescription> <btitemcode>711875</btitemcode> <installationcpeid>id000013</installationcpeid> </prod:product> </siteitem> <siteitem lineitemid="id000143"> <itemcontract contractid="id000144"> <matrixtype>fixed link wan</matrixtype> <contractnumber /> <contractreference>btim789456</contractreference> <contractduration>1</contractduration> <contractsigneddate>2015-07-31</contractsigneddate> </itemcontract> <orderitemaction majororderaction="resign" minororderaction="resign" /> <itemquantity>1</itemquantity> <prod:product xsi:type="prod:servicewraptype" instanceid="id000145" internalinventoryid="7235310" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"> <ruleid>62522</ruleid> <productid>319819</productid> <productdescription>standard care</productdescription> <aendsite> <siteref>id000009</siteref> <locref>id000010</locref> <installationlocation> <cabinet /> <room /> <floor /> </installationlocation> </aendsite> <associatedcpeid>id000067</associatedcpeid> <servicewrapproductspecificdetails> <param attributeid="3772">bt alias</param> <integervalue>7235310</integervalue> <param attributeid="2655">contract link</param> <integervalue>547386</integervalue> <param attributeid="318">equipment link</param> <integervalue>7236817</integervalue> <param attributeid="318">equipment link</param> <integervalue>7236817</integervalue> <param attributeid="318">equipment link</param> <integervalue>7236817</integervalue> <param attributeid="9553">is part of management bundle</param> <stringvalue>no</stringvalue> <param attributeid="3771">source system</param> <stringvalue>expedio</stringvalue> </servicewrapproductspecificdetails> </prod:product> </siteitem> </bomsiteitem> </billofmaterials> </changerequest> </cus:request> i want parse whole xml , values of "contactid", "role", "position" , few more contactid , store table . how can achieved.
i have tried using
set serveroutput on; declare x xmltype := xmltype( ' <changerequest customerrequestref="" btprojectmanagementref=""> <customerorganisation orgid="id000001" orginternalid="29823" resigninstancetype="source"> <organisationname>bt plc</organisationname> <contactinfo contactid="id000002"> <role>customer</role> <position /> <fullname /> <telephone /> <fax /> <email /> <contactaddress> <room /> <floor /> <building /> <streetnumber /> <streetname /> <locality /> <city /> <county-state /> <country isocountrycode="" /> <postcode /> </contactaddress> </contactinfo> </customerorganisation> <customerorganisation orgid="id000003" orginternalid="29823" resigninstancetype="target"> <organisationname>bt plc</organisationname> <contactinfo contactid="id000004"> <role>customer</role> <position /> <fullname /> <telephone /> <fax /> <email /> <contactaddress> <building /> <streetnumber /> <streetname /> <city /> <county-state /> <country /> <postcode /> </contactaddress> </contactinfo> </customerorganisation> </changerequest>'); begin r in ( select extractvalue(value(p),'/changerequest/customerorganisation/contactinfo/role/text()') name table(xmlsequence(extract(x,'/changerequest'))) p ) loop dbms_output.put_line(r.name); end loop; exception when others dbms_output.put_line(sqlerrm); end; but throws exception ora-19025: extractvalue returns value of 1 node
xmlsequence - deprecated.try xmltable xmltable.
to access xml elmeent attribute use "@attrname"
select * xmltable('/changerequest/customerorganisation' passing xmltype( ' <changerequest customerrequestref="" btprojectmanagementref=""> <customerorganisation orgid="id000001" orginternalid="29823" resigninstancetype="source"> <organisationname>bt plc</organisationname> <contactinfo contactid="id000002"> <role>customer</role> <position /> <fullname /> <telephone /> <fax /> <email /> <contactaddress> <room /> <floor /> <building /> <streetnumber /> <streetname /> <locality /> <city /> <county-state /> <country isocountrycode="" /> <postcode /> </contactaddress> </contactinfo> </customerorganisation> <customerorganisation orgid="id000003" orginternalid="29823" resigninstancetype="target"> <organisationname>bt plc</organisationname> <contactinfo contactid="id000004"> <role>customer</role> <position /> <fullname /> <telephone /> <fax /> <email /> <contactaddress> <building /> <streetnumber /> <streetname /> <city /> <county-state /> <country /> <postcode /> </contactaddress> </contactinfo> </customerorganisation> </changerequest>') columns orgid varchar2(100) path './@orgid' , contactid varchar2(100) path './contactinfo/@contactid' , role varchar2(100) path './contactinfo/role' ) 
Comments
Post a Comment