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_name 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,3,'X','AA');
insert into acctype values(10,1,2,3,'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');
select
XMLElement("transactiondetails",
XMLElement("rltp_id", rltp_id),
XMLElement("rltp_name", rltp_name),
(SELECT XMLAGG(XMLElement("product",
XMLElement("prod_id", p.prod_id),
XMLElement("prod_name", p.prod_name),
(SELECT XMLAGG(XMLElement("customer",
XMLElement("cust_id", cust_id),
XMLElement("cust_name", cust_name),
(SELECT XMLAGG(XMLElement("account",
XMLElement("acc_id", acc_id),
XMLElement("acc_name", acc_name),
XMLElement("acc_balance", acc_balance),
(SELECT XMLAGG(XMLElement("transaction",
XMLElement("txntrack",
XMLElement("txn_id", t.txn_id),
XMLElement("txn_amt", t.txn_amt))))
FROM transaction t
WHERE t.rltp_id =a.rltp_id
AND t.prod_id =a.prod_id
AND t.cust_id =a.cust_id
AND t.acc_id=a.acc_id)))
FROM account a
WHERE c.rltp_id=a.rltp_id
AND c.prod_id=a.prod_id
AND c.cust_id=a.cust_id)))
FROM cust c
WHERE p.rltp_id = c.rltp_id
AND p.prod_id = c.prod_id)))
FROM product p
WHERE p.rltp_id = r.rltp_id )
) AS xml
FROM
rltp_mngr r
WHERE rltp_id='10';
] 
Blog
Search
Help
Members
Register
Login
Home
