当前位置: 动力学知识库 > 问答 > 编程问答 >

xml - What's a good approach for concatenating a varray of XMLType's

问题描述:

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.

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.

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).

Here's the plsql script:

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;

/

网友答案:

you're almost there.

first, change your type to an SQL one:

create or replace TYPE XML_Fragments_Type IS VARRAY(1000) OF XMLTYPE;
/

then do this

-- concatenate the set of rowsetQueries result fragments to a single result clob
 --  ???

 select xmlelement("root" ,xmlagg(column_value))
   into xmlresult
   from table(rowsetResultFragments);

eg:

    SQL> create or replace TYPE XML_Fragments_Type IS VARRAY(1000) OF XMLTYPE;
      2  /

    Type created.

    SQL> DECLARE
      2
      3    --TYPE XML_Fragments_Type IS VARRAY(1000) OF XMLTYPE;
      4    TYPE Rowset_Query_List_Type is VARRAY(1000) OF rowset_query_type;
      5
      6    outputDir                 VARCHAR(200)  :=  'ORACLE_FILE_DIR';
      7    outputFile                VARCHAR(200)  :=  'TestDataSet.xml';
      8
...
     26   rowsetQueries := Rowset_Query_List_Type(
     27   rowset_query_type('person', 'select table_name, owner from dba_tables where rownum = 1'),
     28   rowset_query_type('address','select owner, type_name, attributes from dba_types where rownum = 1'));
     29
...
     56
     57   select xmlelement("root" ,xmlagg(column_value))
     58     into xmlresult
     59     from table(rowsetResultFragments);
     60    SELECT XMLSERIALIZE(
     61      CONTENT
     62        xmlresult
     63      AS CLOB )
     64    INTO output
     65    FROM DUAL;
     66    DBMS_OUTPUT.PUT_LINE(output);
     67
     68
     69  END;
     70  /
    Exporting dataset...
    Extracting dataset for table: person using query: 'select table_name, owner from dba_tables where rownum = 1'
    Extracting dataset for table: address using query: 'select owner, type_name, attributes from dba_types where rownum = 1'
    <root>
      <ROWSET_QUERY tableName="person">
        <ROWSET>
          <ROW>
            <TABLE_NAME>ICOL$</TABLE_NAME>
            <OWNER>SYS</OWNER>
          </ROW>
        </ROWSET>
      </ROWSET_QUERY>
      <ROWSET_QUERY tableName="address">
        <ROWSET>
          <ROW>
            <OWNER>CTXSYS</OWNER>
            <TYPE_NAME>CATINDEXMETHODS</TYPE_NAME>
            <ATTRIBUTES>3</ATTRIBUTES>
          </ROW>
        </ROWSET>
      </ROWSET_QUERY>
    </root>
网友答案:

If you don't have a use for these fragments, you could use XMLCONCAT in the loop, then XMLELEMENT at the end:

SQL> DECLARE
  2     TYPE Rowset_Query_List_Type IS VARRAY(1000) OF rowset_query_type;
  3  
  4     outputDir  VARCHAR(200) := 'ORACLE_FILE_DIR';
  5     outputFile VARCHAR(200) := 'TestDataSet.xml';
  6  
  7     qryCtx             DBMS_XMLGEN.ctxHandle;
  8     rowsetQueries      Rowset_Query_List_Type;
  9     xmlResult          XMLTYPE;
 10     rowsetQueryElement rowset_query_type;
 11  BEGIN
 12     -- define fixtures
 13     rowsetQueries := Rowset_Query_List_Type(
 14                         rowset_query_type('person', 'select * from dual'),
 15                         rowset_query_type('address', 'select * from dual'));
 16  
 17     FOR i IN rowsetQueries.FIRST .. rowsetQueries.LAST LOOP
 18  
 19        rowsetQueryElement := rowsetQueries(i);
 20        dbms_output.put_line('Extracting dataset for table: '
 21                             || rowsetQueryElement.table_name
 22                             || ' using query: '''
 23                             || rowsetQueryElement.query_string || '''');
 24  
 25        qryCtx := dbms_xmlgen.newContext(rowsetQueryElement.query_string);
 26  
 27        -- concatenate
 28        SELECT xmlconcat(xmlResult,
 29                         xmlelement(
 30                            "ROWSET_QUERY",
 31                            xmlattributes(
 32                               rowsetQueryElement.table_name AS "tableName"),
 33                            DBMS_XMLGEN.getXMLType(qryCtx)))
 34          INTO xmlResult
 35          FROM dual;
 36  
 37        --close context
 38        DBMS_XMLGEN.closeContext(qryCtx);
 39  
 40     END LOOP;
 41  
 42     -- root element
 43     SELECT xmlelement(ROOT, xmlresult) INTO xmlresult FROM dual;
 44  
 45     dbms_output.put_line('result:');
 46     DBMS_OUTPUT.PUT_LINE(xmlResult.getClobVal());
 47  
 48  END;
 49  /

Extracting dataset for table: person using query: 'select * from dual'
Extracting dataset for table: address using query: 'select * from dual'
result:
<ROOT><ROWSET_QUERY tableName="person"><ROWSET>
  <ROW>
    <DUMMY>X</DUMMY>
  </ROW>
</ROWSET>
</ROWSET_QUERY><ROWSET_QUERY tableName="address"><ROWSET>
  <ROW>
    <DUMMY>X</DUMMY>
  </ROW>
</ROWSET>
</ROWSET_QUERY></ROOT>

PL/SQL procedure successfully completed
分享给朋友:
您可能感兴趣的文章:
随机阅读: