XML parsing error for invalid character 181 (U+00B5)

User_GVUB0

I am trying to import large xml files (1GB) into relational database tables. It is throwing error when there is non-ASCII character. In this case it is µ (mu).

ERROR at line 1:

ORA-31011: XML parsing failed

ORA-19213: error occurred in XML processing at lines 37078

LPX-00217: invalid character 181 (U+00B5)

ORA-06512: at "SYS.XMLTYPE", line 296

This is the line it fails:

l_xmlfile := xmltype(bfilename(i_dir,i_file), NLS_CHARSET_ID('AL32UTF8'));

I tried to overcome that with,

l_xmlfile := xmltype(bfilename(i_dir,i_file), NLS_CHARSET_ID('AL32UTF8'), NULL, 1, 1);

But, trying to loop through the xml data fails. I'm passing l_xmlfile into another procedure as i_xml

Here is the FOR LOOP I get the error message again.

FOR c_records IN

   (

      SELECT *

      FROM TABLE(xmlsequence(extract(i_xml, '/masterrec')))

      CROSS JOIN xmltable

      (

        '/masterrec/subreport'

        PASSING i_xml

        COLUMNS .......

  )

   )

   LOOP

Here are the relevant NLS parameters

NLS_CHARACTERSET AL32UTF8

NLS_DATE_LANGUAGE AMERICAN

NLS_LANGUAGE AMERICAN

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_NCHAR_CONV_EXCP FALSE

Is there any workaround/solution to overcome this other than replacing the non-ASCII characters?

User_GVUB0

xmlfile := xmltype(bfilename(i_dir,i_file), NLS_CHARSET_ID('WE8MSWIN1252'));

fixed the problem! No other changes to NLS parameters or other code.

User_GVUB0

xmlfile := xmltype(bfilename(i_dir,i_file), NLS_CHARSET_ID('WE8MSWIN1252'));

fixed the problem! No other changes to NLS parameters or other code.