0 Replies Latest reply: Nov 5, 2012 1:38 PM by 972627 RSS

    SQL XML special character issue

    972627
      Hi,

      Version of DB: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

      I have issue while loading xml_data into xmltype field in the table.
      Issue is whenever there is a special character like this 'revisions to §6' in xml text then it is 'revisions to §6' when its in the xmltype field of the table.

      There is this new character 'Â' appended before every special character.
      I have checked the database characterset
      Characterset: NLS_CHARACTERSET     Value: AL32UTF8

      Did anyone have this issue before or do anyone please help me how to get this issue fixed.
      Try this simple script
      CREATE TABLE xx_testxml
        (lx xmltype
        );
      /
      DECLARE
        x_item_doc sys.XMLTYPE := NULL;
      BEGIN
        SELECT XMLELEMENT("SyncItemPrimaryAttribute", 'revisions to §6' )
        INTO x_item_doc
        FROM dual;
        INSERT
        INTO xx_testxml VALUES
          (
            (SELECT regexp_replace(x_item_doc, 'â', '') FROM dual
            )
          );
        COMMIT;
      END;
      /
      SELECT * FROM xx_testxml;
      
      Result:
      <SyncItemPrimaryAttribute>revisions to §6</SyncItemPrimaryAttribute>
      
      {End Code}