inserting output of xmlagg into CLOB column

My objective is to get a result set back and "wrap" it in xml tags and then store this xml "document" in a clob field. Thus my table 'xml_test' has a CLOB column - 'xml_string' and a varchar2 column - reference (simply to hold an identifier). Here is what I have come up with that seems to work:

PROCEDURE sqlxtest(referent IN varchar2) IS result CLOB;
BEGIN
select xmlagg(xmlelement("ChangePackage", xmlattributes(a.change_pkg_id as id),
xmlelement("Name", name),
xmlagg(
xmlelement("Entry", xmlattributes(b.ENTRYSEQ as "Seq"), xmlforest(b.APPROVAL_LEVEL as "AppLevel", b.CHANGE_TEXT as "Text"))))).getClobVal() INTO result from app_change_pkg a, app_change_pkg_assoc b where a.CHANGE_PKG_ID = b.CHANGE_PKG_ID and b.OPCODE = 'T'
group by a.change_pkg_id, name;

INSERT INTO xml_test COLUMNS (xml_string, reference) VALUES (result, referent);

COMMIT; END sqlxtest;

However this has no "top level" tag. It is simply an aggregation of all the xml fragments. If I then try to select the CLOB as follows:

select xmlelement("top_level", xml_string).getClobVal() from xml_test where reference = 'aaa'

when I look at the result all the '<' and '>' have been replaced with html equivalents: '&gt;' '&lt'.

What I need are the following tags to encapsulate my xml document in the clob field:

<?xml version="1.0"?>
<top_level>
xml_string
</top_level>

Any help is greatly appreciated. Received on Wed Jul 16 2003 - 13:51:31 CDT