I have one table with one CLOB data type and we inserted the data in that column in below format
<ID name='BSA_NUMBER'></ID>
and I have another few columns with VARCHAR2(100).
I am trying to generate XML from the table using XMLFOREST, so the data in CLOB column is populating with some special characters as (<ID name='BSA_NUMBER'></ID>)
The query I am using is
SELECT (XMLELEMENT (
NAME "File",
XMLAGG (
XMLELEMENT (
NAME "Invoice",
XMLFOREST (
SHIPMENT_NUMBER AS "SHIPMENTNUMBER",
ADDITIONAL_HEADER_INFO AS "Invoice1")))))
FROM test_xml
WHERE 1 = 1 AND CONTROL_ID = 18644;
Output:
<?xml version="1.0" encoding="UTF-8"?>
<File>
<Invoice>
<SHIPMENTNUMBER>456</SHIPMENTNUMBER>
<Invoice1><ID name='BSA_NUMBER'><123/ID></InvoiceNumber>
</Invoice>
</File>
But I need the output in the below format
<?xml version="1.0" encoding="UTF-8"?>
<File>
<Invoice>
<SHIPMENTNUMBER>456</SHIPMENTNUMBER>
<Invoice1><ID name='BSA_NUMBER'>123</ID></Invoice1>
</Invoice>
</File>