Hi all,
Using the following code to generate some XML using UTF-8 encoding, DB charset is US7ASCII
PRG3 SQL> select * from adam_source;
RIVER_NAME BUILDING_NAME
------------------------- --------------------------------------------------
YTM040002001001406 ▒▒▒▒▒▒▒r▒▒
PRG3 SQL> declare
v_text varchar2(100); 2
3 v_text2 varchar2(100);
4 begin
5 select river_name,building_name into v_text,v_text2 from adam_source;
6 insert into adam
7 (select xmlroot(xmlelement("transaction",
8 xmlelement("test1",XMLCDATA(v_text)),
9 xmlelement("test2",XMLCDATA(v_text2))
10 ),version '1.0" encoding="UTF-8').getclobval() as xml from dual);
11 end;
12 /
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00216: invalid character 129 (0x81)
Error at line 1
ORA-06512: at line 6
So I thought I’d try converting it to UTF-8 before handing it to the XML parser, I get a different character it doesn’t like but ultimately the same result:
PRG3 SQL> declare
2 v_text varchar2(100);
3 v_text2 varchar2(100);
4 begin
5 select river_name into v_text from adam_source;
6 select convert(building_name,'UTF8','US7ASCII') into v_text2 from adam_source;
7 insert into adam
8 (select xmlroot(xmlelement("transaction",
9 xmlelement("test1",XMLCDATA(v_text)),
10 xmlelement("test2",XMLCDATA(v_text2))
11 ),version '1.0" encoding="UTF-8').getclobval() as xml from dual);
12 end;
13 /
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00216: invalid character 239 (0xEF)
Error at line 1
ORA-06512: at line 7
Any ideas what I can do to avoid this? Different encoding?
Thanks!
Adam
PRG3 SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production