Convert xml to clob by maintaining formatting Oracle11g

5.9k views Asked by At

I have to store a variable of xmltype into a clob. I am using getclobval() function to convert the xml into clob. However, the data gets stored as a string in the clob and the formatting is lost. Is there any way I can preserve xml format while converting xmltype to clob?

select xmlelement(info,xmlelement(name,'Sankalp'),xmlelement(surname,'Patil')) into xml1 from dual; 
dbms_output.put_line(xml1.getClobval);

Output:

<INFO><NAME>Sankalp</NAME><SURNAME>Patil</SURNAME></INFO>

I am expecting output as:

<INFO>
  <NAME>Sankalp</NAME>
  <SURNAME>Patil</SURNAME>
</INFO>

Thanks in anticipation

1

There are 1 answers

1
user2503883 On

This did the job finally:

select xmlserialize(content v_xmltype as clob indent size = 2 ) 
 into v_clob_data from dual;

fyi, Oracle suggests to use xmlserialize than getclobval.