Thanks all. Here is the SQL which generates the XML file. However it takes days to generate the XML file for 20 million records. I do have proper indexes on the tables. Could you please provide your ideas to generate this XML. Which one is the fast method to build this XML?. 1. Extracting the data to CSV file takes about 15 minutes using ETL and generate the XML using Perl. 2. Otherwise writing a SQL by joining all the normalized tables and generate the XML file by calling the SQL within the perl.

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_nam
+e), 
                                 (SELECT XMLAGG(XMLElement("account", 
                                            XMLElement("acc_id", acc_i
+d), 
                                            XMLElement("acc_name", acc
+_name), 
                                            XMLElement("acc_balance", 
+acc_balance),
                                            nvl(
                                              (
                                               select xmlconcat(
                                                        xmlelement("ac
+c_type", at.acc_type),
                                                        xmlagg(xmlelem
+ent("acc_code", at.acc_code))
                                                      )
                                               from acctype at
                                               where at.rltp_id = a.rl
+tp_id
                                               and at.prod_id = a.prod
+_id
                                               and at.cust_id = a.cust
+_id
                                               and at.acc_id = a.acc_i
+d
                                               group by at.acc_type
                                               )
                                             , xmlconcat(xmlelement("a
+cc_type"),xmlelement("acc_code"))   
                                            ) ,
                                        (SELECT XMLAGG(XMLElement("tra
+nsaction", 
                                               XMLElement("txntrack", 
                                               XMLElement("txn_id", t.
+txn_id), 
                                               XMLElement("txn_amt", t
+.txn_amt),
                                               (
                                                select nvl(
                                                         xmlagg(xmlele
+ment("txn_code",tt.txn_code))
                                                       , xmlelement("t
+xn_code")
                                                       )
                                                from txntype tt
                                                where tt.rltp_id = t.r
+ltp_id
                                                and tt.prod_id = t.pro
+d_id
                                                and tt.cust_id = t.cus
+t_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';
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.