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;
(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