DBMS_XMLSTORE and LPX-00222: error received from SAX callback function

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may see the following stack of errors

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00222: error received from SAX callback function
ORA-06512: at "SYS.DBMS_XMLSTORE", line 78
ORA-06512: at "TEST.INSERTXML", line 28
ORA-06512: at line 1

in an attempt of:

1. using DBMS_XMLSTORE.INSERTXML (prior to version 10GR2) for loading a document containing an apostrophe ('). The error is raised regardless of the representation of the apostrophe ( ' or as ' or ') and is filed as a bug in metalink.
   
2. using DBMS_XMLSTORE.INSERTXML for loading a document into a table whose columns have NOT NULL constraints, no default value and the corresponding element(s) in the document present at least one empty field. This holds also for documents containing a subset of the columns of the table and one of the missing columns is mandatory.
3. using DBMS_XMLSTORE.INSERTXML for loading a document into a table with primary, unique or foreign keys and the data being loaded violates one or more constraints.
   
4. using DBMS_XMLSTORE.INSERTXML for loading a document into a table with insufficient column size. For instance, a document containing strings larger than the target table column can hold.
   

Most likely there are more situations involving other procedures in the DBMS_XMLSTORE package that i hadn't the time to investigate yet.
In other words, this LPX-00222 error is a sort of catch-all exception, which means that you'll have to go through the pesky job of identifying the offending element(s) in your source XML file manually.
For large files this can really turn into a nightmare, indeed initially i wasted several hours trying to load an XML containing an apostrophe before realizing that this was a known bug of 10GR1.

See message translations for LPX-00222 and search additional resources.