Oracle SQL Developer导出结果至XLS时触发ORA-31011等XML解析错误的原因咨询

Great question — this boils down to how Oracle handles XML validation during query vs. how SQL Developer processes results during export. Let's break this down step by step:

Why your query works fine #

When you use xmltype(photoinfo, 1), that second parameter 1 tells Oracle to use non-validating mode (aka XMLTYPE.NONS_VALIDATING). In this mode, Oracle skips strict XML syntax checks and only does enough parsing to locate and extract the text node you're targeting. So even if your XML has minor invalidities (like unescaped special bytes or encoding mismatches), the query will still return the text you need without throwing errors.

Why exporting to XLS triggers the error #

Oracle SQL Developer doesn't just dump the raw query results into Excel. When it encounters XMLType data in your result set, it likely re-processes the XML using a validating parser to serialize it properly for Excel. This strict validation catches issues that the non-validating query mode ignored.

Looking at your error message Lpx-00216 invalid character 226(0xE2):

• The 0xE2 byte is part of UTF-8 encoded special characters (think smart quotes, en dashes, or other non-ASCII symbols). These characters are often invisible or look like regular characters, but they don't play nice with XML validation if there's an encoding mismatch or if the XML doesn't declare the correct character set.
• Your suspicion about & and () is off the mark: () are fully allowed in XML text nodes, and & is the correct escaped form of & (so that's not the problem here). The real culprit is that hidden 0xE2 byte in your XML content.

Fixes to resolve the issue #

1. Clean up the source XML data #

First, fix the invalid characters in your photoinfo column. You can target the problematic rows (40 and 283) with these queries:

• Remove non-printable/non-XML-compliant characters:

  UPDATE ProfilePictures
  SET photoinfo = REGEXP_REPLACE(photoinfo, '[^[:print:]]', '')
  WHERE photosourcetype = 10 AND ROWID IN (
      SELECT ROWID FROM ProfilePictures WHERE photosourcetype = 10 OFFSET 39 ROW FETCH NEXT 1 ROW ONLY,
      SELECT ROWID FROM ProfilePictures WHERE photosourcetype = 10 OFFSET 282 ROW FETCH NEXT 1 ROW ONLY
  );
  

• Or transliterate special characters to ASCII equivalents:

  UPDATE ProfilePictures
  SET photoinfo = UTL_I18N.TRANSLITERATE(photoinfo, 'US7ASCII', 'UTF8')
  WHERE photosourcetype = 10 AND ROWID IN (...); -- same ROWID filter as above
  

2. Modify your query to return clean text #

Instead of returning XMLType data, extract and clean the text directly in your query so SQL Developer only has to handle plain text:

SELECT PhotoNumber, 
       REGEXP_REPLACE(
           extract(xmltype(photoinfo,1), '/DataIM/PhotoChosen/Photo/jpg[1]/text()').getstringval(),
           '[^[:print:]]', ''
       ) AS photoinfo
FROM ProfilePictures 
WHERE photosourcetype = 10;

This way, there's no XML parsing happening during export — just plain text going straight into Excel.

3. Check SQL Developer export settings (long shot) #

Occasionally, SQL Developer has export options related to XML handling. Go to Tools > Preferences > Database > Export and look for any settings related to XML validation. Disabling validation might bypass the error, but cleaning the data or modifying the query is a more reliable fix.

内容的提问来源于stack exchange，提问作者Anonymous