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';