I have 2 tables zac_d and zac_e.
create table zac_d (INVOICE_NUMBER CHAR (6),INVOICE# VARCHAR2 (17),ISSUEDATE VARCHAR2 (10),ISSUETIME VARCHAR2 (8),STARTDATE VARCHAR2 (10),ENDDATE VARCHAR2 (10),INVOICETYPECODE CHAR (3),DOCUMENTCURRENCYCODE CHAR (3),STREETNAME CHAR (20),CITYNAME CHAR (6),POSTALZONE CHAR (5),IDENTIFICATIONCODE CHAR (2),COMPANYID CHAR (15),REGISTRATIONNAME CHAR (21),ITEM_QUANTITY CHAR (1),VLID_DESCRIPTION VARCHAR2 (40),VLID_AMOUNT NUMBER,VLID_VAT_AMOUNT NUMBER,LINE_AMT_WITH_VAT NUMBER,VLID_VAT_TYPE VARCHAR2 (1),VLID_VAT_PERCENT NUMBER,TAXSCHEME CHAR (3),LTL_LESSEE_NAME VARCHAR2 (40),LTL_LESSEE_ADDRESS_1 VARCHAR2 (30),LTL_LESSEE_ADDRESS_2 VARCHAR2 (30),LTL_LESSEE_ADDRESS_3 VARCHAR2 (30) );
create table zac_e (INVOICE_NUMBER CHAR (6),ISSUEDATE VARCHAR2 (10),ISSUETIME VARCHAR2 (8),TAXEXCLUSIVEAMOUNT NUMBER,TAXAMOUNT NUMBER,TAXINCLUSIVEAMOUNT NUMBER,STREETNAME CHAR (20),CITYNAME CHAR (6),POSTALZONE CHAR (5),IDENTIFICATIONCODE CHAR (2),COMPANYID CHAR (15),REGISTRATIONNAME CHAR (21)
);
INSERT INTO ZAC_E ( INVOICE_NUMBER, ISSUEDATE, ISSUETIME, TAXEXCLUSIVEAMOUNT, TAXAMOUNT,TAXINCLUSIVEAMOUNT, STREETNAME, CITYNAME, POSTALZONE, IDENTIFICATIONCODE, COMPANYID,REGISTRATIONNAME ) VALUES ( '557557', '2018/06/25', '14:23:43', 37500, 544, 38044, 'Prince Street', 'ABHA', '21483', 'SA', '300181458904100', 'Arabian Stables Company');
COMMIT;
INSERT INTO ZAC_D ( INVOICE_NUMBER, INVOICE#, ISSUEDATE, ISSUETIME, STARTDATE, ENDDATE,INVOICETYPECODE, DOCUMENTCURRENCYCODE, STREETNAME, CITYNAME, POSTALZONE, IDENTIFICATIONCODE,COMPANYID, REGISTRATIONNAME, ITEM_QUANTITY, VLID_DESCRIPTION, VLID_AMOUNT, VLID_VAT_AMOUNT,LINE_AMT_WITH_VAT, VLID_VAT_TYPE, VLID_VAT_PERCENT, TAXSCHEME, LTL_LESSEE_NAME,LTL_LESSEE_ADDRESS_1, LTL_LESSEE_ADDRESS_2, LTL_LESSEE_ADDRESS_3 ) VALUES ( '557557', '2018/06/25/557557', '2018/06/25', '14:23:43', '2018/06/15', '2018/09/14', '380', 'SAR', 'Prince Street', 'ABHA', '21483', 'SA', '300181458904100'
, 'Arabian Stables Company', '1', 'RENT', 26625, 0, 26625, 'E', 0, 'VAT', 'JEREMY JOHNSTON'
, 'C/O 24 ALGA', 'DIANA VILLAGE', 'ABHA, KSA');
INSERT INTO ZAC_D ( INVOICE_NUMBER, INVOICE#, ISSUEDATE, ISSUETIME, STARTDATE, ENDDATE,INVOICETYPECODE, DOCUMENTCURRENCYCODE, STREETNAME, CITYNAME, POSTALZONE, IDENTIFICATIONCODE,COMPANYID, REGISTRATIONNAME, ITEM_QUANTITY, VLID_DESCRIPTION, VLID_AMOUNT, VLID_VAT_AMOUNT,LINE_AMT_WITH_VAT, VLID_VAT_TYPE, VLID_VAT_PERCENT, TAXSCHEME, LTL_LESSEE_NAME,LTL_LESSEE_ADDRESS_1, LTL_LESSEE_ADDRESS_2, LTL_LESSEE_ADDRESS_3 ) VALUES ( '557557', '2018/06/25/557557', '2018/06/25', '14:23:43', '2018/06/15', '2018/09/14', '380', 'SAR', 'Prince Street', 'ABHA', '21483','SA', '300181458904100'
, 'Arabian Stables Company', '1', 'FURNITURE/APPLIANCES RENT', 2750, 138, 2888, 'S'
, 5, 'VAT', 'JEREMY JOHNSTON', 'C/O 24 ALGA', 'DIANA VILLAGE', 'ABHA, KSA');
INSERT INTO ZAC_D ( INVOICE_NUMBER, INVOICE#, ISSUEDATE, ISSUETIME, STARTDATE, ENDDATE,INVOICETYPECODE, DOCUMENTCURRENCYCODE, STREETNAME, CITYNAME, POSTALZONE, IDENTIFICATIONCODE,COMPANYID, REGISTRATIONNAME, ITEM_QUANTITY, VLID_DESCRIPTION, VLID_AMOUNT, VLID_VAT_AMOUNT,LINE_AMT_WITH_VAT, VLID_VAT_TYPE, VLID_VAT_PERCENT, TAXSCHEME, LTL_LESSEE_NAME,LTL_LESSEE_ADDRESS_1, LTL_LESSEE_ADDRESS_2, LTL_LESSEE_ADDRESS_3 ) VALUES ( '557557', '2018/06/25/557557', '2018/06/25', '14:23:43', '2018/06/15', '2018/09/14', '380', 'SAR', 'Prince Street', 'ABHA', '21483', 'SA', '300181458904100'
, 'Arabian Stables Company', '1', 'MANAGEMENT FEES', 8125, 406, 8531, 'S', 5, 'VAT', 'JEREMY JOHNSTON', 'C/O 24 ALGA', 'DIANA VILLAGE', 'ABHA, KSA');
COMMIT;
I am using XMLAGG function on last 2 tags and it wirks fine. When i add the cac:LegalMonetorytotal, it gives error. It doesn't require XMLAGG function. It should appear once in the output.
select XMLELEMENT("Invoice",
XMLELEMENT("cac:LegalMonetoryTotal",
XMLELEMENT("cbc:LineExtensionAmount",
XMLAttributes('SAR' as "CurrencyID"),e.TAXEXCLUSIVEAMOUNT),
XMLELEMENT("cbc:TaxExclusiveAmount",
XMLAttributes('SAR' as "CurrencyID"),e.TAXEXCLUSIVEAMOUNT),
XMLELEMENT("cbc:TaxInclusiveAmount",
XMLAttributes('SAR' as "CurrencyID"),e.TAXINCLUSIVEAMOUNT),
XMLELEMENT("cbc:PayableAmount",
XMLAttributes('SAR' as "CurrencyID"),e.TAXINCLUSIVEAMOUNT)
),
XMLAGG( XMLELEMENT("cac:TaxTotal",
XMLELEMENT("cbc:TaxAmount",XMLAttributes('SAR' as "CurrencyID"),e.TAXAMOUNT),
XMLELEMENT("cac:TaxSubTotal",
XMLELEMENT("cbc:TaxableAmount",XMLAttributes('SAR' as "CurrencyID"),e.TAXEXCLUSIVEAMOUNT),
XMLELEMENT("cbc:TaxAmount",XMLAttributes('SAR' as "CurrencyID"),e.TAXAMOUNT),
XMLELEMENT("cac:TaxCategory",
XMLFOREST(d.VLID_VAT_TYPE as "cbc:ID",d.VLID_VAT_PERCENT as "cbc:percent")),
XMLELEMENT("cac:TaxScheme", XMLFOREST(d.TaxScheme as "cbc:ID")
))
)),
XMLAGG( XMLELEMENT("cac:InvoiceLine",
XMLELEMENT("cbc:InvoicedQuantity",d.Item_Quantity),
XMLELEMENT("cbc:LineExtensionAmount", XMLAttributes('SAR' as "CurrencyID"),d.VLID_AMOUNT),
XMLELEMENT("cac:TaxTotal",
XMLELEMENT("cbc:RoundingAmount", XMLAttributes('SAR' as "CurrencyID"),d.LINE_AMT_WITH_VAT),
XMLELEMENT("cbc:TaxAmount",XMLAttributes('SAR' as "CurrencyID"),d.VLID_VAT_AMOUNT)),
XMLELEMENT("cac:Item",
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",
XMLELEMENT("cbc:ID",d.TAXSCHEME)))),
XMLELEMENT("cac:Price",
XMLELEMENT("cbc:PriceAmount",XMLAttributes('SAR' as "SchemeID"),d.VLID_AMOUNT))
))
).getClobVal()
as "ZATCA" FROM zatca_d d,zatca_e e
where
d.invoice_number=e.invoice_number and
e.invoice_number='557557';