Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

convert sql rows into xml tags

Daniyal AhmedSep 15 2022 — edited Sep 15 2022

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>

Comments

Post Details

Added on Sep 15 2022
1 comment
277 views