Oracle SQL XML Offending characters in XML file (UTF-8)

Question

I'm trying to find a way of either replacing/deleting offending characters from the Oracle SQL XML files I'm creating. The structure of the XML file is correct but the company I'm sending the files too can't load the files because of the offending characters in the XML file. I'm using Oracle 11g release 2 database.

What can I do and what are my options?

A screen shot is below of an example of these offending characters, both myself and the company i'm sending the files too are using the Unicode UTF-8 encoding.

An example of a tag that it does not like is below for ZOË WANAMAKER

<prodAssociatedParty>
    <apType>ACTOR</apType>
    <lastName>ZOË WANAMAKER</lastName>
</prodAssociatedParty>

Ë (0xCB), É (0xC9), Ï (0xCF), £ (0xA3), Ç (0xC7), Ò (0xD2), Ü (0xDC)

Thanks in Advance for any advice.

But what do you want achieve? get rid of this special characters or You want to give advice to other company how to fix their application? — Arkadiusz Łukasiewicz
– Arkadiusz Łukasiewicz, Commented Mar 16, 2017 at 12:39

Wernfried Domscheit · Accepted Answer · 2017-03-16 12:58:15Z

1

Obviously your XML files has <?xml version="1.0" encoding="UTF-8"?> as declaration but in fact it is stored with different encoding.

Do not declare your XML-File with <?xml version="1.0" encoding="UTF-8"?> just because "everybody does it". If you declare UTF-8 then you also have to save it as UTF-8. Check save options at your editor, resp. settings in your application which creates the file.

I assume the XML File is saved in Windows-1252 encoding. Try <?xml version="1.0" encoding="ISO-8859-1"?> instead. Windows-1252 is very similar to ISO 8859-1, see ISO 8859-1 vs. ISO 8859-15 vs. Windows-1252 vs. Unicode so it should work unless your XML contains any of € Š š Ž ž Œ œ Ÿ.

However, according XML specification only UTF-8 and UTF-16 are mandatory, ISO 8859-x are optional, so the target application may not be able to read the file. In this case you have to convert your XML-File as UTF-8.

answered Mar 16, 2017 at 12:58

Wernfried Domscheit

60.4k10 gold badges92 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shaun Kinnair Over a year ago

i'll take a look into this and report back

Shaun Kinnair · Accepted Answer · 2017-03-23 16:02:15Z

1

Thanks for all your replies. In the end I put the below into my PL/SQL, as like some of you said even though you have version '1.0" encoding="UTF-8" in your SQL XML code the file was being stored in a different encoding. So I needed to force it to write/store an XML file in UTF-8 format.

If you look up DBMS_XSLPROCESSOR.clob2file there are a number of parameters passed to this procedure, one being the character set to use for the output file. Which in this case for UTF-8 it was nls_charset_id('AL32UTF8').

DBMS_XSLPROCESSOR.clob2file(l_clob, l_directory, l_file_name||'.xml',nls_charset_id('AL32UTF8'));

thanks Guys

answered Mar 23, 2017 at 16:02

Shaun Kinnair

5353 gold badges11 silver badges30 bronze badges

Collectives™ on Stack Overflow

Oracle SQL XML Offending characters in XML file (UTF-8)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related