CODEWITHSUNDEEP
xmloracle-databaseoracle-apex
Farzad Soltani
Farzad Soltani

Reputation: 505

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

<?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

Upvotes: 1

Views: 3501

Answers (2)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

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>

Upvotes: 2

MT0
MT0

Reputation: 167962

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
)

Upvotes: 2

Related Questions