Hello Tom,
We have an XML in a remote table which is stored as a CLOB column Type , this is stored in a local table local_table.
The XML is Stored in the following format:
<Transaction> 1
<Legs> n
<payments> n
i.e for each transaction there may be 0 or more leg , for each leg there may be 0 or more than one payment .
For resolving this we created 3 temporary tables locally and tried to extract and upload the transaction, transaction_legs and transaction_payments into respective tables . The below is
for i in ( select xmltype(clob_data) as clob_data,
from local_table
)
loop
for r1 in (
select x.column_value,
extractvalue(x.column_value,'/Transaction/TradeVersion') tradeversion,
extractvalue(x.column_value,'/Transaction/TransactionID') transactionid
from table(xmlsequence(extract(i. clob_data,'/Transaction'))) x
) loop
-- dbms_output.put_line('transaction id '||r1.transactionid);
g_transaction_id := r1.transactionid;
insert into tran values(
,r1.TRADEVERSION
,r1.TRANSACTIONID
for r2 in (
select y.column_value,
extractvalue(y.column_value,'/Leg/Basis') leg_basis,
extractValue(y.column_value,'/Leg/FixedRate') leg_fixedrate
from table(XMLSequence(extract(r1.column_value,'//Leg'))) y
) loop
-- dbms_output.put_line(r2.leg_basis||'Leg number '||r2.leg_legnumber||' currency '||r2.leg_currency);
g_leg_number := r2.leg_legnumber;
insert into tran_leg values( g_transaction_id
,r2.LEG_BASIS
,r2.LEG_FIXEDRATE
);
for r3 in (
select z.column_value,
extractvalue(z.column_value,'/Leg/LegNumber') leg_legnumber ,
extractValue(z.column_value,'/Payment/StartDate') pay_startdate,
extractValue(z.column_value,'/Payment/Value') pay_value
from table(XMLSequence(extract(r2.column_value,'//Payment'))) z
) loop
-- dbms_output.put_line('pay currency '|| r3.pay_currency);
v_line := v_line + 1;
insert into tran_pay values(
,r3.PAY_STARTDATE
,r3.PAY_VALUE
);
if v_line = 500 then
commit;
v_line := 0;
end if;
end loop;
end loop;
end loop;
end loop;
but the above is taking approx. 45-50 minutes to load the data into all the 3 tables. We are trying to reduce the loading time below 10 minutes. Please suggest some alternative solution.
Regards
Senthil
I asked Laurent Schneider
http://laurentschneider.com/ to look at this (as he enjoys XML and is much more familiar with the syntax than I am :) )
Our idea is "do not go row by row, try bulk". Our suggestion would be less code, bulk SQL. For example:
drop table local_table;
create table local_table (clob_data clob);
insert into local_table (clob_data) values
('<TransactionList>
<Transaction id="1">
<TradeVersion>1</TradeVersion>
<TransactionId>1</TransactionId>
<Leg id="1">
<LegNumber>1</LegNumber>
<Basis>1</Basis>
<FixedRate>1</FixedRate>
<Payment id="1">
<StartDate>2000-01-01</StartDate>
<Value>1</Value>
</Payment>
<Payment id="2">
<StartDate>2000-01-02</StartDate>
<Value>2</Value>
</Payment>
</Leg>
<Leg id="2">
<LegNumber>2</LegNumber>
<Basis>2</Basis>
<FixedRate>2</FixedRate>
<Payment id="1">
<StartDate>2000-02-01</StartDate>
<Value>10</Value>
</Payment>
<Payment id="2">
<StartDate>2000-02-02</StartDate>
<Value>20</Value>
</Payment>
</Leg>
</Transaction>
<Transaction id="2">
<TradeVersion>2</TradeVersion>
<TransactionId>2</TransactionId>
<Leg id="1">
<LegNumber>21</LegNumber>
<Basis>21</Basis>
<FixedRate>21</FixedRate>
<Payment id="1">
<StartDate>2002-01-01</StartDate>
<Value>21</Value>
</Payment>
<Payment id="2">
<StartDate>2002-01-02</StartDate>
<Value>22</Value>
</Payment>
</Leg>
<Leg id="22">
<LegNumber>22</LegNumber>
<Basis>22</Basis>
<FixedRate>22</FixedRate>
<Payment id="21">
<StartDate>2002-02-01</StartDate>
<Value>210</Value>
</Payment>
<Payment id="22">
<StartDate>2002-02-02</StartDate>
<Value>220</Value>
</Payment>
</Leg>
</Transaction>
</TransactionList>
');
insert into local_table (clob_data) values
('<TransactionList>
<Transaction id="31">
<TradeVersion>31</TradeVersion>
<TransactionId>31</TransactionId>
<Leg id="31">
<LegNumber>31</LegNumber>
<Basis>31</Basis>
<FixedRate>31</FixedRate>
<Payment id="31">
<StartDate>3000-01-01</StartDate>
<Value>31</Value>
</Payment>
</Leg>
</Transaction>
</TransactionList>');
commit;
-- insert into tran
select
extractvalue(x.column_value,'/Transaction/TradeVersion/text()') tradeversion,
extractvalue(x.column_value,'/Transaction/TransactionId/text()') transactionid
from
local_table,
table(xmlsequence(extract(xmltype(clob_data),'/TransactionList/Transaction'))) x;
-- insert into tran_leg
select
extractvalue(x.column_value,'/Transaction/TransactionId/text()') transactionid,
extractvalue(y.column_value,'/Leg/Basis/text()') leg_basis,
extractValue(y.column_value,'/Leg/FixedRate/text()') leg_fixedrate
from
local_table,
table(xmlsequence(extract(xmltype(clob_data),'/TransactionList/Transaction'))) x,
table(xmlsequence(extract(x.column_value,'/Transaction/Leg'))) y;
-- insert into tran_pay
select
extractvalue(x.column_value,'/Transaction/TransactionId/text()') transactionid,
extractvalue(y.column_value,'/Leg/LegNumber/text()') leg_legnumber,
extractvalue(z.column_value,'/Payment/StartDate/text()') pay_startdate,
extractValue(z.column_value,'/Payment/Value/text()') pay_value
from
local_table,
table(xmlsequence(extract(xmltype(clob_data),'/TransactionList/Transaction'))) x,
table(xmlsequence(extract(x.column_value,'/Transaction/Leg'))) y,
table(xmlsequence(extract(y.column_value,'/Leg/Payment'))) z;