oracle xml parsing gives invalid character error

Question

Hi,

I am using Oracle 11G sql developer's Export Utility to convert the tabular data into XML. To do so, I have used utf-8 encoding. The special characters (0x13) which i see in DB as square boxes, have come into xml as it is. To get rid of this special character, do we need to select some other encoding in Sql Developer?

The problem I am facing is, 'xml parsing failed' while I try to insert the above created xml file into different Oracle table (using xmltable function ). I get the below error.

ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00216: invalid character 0 (0x13) Error at line 10682 ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 0 ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 26 ORA-06512: at line 9

If I remove the special characters from xml file manually, it started working fine. But I would like to get this working without removing them.

Thanks SK

Answer 1

you can remove the spl char be a replace function .. just replace the chr(13) by a blank..

REPLACE (arg1, CHR (13), '');