select xmlserialize(document
       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),
                                            nvl(
                                              (
                                               select xmlconcat(
                                                        xmlelement("acc_type", at.acc_type),
                                                        xmlagg(xmlelement("acc_code", at.acc_code))
                                                      )
                                               from acctype at
                                               where at.rltp_id = a.rltp_id
                                               and at.prod_id = a.prod_id
                                               and at.cust_id = a.cust_id
                                               and at.acc_id = a.acc_id
                                               group by at.acc_type
                                               )
                                             , xmlconcat(xmlelement("acc_type"),xmlelement("acc_code"))   
                                            ) ,
                                        (SELECT XMLAGG(XMLElement("transaction", 
                                               XMLElement("txntrack", 
                                               XMLElement("txn_id", t.txn_id), 
                                               XMLElement("txn_amt", t.txn_amt),
                                               (
                                                select nvl(
                                                         xmlagg(xmlelement("txn_code",tt.txn_code))
                                                       , xmlelement("txn_code")
                                                       )
                                                from txntype tt
                                                where tt.rltp_id = t.rltp_id
                                                and tt.prod_id = t.prod_id
                                                and tt.cust_id = t.cust_id
                                                and tt.acc_id = t.acc_id
                                                and tt.txn_id = t.txn_id
                                               )
                                               ))
                                               ORDER BY t.txn_id
                                               ) 
                                        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))
                                        ORDER BY 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))
                            ORDER BY c.cust_id
                            ) 
                        FROM cust c 
                        WHERE p.rltp_id = c.rltp_id 
                        AND p.prod_id = c.prod_id))
                        ORDER BY p.prod_id
                        ) 
                     FROM product p 
                     WHERE p.rltp_id = r.rltp_id )        
                    ) indent) AS xml 
FROM 
  rltp_mngr r 
  WHERE rltp_id='10';