What's a good approach for concatenating the XMLType flavor
Using Oracle PLSQL, I came to a point where I created several XML fragments of type XMLType. I have them stored in VARRAY type XMLTYPE. I can successfully print them separately in a file.
Next, I want to combine all these pieces and wrap them in a different root element to create a single document. From what I've read, if I can get the XMLSEQUENCETYPE, I can just pass that to XMLCONCAT (..) and it should return the XMLType concatenation of all fragments. After that it will just be adding the root elements with XMLELEMENT (..). However, I am at a loss to find a way to generate the XMLSEQUENCETYPE from my VARRAY XMLTYPE.
Does anyone know how this can be done and is the approach I have taken really the best one? (If anyone is interested, I am trying to create a basic structure like dbunit. The purpose of this script is to create a tool that can be used to output XML DataSets to a file that is later 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;
/
+3
source to share
2 answers
you're almost there.
first change the type to SQL code:
create or replace TYPE XML_Fragments_Type IS VARRAY(1000) OF XMLTYPE;
/
then do it
-- 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>
+3
source to share
If you don't use these snippets, you can use XMLCONCAT in a 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
+2
source to share