Hi, I'm new to GA so bear with me please. I don't have a lots and
lots of experience with loading XML to oracle. That said however, I
recently solved this problem for myself. Here is what I learned:
There are two basic methods of loading XML to Oracle, 1) use the XML
DB stuff and register a schema etc. as you have described or 2) parse
the XML file and load it via pl/sql. I use method 2 (parsing the xml
file and using pl/sql to work the results). I choose this method
because I have found an easy and consistent way to do it. The trick
is use of cursor expressions. Since you want an short answer and
example, here it is (should you need anything further, just ask and
I'll add more details till you are satisfied):
1) I ask people to pass me xml as a clob
2) I use cusor expressions to break up the hierarcy of the xml data
3) then just load what now is just rows/colums
a) this is an actual test case I was using to show other people
b) if you create the tables empty then the case will compile and run for you
c) the interesting points are
table(xmlsequence(extract(extract(value(...
cursor(select...
d) I cast elemental components into their original datatypes during the process
It is not as bad as it looks. Indeed, once you get it, it is
amazingly easy to repeat for anything. Have fun with it. Again, if
you need more details or want to discuss why I like this method over
registering schemas then let me know.
Kevin
SQL> @desc2 xpolicy
Name Null? Type
-------------------------------------- -------- -----------------
POLICY_ID NOT NULL NUMBER(38)
POLICY_NUMBER NOT NULL VARCHAR2(10)
SQL> @desc2 xtransaction
Name Null? Type
-------------------------------------- -------- -----------------
TRANSACTION_ID NOT NULL NUMBER(38)
TRANSACTION_DATE NOT NULL DATE
POLICY_ID NOT NULL NUMBER(38)
SQL> @desc2 xoccoverage
Name Null? Type
-------------------------------------- -------- -----------------
OCCOVERAGE_ID NOT NULL NUMBER(38)
OCCOVERAGE_TYPE NOT NULL VARCHAR2(10)
TRANSACTION_ID NOT NULL NUMBER(38)
SQL> @desc2 xcommodity
Name Null? Type
-------------------------------------- -------- -----------------
COMMODITY_ID NOT NULL NUMBER(38)
COMMODITY_NAME NOT NULL VARCHAR2(30)
OCCOVERAGE_ID NOT NULL NUMBER(38)
SQL> @desc2 xroute
Name Null? Type
-------------------------------------- -------- -----------------
ROUTE_ID NOT NULL NUMBER(38)
ROUTE_START_NAME VARCHAR2(30)
ROUTE_END_NAME VARCHAR2(30)
OCCOVERAGE_ID NOT NULL NUMBER(38)
SQL> @desc2 xbrcoverage
Name Null? Type
-------------------------------------- -------- -----------------
BRCOVERAGE_ID NOT NULL NUMBER(38)
BRCOVERAGE_TYPE NOT NULL VARCHAR2(10)
TRANSACTION_ID NOT NULL NUMBER(38)
SQL> @desc2 xlocation
Name Null? Type
-------------------------------------- -------- -----------------
LOCATION_ID NOT NULL NUMBER(38)
LOCATION_NAME VARCHAR2(30)
BRCOVERAGE_ID NOT NULL NUMBER(38)
SQL>
set serveroutput on
create or replace
procedure shread_xml (xml_as_clob_p in clob) is
cpolicy sys_refcursor;
rpolicy xpolicy%rowtype;
ctransaction sys_refcursor;
rtransaction xtransaction%rowtype;
coccoverage sys_refcursor;
roccoverage xoccoverage%rowtype;
ccommodity sys_refcursor;
rcommodity xcommodity%rowtype;
croute sys_refcursor;
rroute xroute%rowtype;
cbrcoverage sys_refcursor;
rbrcoverage xbrcoverage%rowtype;
clocation sys_refcursor;
rlocation xlocation%rowtype;
begin
open cpolicy for
select
cast(extractvalue(value(tpolicy),'/POLICIES_ROW/POLICY_ID')
as number) policy_id
,cast(extractvalue(value(tpolicy),'/POLICIES_ROW/POLICY_NUMBER')
as varchar2(10)) policy_number
,cursor(
select
cast(extractvalue(value(ttransaction),'/TRANSACTIONS_ROW/TRANSACTION_ID')
as number) transaction_id
,cast(extractvalue(value(ttransaction),'/TRANSACTIONS_ROW/TRANSACTION_DATE')
as date) transaction_date
,cast(extractvalue(value(ttransaction),'/TRANSACTIONS_ROW/POLICY_ID')
as number) policy_id
,cursor(
select
cast(extractvalue(value(toccoverage),'/OCCOVERAGES_ROW/OCCOVERAGE_ID')
as number) occoverage_id
,cast(extractvalue(value(toccoverage),'/OCCOVERAGES_ROW/OCCOVERAGE_TYPE')
as varchar2(10)) occoverage_type
,cast(extractvalue(value(toccoverage),'/OCCOVERAGES_ROW/TRANSACTION_ID')
as number) transaction_id
,cursor(
select
cast(extractvalue(value(tcommodity),'/COMMODITIES_ROW/COMMODITY_ID')
as number) commodity_id
,cast(extractvalue(value(tcommodity),'/COMMODITIES_ROW/COMMODITY_NAME')
as varchar2(30)) commodity_name
,cast(extractvalue(value(tcommodity),'/COMMODITIES_ROW/OCCOVERAGE_ID')
as number) occoverage_id
from
table(xmlsequence(extract(extract(value(toccoverage),'/OCCOVERAGES_ROW/COMMODITIES'),'/COMMODITIES/COMMODITIES_ROW')))
tcommodity
) commodities
,cursor(
select
cast(extractvalue(value(troute),'/ROUTES_ROW/ROUTE_ID') as number)
route_id
,cast(extractvalue(value(troute),'/ROUTES_ROW/ROUTE_START_NAME') as
varchar2(30)) route_start_name
,cast(extractvalue(value(troute),'/ROUTES_ROW/ROUTE_END_NAME') as
varchar2(30)) route_end_name
,cast(extractvalue(value(troute),'/ROUTES_ROW/OCCOVERAGE_ID') as
number) occoverage_id
from
table(xmlsequence(extract(extract(value(toccoverage),'/OCCOVERAGES_ROW/ROUTES'),'/ROUTES/ROUTES_ROW')))
troute
) routes
from
table(xmlsequence(extract(extract(value(ttransaction),'/TRANSACTIONS_ROW/OCCOVERAGES'),'/OCCOVERAGES/OCCOVERAGES_ROW')))
toccoverage
) occoverages
,cursor(
select
cast(extractvalue(value(tbrcoverage),'/BRCOVERAGES_ROW/BRCOVERAGE_ID')
as number) brcoverage_id
,cast(extractvalue(value(tbrcoverage),'/BRCOVERAGES_ROW/BRCOVERAGE_TYPE')
as varchar2(10)) brcoverage_type
,cast(extractvalue(value(tbrcoverage),'/BRCOVERAGES_ROW/TRANSACTION_ID')
as number) transaction_id
,cursor(
select
cast(extractvalue(value(tlocation),'/LOCATIONS_ROW/LOCATION_ID') as
number) location_id
,cast(extractvalue(value(tlocation),'/LOCATIONS_ROW/LOCATION_NAME') as
varchar2(30)) location_name
,cast(extractvalue(value(tlocation),'/LOCATIONS_ROW/BRCOVERAGE_ID') as
number) brcoverage_id
from
table(xmlsequence(extract(extract(value(tbrcoverage),'/BRCOVERAGES_ROW/LOCATIONS'),'/LOCATIONS/LOCATIONS_ROW')))
tlocation
) locations
from
table(xmlsequence(extract(extract(value(ttransaction),'/TRANSACTIONS_ROW/BRCOVERAGES'),'/BRCOVERAGES/BRCOVERAGES_ROW')))
tbrcoverage
) brcoverages
from
table(xmlsequence(extract(extract(value(tpolicy),'/POLICIES_ROW/TRANSACTIONS'),'/TRANSACTIONS/TRANSACTIONS_ROW')))
ttransaction
) transactions
from table(xmlsequence(extract(extract(xmltype(xml_as_clob_p),'/POLICIES'),'/POLICIES/POLICIES_ROW')))
tpolicy
;
loop
fetch cpolicy into rpolicy.policy_id,rpolicy.policy_number,ctransaction;
if cpolicy%notfound then exit; end if;
dbms_output.put_line(rpolicy.policy_id);
loop
fetch ctransaction into
rtransaction.transaction_id,rtransaction.transaction_date,rtransaction.policy_id,coccoverage,cbrcoverage;
if ctransaction%notfound then exit; end if;
dbms_output.put_line(rtransaction.transaction_id);
loop
fetch coccoverage into
roccoverage.occoverage_id,roccoverage.occoverage_type,roccoverage.transaction_id,ccommodity,croute;
if coccoverage%notfound then exit; end if;
dbms_output.put_line(roccoverage.occoverage_id);
loop
fetch ccommodity into
rcommodity.commodity_id,rcommodity.commodity_name,rcommodity.occoverage_id;
if ccommodity%notfound then exit; end if;
dbms_output.put_line(rcommodity.commodity_id);
end loop;
loop
fetch croute into
rroute.route_id,rroute.route_start_name,rroute.route_end_name,rroute.occoverage_id;
if croute%notfound then exit; end if;
dbms_output.put_line(rroute.route_id);
end loop;
end loop;
loop
fetch cbrcoverage into
rbrcoverage.brcoverage_id,rbrcoverage.brcoverage_type,rbrcoverage.transaction_id,clocation;
if cbrcoverage%notfound then exit; end if;
dbms_output.put_line(rbrcoverage.brcoverage_id);
loop
fetch clocation into
rlocation.location_id,rlocation.location_name,rlocation.brcoverage_id;
if clocation%notfound then exit; end if;
dbms_output.put_line(rlocation.location_id);
end loop;
end loop;
end loop;
end loop;
close cpolicy;
end;
/
show errors
/*
declare
clobv clob :=
'<?xml version="1.0"?>'
||'<POLICIES>'
||' <POLICIES_ROW>'
||' <POLICY_ID>39843423</POLICY_ID>'
||' <POLICY_NUMBER>POL111</POLICY_NUMBER>'
||' <TRANSACTIONS>'
||' <TRANSACTIONS_ROW>'
||' <TRANSACTION_ID>39843424</TRANSACTION_ID>'
||' <TRANSACTION_DATE>03-JUL-05</TRANSACTION_DATE>'
||' <POLICY_ID>39843423</POLICY_ID>'
||' <OCCOVERAGES>'
||' <OCCOVERAGES_ROW>'
||' <OCCOVERAGE_ID>39843425</OCCOVERAGE_ID>'
||' <OCCOVERAGE_TYPE>OCCTYPE111</OCCOVERAGE_TYPE>'
||' <TRANSACTION_ID>39843424</TRANSACTION_ID>'
||' <COMMODITIES>'
||' <COMMODITIES_ROW>'
||' <COMMODITY_ID>39843426</COMMODITY_ID>'
||' <COMMODITY_NAME>COMM111</COMMODITY_NAME>'
||' <OCCOVERAGE_ID>39843425</OCCOVERAGE_ID>'
||' </COMMODITIES_ROW>'
||' </COMMODITIES>'
||' <ROUTES>'
||' <ROUTES_ROW>'
||' <ROUTE_ID>39843427</ROUTE_ID>'
||' <ROUTE_START_NAME>START111</ROUTE_START_NAME>'
||' <ROUTE_END_NAME>END111</ROUTE_END_NAME>'
||' <OCCOVERAGE_ID>39843425</OCCOVERAGE_ID>'
||' </ROUTES_ROW>'
||' </ROUTES>'
||' </OCCOVERAGES_ROW>'
||' </OCCOVERAGES>'
||' <BRCOVERAGES>'
||' <BRCOVERAGES_ROW>'
||' <BRCOVERAGE_ID>39843428</BRCOVERAGE_ID>'
||' <BRCOVERAGE_TYPE>BRCTYPE111</BRCOVERAGE_TYPE>'
||' <TRANSACTION_ID>39843424</TRANSACTION_ID>'
||' <LOCATIONS>'
||' <LOCATIONS_ROW>'
||' <LOCATION_ID>39843429</LOCATION_ID>'
||' <LOCATION_NAME>LOC111</LOCATION_NAME>'
||' <BRCOVERAGE_ID>39843428</BRCOVERAGE_ID>'
||' </LOCATIONS_ROW>'
||' </LOCATIONS>'
||' </BRCOVERAGES_ROW>'
||' </BRCOVERAGES>'
||' </TRANSACTIONS_ROW>'
||' </TRANSACTIONS>'
||' </POLICIES_ROW>'
||'</POLICIES>'
;
begin
shread_xml (clobv);
end;
/
*/ |
