Oracle CLOB to XMLTYPE Errors

Question

We are running an older Oracle Server, 10.1.0.5... yes, we will upgrade soon. Some relevant NLS Settings are as follows...

• NLS_CHARACTERSET IS 'US7ASCII'
• NLS_LENGTH_SEMANTICS IS 'BYTE'

Onto the question... We have well formed XML stored in CLOB columns. When trying to pull XML Element data I am using syntax like

select XMLTYPE(I.CLOBFIELD).EXTRACT('/Record/RecordID/text()') as Record_ID
from iTable I

Where I.CLOBFIELD is the CLOB containing some XML.

This works great, usually.

We sometimes get an error when the CLOB data contains non-ascii data that has been encoded using "&#xxxx;".

For example if the following text

... “violation” ...

were found anywhere in the CLOB I would get the above error when running this query.

The left quote is x201C and the right is x201D, these are stored as plain ascii in the xml as &#x201C and &#x201D, respectively.

Punctuation like this has crept into our CLOB fields (mostly from users cutting and pasting from ms-word). At some point we will clean them up, probably when we migrate, but for now we would like the above query to always work, even when these Unicode equivalents are found in the CLOB.

Note: I would use XMLTABLE(..) if I could but it is not available in this edition of Oracle.

Suggestions or alternatives to XMLTYPE would be welcome.

Thank you,

sse