I have a data which is Japanese Names. I have Created a table with two columns to insert the data in these columns.
When the data is inserted then select query fires correct result, but when i use the XML the data get corroupt.
NLS_CHARACTERSET is 'AL32UTF8'
SQL> CREATE TABLE JAP_NM_TEST
2 (
3 KANJI_NM VARCHAR2(500 BYTE),
4 KANA_NM VARCHAR2(500 BYTE)
5 );
Table created.
SQL> insert into JAP_NM_TEST values('タナカ トモナリ','タナカ トモナリ');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from JAP_NM_TEST;
KANJI_NM KANA_NM
------------ ------------
タナカ トモナリ タナカ トモナリ
SQL> -- What if when we select this data is xml
SQL> -- The Data get corroupt
SQL> SELECT (XMLFOREST (XMLELEMENT ("names", XMLELEMENT ("KanjiNm", a.kanji_nm)) AS "NM").getclobval ()) AS SQLDATA
2 FROM jap_nm_test a;
SQLDATA
--------------------------------------------------------------------------------
<NM><names><KanjiNm>・スナカ ・ス・ス</KanjiNm></names></NM>