Database Software

Hi,
The (e acute) é characters get garbled when they are retrieved from the
XMLType column of a regular table.

How can we fix to get (e acute) é characters properly?

We both tried setting "setenv NLS_LANG French_France.WE8ISO8859P1" and
"setenv NLS_LANG French_France.WE8DEC" before loading the table.

Database version:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

The sample test case is as follows:

--connect to any schema where you can store XMLType
set long 2000;
set pagesize 2000;
set serveroutput on;
--delete from test;
drop table test;
create table test (id number, xmldata XMLType);


declare
featureDescriptorXML  CLOB;
xml_type XMLType;
new_xml_type XMLType;
myName varchar2(100);
myName2 varchar2(100);
myName3 varchar2(100);
stmt varchar2(4000);
begin
 featureDescriptorXML :=
 '<?xml version="1.0" encoding="UTF-8"?>' ||
 '<abc:TheFeature xmlns:' || 'de' || '="' || 'http://abc.klmno.org/fghde' || '" xmlns:abc="http://www.ghijklmn.net/abc"' ||
 ' xmlns:xyz="http://www.ghijklmn.net/xyz">' ||
 '<abc:Name>de:MyGénérique</abc:Name>' ||
 '</abc:TheFeature>';
 xml_type := xmltype(featureDescriptorXML);
 myName := xml_type.extract('/abc:TheFeature/abc:Name/text()', 'xmlns:abc="http://www.ghijklmn.net/abc"').getStringVal();
 dbms_output.put_line('abc:Name value stored in VARCHAR2 variable from XMLType variable is ' || myName);
 -- can show French chars

 insert into test(id, xmldata) values(20, xml_type);

 stmt := 'select t.xmldata.extract(''/abc:TheFeature/abc:Name/text()'', ''xmlns:abc="http://www.ghijklmn.net/abc"'').getStringVal() from test t';
 execute immediate stmt into myName2;
 dbms_output.put_line('abc:Name value stored in VARCHAR2 variable from XMLType column in 2nd version is ' || myName2);
 -- cannot show French chars


 stmt := 'select  t.xmldata from test t';
 execute immediate stmt into new_xml_type;
 myName3 := new_xml_type.extract('/abc:TheFeature/abc:Name/text()', 'xmlns:abc="http://www.ghijklmn.net/abc"').getStringVal();
 dbms_output.put_line('abc:Name value stored in VARCHAR2 variable from first XMLType column and then from XMLType variable in 3rd version is ' || myName3);
 -- cannot show French chars

end;
/

select t.xmldata.extract('/abc:TheFeature/abc:Name/text()', 'xmlns:abc="http://www.ghijklmn.net/abc"').getStringVal()
from test t;
-- Cannot show French chars


select t.xmldata.extract('/abc:TheFeature/abc:Name/text()', 'xmlns:abc="http://www.ghijklmn.net/abc"').getStringVal() "myname"
from test t;
-- Cannot show French chars


select t.xmldata.getCLOBVal() from test t;
-- Cannot show French chars


select t.xmldata from test t;
-- Can show French chars

Output is as follows with setenv NLS_LANG French_France.WE8ISO8859P1
and NLS_DATABASE_PARAMETERS are as follows:

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8DEC
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.2.0

20 ligne(s) sélectionnée(s).

Table creé.

abc:Name value stored in VARCHAR2 variable from XMLType variable is
de:MyGénérique
abc:Name value stored in VARCHAR2 variable from XMLType column in 2nd version is
de:MyGénérique
abc:Name value stored in VARCHAR2 variable from first XMLType column and then
from XMLType variable in 3rd version is de:MyGénérique

Procdure PL/SQL terminée avec succès.


T.XMLDATA.EXTRACT('/ABC:THEFEATURE/ABC:NAME/TEXT()','XMLNS:ABC="HTTP://WWW.GHIJK
--------------------------------------------------------------------------------
de:MyGénérique


myname
--------------------------------------------------------------------------------
de:MyGénérique


T.XMLDATA.GETCLOBVAL()
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="DEC-MCS"?>
<abc:TheFeature xmlns:de="http://abc.klmno.org/fghde" xmlns:abc="http://www.ghij
klmn.net/abc" xmlns:xyz="http://www.ghijklmn.net/xyz">
  <abc:Name>de:MyGénérique</abc:Name>
</abc:TheFeature>



XMLDATA
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?>
<abc:TheFeature xmlns:de="http://abc.klmno.org/fghde" xmlns:abc="http://www.ghij
klmn.net/abc" xmlns:xyz="http://www.ghijklmn.net/xyz">
  <abc:Name>de:MyGénérique</abc:Name>
</abc:TheFeature>

We also tried setting NLS_CHARACTERSET to AL32UTF8
via ALTER DATABASE CHARACTER SET,
and then shutting down the database and restarting it.
But, that did not help.

Thanks