Skip to Main Content

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

User_GVUB0May 18 2022

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?

This post has been answered by User_GVUB0 on May 21 2022
Jump to Answer
Comments
Post Details
Added on May 18 2022
1 comment
60 views