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>