Yes. Txn I'd's missing in the file. Because it does not have txn codes. Sorry I forget to mention these. This is the reason we have empty tags in XML. Txn codes are coming from different table. While exporting the data from database to csv I have used inner join. Please consider we have 4 and 5 txn I'd,s in the CSV file and txn codes as nulls. I mean I will use the outer join to export the data to csv.

Ok, I think I understand you.

I wrote some code to process the CSV and the results were encouraging, 3 million records in about 5 minutes. However, I think a DBI connection to the database might be cleaner so this is what I have so far. You need to take A_ off the table names in the sql

Read more... (3 kB)

Let me know if I am on the right track and I will finish it off.

poj

Awesome. excellent. thank you so much. 3 million records in 5 minutes is really encouraging. yes. this code looks clean and we are on the right track. I noticed that the acc_type and acc_code fields are missing in account XML and txn_code is missing in transaction XML. these fields are coming from different tables too. a account will have an acc_type but multiple acc_codes. if there is no acc_types and acc_codes, we need to have empty tags. a transaction can have multiple txn_code. if there are no txn_codes, we need to have empty tag. here is the DDL and DML for these tables. Also, could you please post the code to process the CSV file? I will go with the DBI connection code.

drop table rltp_mngr;  
drop table product;  
drop table cust;  
drop table account;  
drop table transaction;  
drop table acctype;  
drop table txntype;  
  
create table rltp_mngr(rltp_id number,rltp_name varchar2(50));  
Insert into rltp_mngr values(10, 'Phil');  
Insert into rltp_mngr values(20, 'Jack');  
  
create table product(rltp_id number,prod_id number,prod_name varchar2(
+50));  
Insert into product values(10,1, 'Personal');  
Insert into product values(20,1, 'Business');  
 
create table cust(rltp_id number,prod_id number,cust_id number,cust_na
+me varchar2(50));  
insert into cust values(10,1,2,'Fixed');  
insert into cust values(20,1,2,'Fixed');  
 
Create table account(rltp_id number,prod_id number,cust_id number,acc_
+id number,acc_name varchar2(50),acc_balance number(18,2));  
insert into account values(10,1,2,3,'Savings',3000);  
insert into account values(10,1,2,7,'Savings',3000);  
insert into account values(20,1,2,3,'Savings',3000);  
 
create table transaction(rltp_id number,prod_id number,cust_id number,
+acc_id number,txn_id number,txn_amt number(18,2));  
insert into transaction values(10,1,2,3,4,500);  
insert into transaction values(10,1,2,3,5,500);  
insert into transaction values(10,1,2,3,6,500);  
insert into transaction values(10,1,2,7,8,500);  
insert into transaction values(20,1,2,3,4,500);  
 
create table acctype(rltp_id number,prod_id number,cust_id number,acc_
+id number,acc_type varchar2(5),acc_code varchar2(10));  
insert into acctype values(10,1,2,3,'X','ZZ');  
insert into acctype values(10,1,2,3,'X','YY');  
insert into acctype values(10,1,2,7,'X','AA');  
insert into acctype values(10,1,2,7,'X','BB');  
  
create table txntype(rltp_id number,prod_id number,cust_id number,acc_
+id number,txn_id number,txn_code varchar2(10));  
insert into txntype values(10,1,2,3,4,'11');  
insert into txntype values(10,1,2,3,4,'12');  
insert into txntype values(10,1,2,7,8,'11');  
insert into txntype values(10,1,2,7,8,'12');  
insert into txntype values(10,1,2,7,8,'13');
[download]