Convert SQL rows into xml tags

Viewed 27

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>

Desired 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>
0 Answers
Related