SQL & PL/SQL

Hi,

I need to populate a CLOB column in a table with the output from a select query using XMLAgg which returns a string length in excess of the maximum SQL length of 32767 bytes.

The table I am querying has 300,000 rows which I need to create as an XML file. When I apply the query below (without XMLAgg) I get 300,000 rows each of 134 chars each. However, when I add the XMLAgg I cannot select results as XMLAgg concatenates my 300,000 rows into one row resulting in a String length too long for SQL.

SELECT ( XMLElement("row", XMLELEMENT("cell", XMLATTRIBUTES('XML_TAG_1' "colName"),attribute1)
, XMLELEMENT("cell", XMLATTRIBUTES('XML_TAG_2' "colName" ) ,attribute2)
, XMLELEMENT("cell", XMLATTRIBUTES('XML_TAG_3' "colName"),attribute3))
)
from XML_TAGS_TABLE
--Produces 300,000 rows like below
<row><cell colName="XML_TAG_1">12345</cell><cell colName="XML_TAG_2">abcdef</cell><cell colName="XML_TAG_3">123xyz</cell></row>

With this in mind I have created a temporary table (TEMP_TAB_TEST) with one column called XML_ROW which is a CLOB. By applying XMLAgg to the above query I would get 300,000 rows concatenated in one string. I need to get the single string XMLAgg output into the XML_ROW (CLOB) column so as I can then output to an XML file.

I cannot see how I can use XMLAgg due to the length of the output it produces? Is there a way I can populate the clob column using the XMLAgg results from a PL/SQL package / Anon Block?

Many Thanks,
Simon.