Hi,
I would like to know how to remove null tags in xml using xml element and xml forest.
And also, I have few clob columns in the xml forest query and I want to remove those tags as well when there is no data
Below is the query I am using.
SELECT (XMLELEMENT (
NAME "File",
XMLAGG (
XMLELEMENT (
NAME "Invoice",
XMLFOREST (
AEH.INVOICE_NUMBER AS "InvoiceNumber",
AEH.INVOICE_DATE AS "InvoiceDate"),
XMLELEMENT ("TaxPointDate",
AEH.TAXPOINT_DATE),
XMLFOREST (AEH.T_TRANS_TYPE AS "InvoiceType"),
XMLELEMENT (
NAME "AdditionalDocumentReference",
XMLFOREST (
'SHIPMENT_NUMBER' AS "DocumentType"),
XMLELEMENT ("ID", AEHD.SHIPMENT_NUMBER)),
XMLELEMENT (NOENTITYESCAPING
"AccountingSupplierParty",
AEH.SUPPLIER_PARTY_DETAILS),
XMLELEMENT (
NAME "AllowanceCharge",
XMLFOREST (
'Discount' AS "ChargeIndicator"),
XMLELEMENT ("AllowanceChargeReason",
AEH.DISCOUNT_DESC),
XMLELEMENT ("Amount",
AEH.DISCOUNT_AMOUNT),
-- XMLELEMENT (
-- NAME "TaxInfo",
-- XMLELEMENT (
-- NAME "TaxSubtotal",
-- XMLELEMENT (
-- "TaxAmount",
-- AEH.TAX_AMOUNT_FOR_DISCOUNT),
-- XMLELEMENT (
-- "Percent",
-- AEH.TAX_RATE_FOR_DISCOUNT),
-- XMLELEMENT (
-- "TaxTypeCode",
-- AEH.TAX_CATEGORY_CODE_FOR_DISCOUNT),
-- XMLELEMENT (
-- "TaxTypeDescription",
-- AEH.TAX_CATEGORY_CODE_FOR_DISCOUNT))
-- )),
(SELECT XMLELEMENT (
NAME "TaxInfo",
XMLAGG (
XMLELEMENT (
NAME "TaxSubtotal",
XMLELEMENT ("TaxAmount",
AET.LINE_TAX_AMOUNT),
XMLELEMENT ("Percent",
AET.TAX_RATE),
XMLELEMENT ("TaxTypeCode",
AET.TAX_CODE),
XMLELEMENT ("TaxTypeDescription",
AET.T_TAX_RATE_CODE),
XMLELEMENT ("TaxNetAmount",
AET.TAX_NET_AMOUNT),
XMLELEMENT ("TaxGrossAmount",
AET.TAX_GROSS_AMOUNT))))
FROM ADP_AR_EINVOICE_TAX AET
WHERE 1 = 1
AND AET.MASTER_RUN_ID = AEH.MASTER_RUN_ID
AND AET.CUSTOMER_TRX_ID = AEH.CUSTOMER_TRX_ID)),
XMLELEMENT (
NAME "InvoiceTotals",
XMLELEMENT ("InvoiceNetAmount",
AEH.INVOICE_NET_AMOUNT),
XMLELEMENT ("TaxAmount",
AEH.INVOICE_TAX_AMOUNT),
XMLELEMENT ("InvoiceGrossAmount",
AEH.INVOICE_GROSS_AMOUNT)),
(SELECT XMLELEMENT (
NAME "InvoiceDetails",
XMLAGG (XMLELEMENT (
NAME "LineItem",
XMLFOREST (
''--AEL.LINE_DETAIL1
AS "POLineNumber"),
XMLELEMENT (
"LineDetail1",
AEL.LINE_DETAIL1),
XMLELEMENT (
"LineDetail2",
AEL.LINE_DESCRIPTION),
XMLELEMENT (
"LineDetail3",
AEL.LINE_DETAIL3),
XMLELEMENT (
"PONumber",
AEL.PO_NUMBER),
XMLELEMENT (
NOENTITYESCAPING
"IDS",
AELD.ATTACHMENT\_REFERENCE\_ABOVE)),
XMLELEMENT (
NAME
"AdditionalDocumentReference",
XMLFOREST (
'ATTACHMENT\_REFERENCE\_BELOW'
AS "DocumentType"),
XMLELEMENT (
NOENTITYESCAPING
"IDS",
AELD.ATTACHMENT\_REFERENCE\_BELOW)))
ORDER BY
TO\_NUMBER (AEL.LINE\_DETAIL1)))
FROM XX\_TEST\_LINE AEL,
XX\_TEST\_LINE\_DOCREF AELD
WHERE 1 = 1
AND AEL.CONTROL\_ID = AELD.CONTROL\_ID
AND AEL.CUSTOMER\_TRX\_ID =
AELD.CUSTOMER\_TRX\_ID
AND AEL.CUSTOMER\_TRX\_LINE\_ID =
AELD.CUSTOMER\_TRX\_LINE\_ID
AND AEL.CONTROL\_ID = AEH.CONTROL\_ID
AND AELD.CONTROL\_ID = AEH.CONTROL\_ID)))))
-- ) -- // end AGG
-- // end ELEMENT - Download
-- INTO L\_HDR\_XML
FROM (select \*
from XX\_TEST\_HDR
where MASTER\_RUN\_ID = P\_MASTER\_RUN\_ID
order by to\_number(DECODE ( RTRIM ( INVOICE\_NUMBER, '1234567890'), NULL, INVOICE\_NUMBER,-1)) , invoice\_date, INVOICE\_NUMBER
) AEH,
XX\_TEST\_HDR\_DOCREF AEHD
WHERE 1 = 1
AND AEH.CONTROL\_ID = AEHD.CONTROL\_ID
AND ORG\_ID = P\_ORG\_ID
--AND MASTER\_RUN\_ID = P\_MASTER\_RUN\_ID
AND AEH.OUTBOUND\_STATUS = 'V' --invoice is valid
order by to\_number(DECODE ( RTRIM ( aeh.INVOICE\_NUMBER, '1234567890'), NULL, aeh.INVOICE\_NUMBER,-1)) , aeh.invoice\_date, aeh.INVOICE\_NUMBER;
The output I got
<?xml version="1.0" encoding="UTF-8"?>
<File>
<Invoice>
<InvoiceNumber>TEST123</InvoiceNumber>
<InvoiceDate>2023-01-26</InvoiceDate>
<InvoiceType>380</InvoiceType>
<BuyerID>TEST456</BuyerID>
<AdditionalDocumentReference>
<DocumentType>BUYER_COST_CENTER</DocumentType>
<ID />
</AdditionalDocumentReference>
<AdditionalDocumentReference>
<DocumentType>BILL_OF_LADING</DocumentType>
<ID />
</AdditionalDocumentReference>
<AdditionalDocumentReference>
<DocumentType>CONTRACT_ID</DocumentType>
<ID />
<AccountingSupplierParty>
<PartyNumber />
<Name>XXXXXXX</Name>
<Street1>41 STREET</Street1>
<Street2 />
<Street3 />
<Street4 />
<City>XCITY</City>
<PostalCode>1111111</PostalCode>
<State />
<Country>XXX</Country>
<TaxRegistrationNumber>4147889878</TaxRegistrationNumber>
<CompanyNumber>111111111111111</CompanyNumber>
<QST />
<PST />
<GST />
<HST />
<Contact>
<Person />
<Telephone />
<Email />
<ContactType>MAIN_TEST</ContactType>
</Contact>
<Contact>
<Person>TEST ABCD</Person>
<Telephone>11.11.11.11.11</Telephone>
<Email>TEST@GMAIL.com</Email>
<ContactType>SECOND_TEST</ContactType>
</Contact>
</AccountingSupplierParty>
The output I need
<?xml version="1.0" encoding="UTF-8"?>
<File>
<Invoice>
<InvoiceNumber>TEST123</InvoiceNumber>
<InvoiceDate>2023-01-26</InvoiceDate>
<InvoiceType>380</InvoiceType>
<BuyerID>TEST456</BuyerID>
<AccountingSupplierParty>
<PartyNumber />
<Name>XXXXXXX</Name>
<Street1>41 STREET</Street1>
<City>XCITY</City>
<PostalCode>1111111</PostalCode>
<Country>XXX</Country>
<TaxRegistrationNumber>4147889878</TaxRegistrationNumber>
<CompanyNumber>111111111111111</CompanyNumber>
<Contact>
<ContactType>MAIN_TEST</ContactType>
</Contact>
<Contact>
<Person>TEST ABCD</Person>
<Telephone>11.11.11.11.11</Telephone>
<Email>TEST@GMAIL.com</Email>
<ContactType>SECOND_TEST</ContactType>
</Contact>
</AccountingSupplierParty>