Get the resultset of any SQL cursor as XML with column names as attribute without using XSLT in Oracle

I'm trying to create a routine that takes an arbitrary cursor as input and generates the resultset as XML in this form:

<ROWSET>
  <ROW>
    <COLUMN name="ID">1</COLUMN> 
    <COLUMN name="NAME">Linda</COLUMN> 
    <COLUMN name="AGE">63</COLUMN> 
  </ROW>
  <ROW>
    <COLUMN name="ID">2</COLUMN> 
    <COLUMN name="NAME">Richard</COLUMN> 
    <COLUMN name="AGE">36</COLUMN> 
  </ROW>
</ROWSET>

So far I found DBMS_XMLGEN is the tool that goes closer to my use case, but I see no way to get the column names as attributes. Notice that XSLT is not an option in my use case, because the dataset may be potentially large and XSLT would not be efficient in this scenario.

You can use DBMS_XMLGEN to generate the XML and then XMLTABLE and Oracle's XML functions to transform it into a different structure:

DECLARE
  ctx  NUMBER;
  xml  CLOB;
BEGIN
  ctx := DBMS_XMLGEN.NEWCONTEXT('SELECT * FROM table_name'); 
  xml := DBMS_XMLGEN.GETXML(ctx);  
  DBMS_XMLGEN.CLOSECONTEXT(ctx);
  SELECT XMLSERIALIZE(
           DOCUMENT
           XMLELEMENT(
             "ROWSET",
             XMLAGG(
               XMLELEMENT(
                 "ROW",
                 XMLAGG(
                   XMLELEMENT("COLUMN", XMLATTRIBUTES(name AS "name"), value)
                   ORDER BY c.id
                 )
               )
               ORDER BY r.id
             )
           ) AS CLOB
           INDENT SIZE=2
         )
  INTO   xml
  FROM   XMLTABLE(
           '/ROWSET/ROW'
           PASSING XMLTYPE(xml)
           COLUMNS
             id FOR ORDINALITY,
             rw XMLTYPE PATH '.'
         ) r
         CROSS APPLY XMLTABLE(
           '/ROW/*'
           PASSING r.rw
           COLUMNS
             id    FOR ORDINALITY,
             name  VARCHAR2(30) PATH './name()',
             value VARCHAR2(4000) PATH '.'
         ) c
  GROUP BY r.id;

  DBMS_OUTPUT.PUT_LINE(xml);
END;
/

Which, for the sample data:

CREATE TABLE table_name (id, name, age) AS
SELECT 1, 'Linda', 63 FROM DUAL UNION ALL
SELECT 2, 'Richard', 36 FROM DUAL

Outputs:

<ROWSET>
  <ROW>
    <COLUMN name="ID">1</COLUMN>
    <COLUMN name="NAME">Linda</COLUMN>
    <COLUMN name="AGE">63</COLUMN>
  </ROW>
  <ROW>
    <COLUMN name="ID">2</COLUMN>
    <COLUMN name="NAME">Richard</COLUMN>
    <COLUMN name="AGE">36</COLUMN>
  </ROW>
</ROWSET>

fiddle