convert sql rows into xml tags

User_WWD0C

my query :

 SELECT XMLELEMENT("Invoice", XMLAttributes('xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2"' AS "xmlns:xsi", 'xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"' as "xmlns:cac", 'xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"' as "xmlns:cbc", 'xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2"' as "xmlns:ext" ),                        

      XMLAGG(

       XMLELEMENT("Invoice",

        XMLFOREST(

         rc.trx_number AS "cbc:ID",

         rc.trx_date AS "cbc:IssueDate",

         sum(tax_recoverable) AS "tax_recoverable",

         sum(rcg.amount + nvl(tax_recoverable, 0)) AS "net_total"         

        )

       )

      ) 

     ) as resutl

 --INTO  l_xmltype

FROM

  ra_customer_trx_all     rc,

  ra_customer_trx_lines_all  rcl,

  hz_cust_site_uses_all    hzcs,

  ra_cust_trx_line_gl_dist_all rcg,

  gl_code_combinations     cc,

  HZ_CUST_ACCOUNTS RCA , 

  ZX_PARTY_TAX_PROFILE ZPTP,

  HZ_PARTIES HZP1

WHERE

    rc.customer_trx_id = rcl.customer_trx_id

  AND hzcs.site_use_id = rc.bill_to_site_use_id

  AND cc.code_combination_id = rcg.code_combination_id

  AND rcg.customer_trx_id = rc.customer_trx_id

  AND rcl.customer_trx_line_id = rcg.customer_trx_line_id

  and rc.SOLD_TO_CUSTOMER_ID=RCA.CUST_ACCOUNT_ID

  and RCA.party_id=ZPTP.party_id

  and ZPTP.party_id=HZP1.party_id

  AND rcg.customer_trx_line_id IS NOT NULL

  and line_type='LINE'

  --AND rc.customer_trx_id = 8023

group by 

  rc.trx_number,

  HZP1.PARTY_NAME,

  'VAT # '||ZPTP.REP_REGISTRATION_NUMBER,

  rc.trx_date ;

xml output:

<Invoice xmlns:xsi='xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2"'

     xmlns:cac='xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"'

     xmlns:cbc='xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"'

     xmlns:ext='xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2"'>

  <Invoice>

    <cbc:ID xmlns:cbc='xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"'>10002</cbc:ID>

    <cbc:IssueDate xmlns:cbc='xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"'>2019-12-31</cbc:IssueDate>

    <tax_recoverable>4125</tax_recoverable>

    <net_total>86625</net_total>

  </Invoice>

</Invoice>

desire output

<Invoice xmlns:xsi='xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2"'

     xmlns:cac='xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2"'

     xmlns:cbc='xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"'

     xmlns:ext='xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2"'>

  <Invoice>

    <cbc:ID>10002</cbc:ID>

    <cbc:IssueDate>2019-12-31</cbc:IssueDate>

    <cbc:tax_recoverable>4125</cbc:tax_recoverable>

    <cbc:net_total>86625</cbc:net_total>

  </Invoice>

</Invoice>

Jason_(A_Non)

Originally asked at https://community.oracle.com/tech/apps-infra/discussion/4492137/import-ubl-2-1-xml