Wednesday, 9 November 2011

BULK collect XML into an OBJECT table

create or replace type ty_Rep  as object
(rep_name varchar2(40)
,prg_name varchar2(40)
,app_name varchar2(40));
/
create or replace type tb_Rep as table of ty_Rep;
/


set serveroutput on
declare

l_xml xmltype:= xmltype
('<soap:Envelope
    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
  <fetchReportDataResponse xmlns="http://SomeLocation/">
   <fetchReportDataResult>
    <feed xmlns="urn:DA.gtt.BBN.SomeLocation.Services">
     <Table xmlns="">
      <Report>Item</Report>
      <Program>PItem</Program>
      <Application>Paris</Application>
     </Table>
     <Table xmlns="">
      <Report>App Chunk</Report>
      <Program>AC</Program>
      <Application>Paris</Application>
     </Table>
     <Table xmlns="">
      <Report>Customer</Report>
      <Program>Cust</Program>
      <Application>London</Application>
     </Table>
     <Table xmlns="">
      <Report>Delivery</Report>
      <Program>Delv</Program>
      <Application>London</Application>
     </Table>
     <Table xmlns="">
      <Report>Process</Report>
      <Program>Pr</Program>
      <Application>London</Application>
     </Table>
    </feed>
   </fetchReportDataResult>
  </fetchReportDataResponse>
 </soap:Body>
</soap:Envelope>');


  l_Rep tb_Rep := tb_Rep();

begin

  select ty_Rep(x.Report, x.Program, x.Application)
  bulk collect  into  l_Rep
  from (select l_XML as m from dual) d
       ,xmltable ('//Table' passing d.m
         columns
          Report varchar2(40) path 'Report'
         ,Program varchar2(40) path 'Program'       
         ,Application varchar2(40) path 'Application') as x;

  dbms_output.put_line(l_Rep.count);
 
  for r in 1..l_Rep.count loop
    dbms_output.put_line('Report: ' || l_Rep(r).rep_name);
    dbms_output.put_line('Program: ' || l_Rep(r).prg_name);
    dbms_output.put_line('Application: ' || l_Rep(r).app_name);
  end loop;

end;
/

No comments:

Post a Comment