How to define elements and attributes for XML created by apex_data_export

I created an XML output file with APEX_DATA_EXPORT. It is working fine, but I don't understand how to change, if possible, the structure and names of the attributes and elements.

Example output as csv works fine:

Reference	BIC	IBAN	Amount	PaymentDate
AB8473	BICX 123 45	DE 1349134913	999	2022-03-31T00:00:00
ZAB9574	BICX 123 45	DE 1349134913	156	2022-03-31T00:00:00
XY1245	BICX 123 45	DE 1349134913	123	2022-03-31T00:00:00

Example output as xml

<ROWSET>
  <ROW>
    <Reference>AB8473    </Reference>
    <BIC>BICX 123 45</BIC>
    <IBAN>DE 1349134913</IBAN>
    <Amount>999</Amount>
    <PaymentDate>2022-03-31T00:00:00</PaymentDate>
  </ROW>
  <ROW>
    <Reference>ZAB9574   </Reference>
    <BIC>BICX 123 45</BIC>
    <IBAN>DE 1349134913</IBAN>
    <Amount>156</Amount>
    <PaymentDate>2022-03-31T00:00:00</PaymentDate>
  </ROW>  
</ROWSET>
...

I was not able to find anything in Oracle Reference

How to change the names ROWSET / ROW and add some additional attributes?

Thanks in advance, KBK

Solution

This could be done using just SQL. With your xml output using XMLTABLE() function you can get your data as a table (cte tbl below).

WITH    --  S a m p l e    D a t a :      (xml)
    xml_tab (xml_data) AS
      ( Select XMLTYPE(
'<ROWSET>
  <ROW>
    <Reference>AB8473    </Reference>
    <BIC>BICX 123 45</BIC>
    <IBAN>DE 1349134913</IBAN>
    <Amount>999</Amount>
    <PaymentDate>2022-03-31T00:00:00</PaymentDate>
  </ROW>
  <ROW>
    <Reference>ZAB9574   </Reference>
    <BIC>BICX 123 45</BIC>
    <IBAN>DE 1349134913</IBAN>
    <Amount>156</Amount>
    <PaymentDate>2022-03-31T00:00:00</PaymentDate>
  </ROW>  
</ROWSET>')
      From Dual
      ),

... create cte to get the rows of data out of your xml

    tbl AS
      ( Select      xt.REFER, xt.BIC, xt.IBAN, xt.AMT, To_Date( SubStr(xt.PAYDT, 1, 10), 'yyyy-mm-dd' ) "PAYDT"
        From        xml_tab x, 
                    XMLTABLE('/ROWSET/ROW'  
                                PASSING x.xml_data
                                COLUMNS REFER     VARCHAR2(16)  PATH 'Reference', 
                                        BIC       VARCHAR2(16)  PATH 'BIC',
                                        IBAN      VARCHAR2(32)  PATH 'IBAN',
                                        AMT       NUMBER(16, 2) PATH 'Amount',
                                        PAYDT     VARCHAR2(32)  PATH 'PaymentDate'
                             ) xt
      )

Testing the result:

Select * From tbl;
/*     R e s u l t :
REFER            BIC              IBAN                                    AMT PAYDT   
---------------- ---------------- -------------------------------- ---------- --------
AB8473           BICX 123 45      DE 1349134913                           999 31.03.22
ZAB9574          BICX 123 45      DE 1349134913                           156 31.03.22    */

If you have this dataset as table/view or you could create it then use that as tbl below - no need for cte tbl above... ... with this dataset create your own xml changing what you need to change and add what you need to add (or remove something if you want). There is a set of xml functions helping you to get it done.
Here, ROWSET is changed to Transactions, ROW is changed to Transaction and there is an attribute added to the (ex Reference) TransactionReference (RowNumber). Amount and Date tags are renamed too.

SELECT  XMLELEMENT("Transactions",
                      XMLAGG(XMLELEMENT("Transaction",  
                                        XMLELEMENT("TransactionReference", XMLATTRIBUTES(ROWNUM AS "RowNumber"), t.REFER),   
                                        XMLELEMENT("BIC", t.BIC),
                                        XMLELEMENT("IBAN", t.IBAN),
                                        XMLELEMENT("TransactionAmount", t.AMT),
                                        XMLELEMENT("TransactionDate", To_Char(t.PAYDT, 'yyyy-mm-dd') || 'T00:00:00')
                                  )  
                        ) 
                  ) AS "NEW_XML_DATA" 
FROM tbl t;

/*    R e s u l t :   */
<Transactions>
    <Transaction>
        <TransactionReference RowNumber="1">AB8473    </TransactionReference>
        <BIC>BICX 123 45</BIC>
        <IBAN>DE 1349134913</IBAN>
        <TransactionAmount>999</TransactionAmount>
        <TransactionDate>2022-03-31T00:00:00</TransactionDate>
    </Transaction>
    <Transaction>
        <TransactionReference RowNumber="2">ZAB9574   </TransactionReference>
        <BIC>BICX 123 45</BIC>
        <IBAN>DE 1349134913</IBAN>
        <TransactionAmount>156</TransactionAmount>
        <TransactionDate>2022-03-31T00:00:00</TransactionDate>
    </Transaction>
</Transactions>