I need help to generate xml using XMLFOREST in oracle for the clob column data

93 views Asked by At

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>&lt;ID name='BSA_NUMBER'&gt;&lt;123/ID&gt;</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>
0

There are 0 answers