ORA-31011 ORA-19202 LPX-00217 "XML parsing failed with Extract value from CLOB containing &#x20AC (the hexa code of Euro)"

(Doc ID 1310237.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 24-Jul-2013***

Symptoms

Extract value from CLOB , and CLOB contain euro sign (the hexa code of euro sign ) or  character hexa code of

SELECT
xmltype(d.dem_donnees).extract('/a:entry/a:content/div/div[@class="detailsDemande"]/span[@class="niveauGarantieAssurance"]/text()',
'xmlns:a=http://www.w3.org/2005/Atom
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
xmlns:xhtml=http://www.w3.org/1999/xhtml').getStringVal()
from demande d;

on a CLOB column (dem_donnees) containing html codes, like euro¬ produce the error below

Erreur SQL : ORA-31011: Echec d'analyse XML
ORA-19202: Une erreur s'est produite lors du traitement la fonction XML (
LPX-00217: 8364 (U+20AC) non valide
Error at line 34
)
ORA-06512:   "SYS.XMLTYPE", ligne 272
ORA-06512:   ligne 1
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.

Changes

 

Not working testcase with NLS_CHARACTERSET WE8ISO8859P15



SQL> select *
2 from nls_database_parameters
3 where parameter in
4 ('NLS_CHARACTERSET','NLS_LANGUAGE','NLS_NCHAR_CHARACTERSET','NLS_TERRITO
RY')
5 order by parameter;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET WE8ISO8859P15
NLS_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_TERRITORY AMERICA

SQL> drop table TEST;

Table dropped.

SQL> CREATE TABLE TEST(DATAXML CLOB);

Table created.

SQL> insert into TEST values ('<?xml version="1.0"?><a:entry xmlns:a="http://ww
.w3.org/2005/Atom" xmlns:xhtml="http://www.w3.org/1999/xhtml"><a:content type="
html"><div><span class="commentaire">It></a:entry>');

1 row created.

SQL> SELECT xmltype(d.dataxml).extract('/a:entry/a:content/div/span[@class="com
entaire"]/text()', 'xmlns:a=http://www.w3.org/2005/Atom xmlns:xsi=http://www.w3
org/2001/XMLSchema-instance xmlns:xhtml=http://www.w3.org/1999/xhtml').getStrin
Val() from TEST d;
SELECT xmltype(d.dataxml).extract('/a:entry/a:content/div/span[@class="commenta
re"]/text()', 'xmlns:a=http://www.w3.org/2005/Atom xmlns:xsi=http://www.w3.org/
001/XMLSchema-instance xmlns:xhtml=http://www.w3.org/1999/xhtml').getStringVal(
from TEST d
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 8217 (U+2019)
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

 


Working Testcase with AL32UTF8



SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Database altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 778387456 bytes
Fixed Size 1374808 bytes
Variable Size 260048296 bytes
Database Buffers 511705088 bytes
Redo Buffers 5259264 bytes
Database mounted.
Database opened.
SQL> select *
2 from nls_database_parameters
3 where parameter in
4 ('NLS_CHARACTERSET','NLS_LANGUAGE','NLS_NCHAR_CHARACTERSET','NLS_TERRITO
RY')
5 order by parameter;

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------

NLS_CHARACTERSET
AL32UTF8

NLS_LANGUAGE
AMERICAN

NLS_NCHAR_CHARACTERSET
AL16UTF16


PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------

NLS_TERRITORY
AMERICA


SQL> drop table TEST;

Table dropped.

SQL> CREATE TABLE TEST(DATAXML CLOB);

Table created.

SQL> insert into TEST values ('<?xml version="1.0"?><a:entry xmlns:a="http://www
.w3.org/2005/Atom" xmlns:xhtml="http://www.w3.org/1999/xhtml"><a:content type="x
html"><div><span class="commentaire">It></a:entry>');

1 row created.

SQL> SELECT xmltype(d.dataxml).extract('/a:entry/a:content/div/span[@class="comm
entaire"]/text()', 'xmlns:a=http://www.w3.org/2005/Atom xmlns:xsi=http://www.w3.
org/2001/XMLSchema-instance xmlns:xhtml=http://www.w3.org/1999/xhtml').getString
Val() from TEST d;

XMLTYPE(D.DATAXML).EXTRACT('/A:ENTRY/A:CONTENT/DIV/SPAN[@CLASS="COMMENTAIRE"]/TE

--------------------------------------------------------------------------------

not lucky

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms