1 Reply Latest reply: Aug 7, 2013 7:14 AM by odie_63 RSS

    Xml paring error using utf8

    CRoberts

      I believe I have set up my environment properly to handle utf-8 characters. on my Windows 7 platform.  However for this version of the database, it seems not to be working.

      To get my utl-8 extended Latin-1 characters I used a utf-8 enabled editor which provides a pick list of special characters to select.

       

      This is my database version:

       

      BANNER

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

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      PL/SQL Release 11.2.0.3.0 - Production

      CORE    11.2.0.3.0      Production

      TNS for 64-bit Windows: Version 11.2.0.3.0 - Production

      NLSRTL Version 11.2.0.3.0 - Production

       

      5 rows selected.

       

      I use Sqlplus that is installed with the database and I set up the client (used both cmd and powershell) to run sqlplus using utf-8.

       

      C:\Oracle\product>echo %NLS_LANG%

      AMERICAN_AMERICA.AL32UTF8

       

      C:\Oracle\product>sqlplus xmldemo/******

       

      SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 6 23:39:12 2013

       

      Copyright (c) 1982, 2011, Oracle.  All rights reserved.

       

      Connected to:

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      With the Partitioning, OLAP, Data Mining and Real Application Testing options

       

      CRAIG01> set define off

      CRAIG01> set long 20000

      CRAIG01>

      CRAIG01> SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>

        2  <contact id="1">

        3    <category>customer</category>

        4    <first_name>Johñ</first_name>

        5    <last_name>Pèrez</last_name>

        6  </contact>') as result

        7  FROM Dual

        8  /

       

      ERROR:

      ORA-31011: XML parsing failed

      ORA-19202: Error occurred in XML processing

      LPX-00217: invalid character 164 (U+00A4)

      Error at line 4

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

      ORA-06512: at line 1

       

      no rows selected

       

      However, if I take out the special characters, the xml parses properly and returns it in a utf8 encoding.

       

      CRAIG01> SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>

        2  <contact id="1">

        3    <category>customer</category>

        4    <first_name>John</first_name>

        5    <last_name>Perez</last_name>

        6  </contact>') as result

        7  FROM Dual

        8  /

       

      RESULT

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

      <?xml version="1.0" encoding="UTF-8"?>

      <contact id="1">

        <category>customer</category>

        <first_name>John</first_name>

        <last_name>Perez</last_name>

      </contact>

       

      I have set the NLS_Lang in both my test cases (Dos and powershell v3) and the database itself is in utf-8.  I swear this used to work, it this a bug?

       

      CRAIG01> SELECT SYS_Context('UserEnv', 'Language') as Lan from Dual;

       

      LAN

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

      AMERICAN_AMERICA.AL32UTF8

       

      1 row selected.

        • 1. Re: Xml paring error using utf8
          odie_63

          I have set the NLS_Lang in both my test cases (Dos and powershell v3) and the database itself is in utf-8.

          You mean the database character set, right?

          Like this :

          SQL> select value

            2  from nls_database_parameters

            3  where parameter = 'NLS_CHARACTERSET';

           

          VALUE

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

          AL32UTF8

          ?