| Characterset issue in XML (merged) [message #569804] |
Thu, 01 November 2012 15:43  |
wantmannu
Messages: 42
Registered: June 2009
Location: cali
|
Member |
|
|
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
(
x_item_doc
);
COMMIT;
END;
/
select * from xx_testxml;
Result
<SyncItemPrimaryAttribute>revisions to §6</SyncItemPrimaryAttribute>
Thanks,
Anjali
[Updated on: Thu, 01 November 2012 15:46] Report message to a moderator |
|
|
|
| Re: XML DATA to XMLTYPE field issue [message #569811 is a reply to message #569804] |
Thu, 01 November 2012 21:00  |
 |
spacebar
Messages: 26
Registered: February 2007
|
Junior Member |
|
|
See if you get the same problem running this:
declare
x_item_doc sys.xmltype := null;
begin
select xmlelement("SyncItemPrimaryAttribute", 'revisions to §6' )
into x_item_doc
from dual;
dbms_output.put_line( x_item_doc.getCLOBVal() );
end;
DBMS_OUTPUT:
------------
<SyncItemPrimaryAttribute>revisions to §6</SyncItemPrimaryAttribute>
As an additional note, I use charset(WE8ISO8859P1), and I get the same results as you when I use your charset(AL32UTF8):
select dbms_xmlgen.getXMLType(
q'{select convert('revisions to §6', 'AL32UTF8') myValue from dual}')
from dual
---MYVALUE--
<ROWSET>
<ROW>
<MYVALUE>revisions to §6</MYVALUE>
</ROW>
</ROWSET>
[Updated on: Thu, 01 November 2012 21:15] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
