CODEWITHSUNDEEP
databaseoracle-databaseplsql
Harshana
Harshana

Reputation: 7647

Create a xml root element for a clob which does not have a root element in Oracle

By iteration over a cursor I create a CLOB which has below xml format. Suppose l_tot_clob clob contains the below:

<a row="1">
 <b>test</b>
<a>
<a row="2">
 <b>test</b>
</a>

Then I try to put above inside another element root called Record and assign the result to a another clob called l_return_clob. The code for that is below:

SELECT xmlelement("Record" , xmlconcat(xmltype(l_tot_clob))).getclobval() 
INTO l_return_clob
FROM dual;

What I am expecting from the retrun l_return_clob is something as this:

<Record>
<a row="1">
 <b>test</b>
<a>
<a row="2">
 <b>test</b>
</a>
</Record>

But as I assume since the l_tot_clob does not have a parent element so at the point I try to create a root element as Record, it gives the below error:

fdm_ttwof_pkg.main_prc - Exception -ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00245: extra data after end of document

How can I add the Record element as the root element in this case.

Upvotes: 2

Views: 4167

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

If all you need is just to enclose your xml in <Record>..</Record>, and your XML is in CLOB, then why don't you just simply concatenate the '<Record>' and '</Record>' strings?

DECLARE
  v_clob CLOB;
  v_clob_with_root CLOB;
  v_xml xmltype;
BEGIN
  v_clob := '
    <a row="1">
     <b>test</b>
    <a>
    <a row="2">
     <b>test</b>
    </a>';

  v_clob_with_root := '<Record>' || v_clob_with_root || '</Record>';

  v_xml := xmltype(v_clob_with_root); -- no errors, XML is correct
END;

Upvotes: 4

Related Questions