1

When you add two queries in a report query in shared components and download the XML data, the output XML structure is like this (two ROWSETs):

<?xml version="1.0" encoding="UTF-8"?> <DOCUMENT> <DATA> <ROWSET1> <ROWSET1_ROW> <ID></ID> <NAME></NAME> </ROWSET1_ROW> </ROWSET1> <ROWSET2> <ROWSET2_ROW> <ID2></ID2> <NAME2></NAME2> </ROWSET2_ROW> </ROWSET2> </DATA> </DOCUMENT>

I'm using DBMS_XMLGEN.GETXML to produce the same XML content from my select statements. How can I do it?

The two SQL statements are :

SELECT ID, NAME FROM TABLE1 SELECT ID2,NAME2 FROM TABLE2
Improve this question

2 Answers 2

Reset to default
2 
DECLARE l_ctx dbms_xmlgen.ctxhandle; v_clob clob; begin l_ctx := dbms_xmlgen.newcontext('select cursor(select * from TABLE1) as rowset1,cursor(select * from TABLE2 ) as rowset2 from dual'); dbms_xmlgen.setrowsettag(l_ctx, 'DOCUMENT'); dbms_xmlgen.setrowtag(l_ctx, 'DATA'); v_clob := dbms_xmlgen.getXml(l_ctx); dbms_xmlgen.closeContext(l_ctx); dbms_output.put_line(v_clob); end;

And the result is .

<?xml version="1.0"?> <DOCUMENT> <DATA> <ROWSET1> <ROWSET1_ROW> <ID>1</ID> <NAME>a</NAME> </ROWSET1_ROW> </ROWSET1> <ROWSET2> <ROWSET2_ROW> <ID>1</ID> <NAME>a</NAME> </ROWSET2_ROW> </ROWSET2> </DATA> </DOCUMENT>

Or if you don't have to rename root element. Do the select.

select dbms_xmlgen.getxml('select cursor(select * from TABLE1) as rowset1,cursor(select * from TABLE2 ) as rowset2 from dual') from dual;

result

<?xml version="1.0"?> <ROWSET> <ROW> <ROWSET1> <ROWSET1_ROW> <ID>1</ID> <NAME>a</NAME> </ROWSET1_ROW> </ROWSET1> <ROWSET2> <ROWSET2_ROW> <ID>1</ID> <NAME>a</NAME> </ROWSET2_ROW> </ROWSET2> </ROW> </ROWSET>
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. This solved my problem entirely. Much appreciated.
2

Something like:

SELECT '<?xml version="1.0" encoding="UTF-8"?>' || XMLElement( "DOCUMENT", XMLElement( "DATA", XMLAggregate( row_xml ) ) ).getClobVal() AS xml FROM ( SELECT XMLElement( "ROWSET1", XMLAggregate( XMLElement( "ROWSET1_ROW", XMLForest( id, name ) ) ) ) AS row_xml FROM table1 UNION ALL SELECT XMLElement( "ROWSET2", XMLAggregate( XMLElement( "ROWSET2_ROW", XMLForest( id AS "ID2", name AS "NAME2" ) ) ) ) FROM table2 )
Improve this answer

1 Comment

Much appreciated. Works.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.