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!

need help with xml format

ateeqrahmanOct 18 2021

Database : 11.2.0.4
Windows 2016

I am trying to achieve the below format
<cac:Item>
<cbc:Name>RENT </cbc:Name>
<cac:ClassifiedTaxCategory>
<VLID_VAT_TYPE>E</VLID_VAT_TYPE>
<VLID_VAT_PERCENT>0</VLID_VAT_PERCENT>
</cac:ClassifiedTaxCategory>

<cbc:Name>FURNITURE/APPLIANCES RENT </cbc:Name>
<cac:ClassifiedTaxCategory>
<VLID_VAT_TYPE>S</VLID_VAT_TYPE>
<VLID_VAT_PERCENT>5</VLID_VAT_PERCENT>
</cac:ClassifiedTaxCategory>

<cbc:Name>MANAGEMENT FEES </cbc:Name>
<cac:ClassifiedTaxCategory>
<VLID_VAT_TYPE>S</VLID_VAT_TYPE>
<VLID_VAT_PERCENT>5</VLID_VAT_PERCENT>
</cac:ClassifiedTaxCategory>

</cac:Item>

I have used below query trying to acheive the desired output. Please correct me where am i wrong.

select XMLELEMENT("cac:Item", XMLAGG (XMLELEMENT("cbc:Name",d.VLID_DESCRIPTION,
XMLELEMENT("cac:ClassifiedTaxCategory",
XMLFOREST(d.VLID_VAT_TYPE as "cbc:ID",d.VLID_VAT_PERCENT as "cbc:percent"
XMLELEMENT("cac:TaxScheme",
XMLFOREST(d.TaxScheme as "cbc:ID"
)))))))as "ZATCA" FROM zatca d where invoice_number='2018/06/25/557557';

This post has been answered by Solomon Yakobson on Oct 18 2021
Jump to Answer

Comments

Processing

Post Details

Added on Oct 18 2021
2 comments
192 views