Multiple Rowsets in Dbms_Xmlgen. Getxml - Oracle Apex
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): I'm Using...
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
2 Answers
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>
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
)
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
