title: What's a good approach for concatenating a varray of XMLType's
body: <p>Using Oracle PLSQL, I have arrived at a point where i have generated a number of XML fragments of type XMLType. I have these stored in a VARRAY of type XMLTYPE. I can successfully print these out individually to a file. </p>
  
  <p>What I want to do next is fuse all these fragments together and wrap them in another root element to generate a single document. From what I have read if I can get hold of an XMLSEQUENCETYPE then I can just pass this into XMLCONCAT(..) and it should return an XMLType concatenation of all the fragments. After this it'd just be a case of adding the root elements using XMLELEMENT(..). I am however, having difficulty finding a way of generating an XMLSEQUENCETYPE from my VARRAY of XMLTYPE. </p>
  
  <p>Does anyone know how this can be done, and whether in fact the approach I have taken is the best one? (If anyone is curious, I'm trying to create a basic dbunit type framework. The intention of this script is to create a tool which can be used to output XML DataSets to file, which later get loaded into unit tests).</p>
  
  <p>Here's the plsql script:</p>
  
  <pre><code>
  set serveroutput on;
  CREATE OR REPLACE TYPE rowset_query_type AS OBJECT ( 
     table_name          VARCHAR2(100),
     query_string        VARCHAR2(1024)
  );
  /
  
  DECLARE
  
    TYPE XML_Fragments_Type IS VARRAY(1000) OF XMLTYPE;
    TYPE Rowset_Query_List_Type is VARRAY(1000) OF rowset_query_type;
  
    outputDir                 VARCHAR(200)  :=  'ORACLE_FILE_DIR';
    outputFile                VARCHAR(200)  :=  'TestDataSet.xml';
  
    qryCtx                    DBMS_XMLGEN.ctxHandle;
    rowsetResultFragments     XML_Fragments_Type;
    rowsetQueries             Rowset_Query_List_Type;  
    xmlResult                 xmltype;  
    rowsetQueryElement        rowset_query_type; 
  
    output                     CLOB;
  BEGIN
  
   dbms_output.put_line('Exporting dataset...');
  
   -- export files to data fixture 
  
   -- define fixtures
   rowsetQueries := Rowset_Query_List_Type();
   rowsetQueries.EXTEND(2);
  
   rowsetQueries := Rowset_Query_List_Type(
   rowset_query_type('person', 'select * from person'),
   rowset_query_type('address','select * from address'));
  
   rowsetResultFragments := XML_Fragments_Type();
   rowsetResultFragments.EXTEND(rowsetQueries.count);
  
   FOR i IN rowsetQueries.FIRST..rowsetQueries.LAST 
   LOOP
  
    rowsetQueryElement := rowsetQueries(i);  
    dbms_output.put_line('Extracting dataset for table: ' || rowsetQueryElement.table_name || ' using query: ''' || rowsetQueryElement.query_string || '''');
  
  
    qryCtx := dbms_xmlgen.newContext(rowsetQueryElement.query_string);
  
    -- wrap the result up with a metadata tag containing the fixture tablename
    select xmlelement(
            "ROWSET_QUERY",
            xmlattributes(rowsetQueryElement.table_name as "tableName"),
                DBMS_XMLGEN.getXMLType(qryCtx)  
        )
    into rowsetResultFragments(i)
    from dual;
  
    --close context
    DBMS_XMLGEN.closeContext(qryCtx);
  
    -- print the results to console
    -- serialize the result for printing to output
    SELECT XMLSERIALIZE(
      CONTENT 
        rowsetResultFragments(i) 
      AS CLOB)
    INTO output
    FROM DUAL;
  
    DBMS_OUTPUT.PUT_LINE(output);
  
   END LOOP;
  
  
  
  
   -- concatenate the set of rowsetQueries result fragments to a single result clob
   --  ???
  
  END;
  /
  </code></pre>