Using nested XMLAGG function in XML Type View in PL/SQL

Hey Experts,

I need to code uses nested xmlagg functions to nest my xml output of an xmltype view query I wrote in PL/SQL. The XMLAGG function should start at the (XMLELEMENT("document",)) line as, this is part of the code that I would like to aggregate. My XML Type View output about 209,000 rows and if coded correctly, the XMLAGG function should populate for all those 209,000 rows and the values within the "document" element.
The problem I am encountering is that the xmlagg function only seems to work when i do it once as in this example.
CREATE OR REPLACE VIEW CU_ADV_HURON_DATA_XML_V2 AS
select XMLELEMENT("macess_exp_import_export_file",
        XMLELEMENT("file_header",
         XMLELEMENT("file_information")),
        XMLELEMENT("items",
         XMLELEMENT("documents",
          XMLELEMENT("document",
           XMLELEMENT("external_reference"),
           XMLELEMENT("processing_instructions",
            XMLELEMENT("update", name)),
           XMLELEMENT("filing_instructions",
            XMLELEMENT("folder_ids",
             XMLELEMENT("folder",
              XMLATTRIBUTES(folder_id AS "id", folder_type_id AS "folder_type_id")))),
           XMLELEMENT("document_header",
            XMLELEMENT("document_type",
             XMLATTRIBUTES(document_type AS "id")),
            XMLELEMENT("document_id", document_id),
            XMLELEMENT("document_description", document_description),
            XMLELEMENT("document_date",
             XMLATTRIBUTES(name AS "name"), document_date),
            XMLELEMENT("document_properties")),
           XMLELEMENT("document_data",
             XMLELEMENT("internal_file",
              (SELECT XMLAGG(XMLELEMENT("document_file_path", document_file_path))
              FROM macess_import_base
              where document_id between 'CUE0000172' and 'CUE0000180')
              ))))))AS result
 
from macess_import_base where document_id between 'CUE0000172' and 'CUE0000180'
;

When I attempt adding another XMLAGG function I get errors. How do I write an XMLAGG function that aggregates every column within the document element? Here's a sample of what the xml output looks like for one row.


<?xml version="1.0"?><macess_exp_import_export_file><file_header><file_information></file_information></file_header><items><documents><document><external_reference></external_reference><processing_instructions><update>False</update></processing_instructions><filing_instructions><folder_ids><folder id="UNKNOWN" folder_type_id="19"/></folder_ids></filing_instructions><document_header><document_type id="27"/><document_id>17882</document_id><document_description>Baker, Kenneth PR 12.07.doc</document_description><document_date name="Date of Service">2008-2-6</document_date><document_properties></document_properties></document_header><document_data><internal_file><document_file_path>\\adam\ADM Import XML Docs\ToDo\Baker, Kenneth PR 12.07.doc</document_file_path><document_extension>DOC</document_extension></internal_file></document_data></document></documents></items></macess_exp_import_export_file>

Regards,
Definit1Start Free Trial