Question with XMLAGG/XMLELEMENT
786252Jul 20 2010 — edited Jul 21 2010Hi,
I'm working with XMLAGG and XMLELEMENT functions to extract data from a database, and put it in an XML document. Right now I am having an issue with performance. I wrote a simple SQL statement that is composed of these functions and looks something like this
select xmlelement (item, data,
xmlagg( item, data,
xmlagg( item, data,
xmlagg( item, data,
xmlagg( item, data,
)))).toClobVal() FROM TABLE
It works fine, takes maybe about 20ish minutes to work with the data and returns about 300 rows of items composed with all the aggregations.
Now, the problem is, when putting this into a PL/SQL block, I'm trying to add an additional XMLAGG function to the top layer so I can get one item and put it into a clob to store. This process however is taking hours and hours. Does anyone have a better method? I tried using bulk collect but couldn't figure it out when using clob values like this. Any examples and advice would be helpful!