Skip to Main Content
  • Questions
  • Invalid XML character Error - How to find the invalid character from a VARCHAR2 database column?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Dinushi.

Asked: July 21, 2021 - 8:23 am UTC

Last updated: July 29, 2021 - 10:04 am UTC

Version: 10

Viewed 100+ times

You Asked

Hello,

Oracle newbie here.

I am getting this error "Character reference "&#56256" is an invalid XML character" for XML data that is printed onto a report. The XML data that is causing the issue is from a VARCHAR2 data column in the database.
I have filtered out the column to a separate backup table I have created and I want to go through the records in order to find where this invalid character is in.
Then I need to write an update statement replacing the invalid character with a valid one.
I am not sure how to do this. Is there a regex I can write with a SELECT statement for this?

I tried below, but they didn't bring up any results:

select *
from tabname 
where instr(colname,chr(56256)) > 0;


select *
from tabname 
where colname like unistr('%\dbc0%'); 


Glad if someone one can help.

Thank you!

and we said...

In XML you can use codes in the form &#NNNN; to represent an entry in the ISO/IEC 10646 character set.

For example, &#65 is the character A, so XML processing can change this code to the letter A:
select xmltype ( '<test>&#65;</test>' ).extract ('test') x
from   dual;

X                
<test>A</test>   


&#56256 is an invalid code - it's a digit too long. So I think you need to search for the exact text &#56256, not the converting this to ascii/unicode. e.g.:
select *
from   tabname 
where  instr ( colname, '&#56256' ) > 0;




Rating

  (3 ratings)

Comments

Dinushi Liyanage, July 27, 2021 - 6:52 am UTC

Hello TOM,

Thank you for the answer!
I have tried the below query and it still doesn't fetch any results.
I have to add, I have posted the question with Oracle version as 10g by mistake. The version is 11g.

select *
from   tabname 
where  instr ( colname, '&#56256' ) > 0;


Is there any other suggestion that might help me resolve this?

Many thanks!
Chris Saxon
July 27, 2021 - 12:02 pm UTC

Please share an example of the XML document that's causing the error

Dinushi Liyanage, July 28, 2021 - 1:50 pm UTC

Hi TOM,

I have exported the column data into an XML and uploaded in below link (file size: 359KB):
https://mega.nz/file/d01HkCCb#QGMG2F7XLdCErSOUbni_-5g8l-X56AMnd8f0z3vVnro

Thank you!
Chris Saxon
July 28, 2021 - 3:24 pm UTC

Hmmm, I can't find the string &#56256 in that file.

I don't know what the issue is here. We'll need a lot more information to provide meaningful help here.

XML data that is printed onto a report

What report? How is it generated? Exactly what software throws the error?

Dinushi Liyanage, July 28, 2021 - 3:50 pm UTC

Hi TOM,

Additionally, I have exported ALL records with the related column data into an XML and uploaded in below link as well (file size: 31.2MB):
https://mega.nz/file/o40m0CoQ#lBA62Ij1jYeQZgWQwRp8SMQ56GdbUlGVCEIgMU6taJk

Thank you for the support!
Chris Saxon
July 29, 2021 - 10:04 am UTC

You still haven't explained how you're generating this report. Exactly what software are you using? What's the SQL statement that's throwing the error?

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.