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.

LPX-00601: Invalid token in 'XPath expression'

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

You can get this error while attempting to extract nodes containing a namespace prefix using a SQL expression like the following:

select extractValue(
xml_content,
'//openSearch:totalResults') val
from xml_documents
where id = 38;

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '//openSearch:totalResults'

The problem is caused by the missing namespace parameter in the function call:

select extractValue(
xml_content,
'//openSearch:totalResults',
'xmlns:openSearch="http://a9.com/-/spec/opensearchrss/1.0/"') val
from xml_documents
where id = 38;

VAL
---
175

Clearly the namespace declaration in green, must match the corresponding string in the source XML document.
Note also that Oracle uses double quotes to enclose the string whereas single or double quotes might be used in the source document indifferently, what really matters is the string delimited by the quotes and do not forget that is case sensitive: a mismatching string will cause a null value to be returned instead of the expected node.

See a previous posting on problems related to default namespaces declarations and Oracle XML functions.

ORA-31011 and ORA-19202

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00200: could not convert from encoding UTF-8 to UCS2
Error at line 1931

I must admit that when i saw this exception for the first time, i thought it would take a long time to be sorted out.
But, fortunately, things went much better!

For some reason the XML parser of Oracle doesn't like some character in the file, however line 1931 or whatever number is present in the message you've got, doesn't normally match with the line number that you can see if you open the xml file with a text editor like Ultraedit or XMLSpy.

Even if you don't know precisely where is the offending charater, you can be sure that it will look like some weird glyph, it was a square in my case and when i opened the xml file with the hex viewer of Ultraedit, i could see it was some kind of junk character whose hex code was FDFF.

I don't really know why Oracle rejected it if the file was meant to be UTF-8, i'll investigate the problem later if i'll have the time.

I tried to to think of a way of recognizing this kind of situations upfront and with the help of an XSL transformation probably one can get rid of these characters or replace them with other symbols, however, for some reason, my old XMLSpy version seems unable to cope with a translate function containing characters represented by their hex code like ﷿ or at least so does the Evaluate XPath menu function.

My idea was to look for elements matching the following expression:

//elem[contains(translate(.,'﷿','¿'),'¿')]

But XMLSpy failed to find any element, until i copied and pasted the offending character from the xml file directly in place of ﷿.

Later i'll try with a real transformation and if xmlspy fails, i'll stick to the good ole Saxon.


In the meanwhile, happy searching!



Updated March 6, 2007

PS: Well, if you are unfamiliar with Unicode, UTF-8, UCS-2 and other character encoding issues, i bet you'll find this article very helpful and also very entertaining!
For instance now i am finally clear with one of the issues: FDFF must be read the other way around, FFFD, in big-endian mode and it represents a so-called replacement character, that is a placeholder for a character that is not defined in Unicode.

What i am still not clear with is if Oracle should accept this character or not.
I posted a message in the XML DB forum, let's see if the Oracle gurus come up with an answer.

But, at any rate, now i know exactly what to look for in the files.

Updated March 7, 2007

I downloaded Saxon-B version 8.9, i "upgraded" my original XSLT from 1.0 to 2.0 and now, before outputting the text nodes of my elements, i replace any unwanted character with a more readable string.

replace( . ,'�','** U+FFFD **')

See message translations for ORA-31011, ORA-19202, LPX-00200 and search additional resources.

---

ORA-31011: Analisi XML non riuscita
ORA-19202: Errore durante XML processing

ORA-31011: fallo en el análisis de XML
ORA-19202: Se ha producido un error en el procesamiento de XML

ORA-31011: Ha fallat l'anàlisi XML
ORA-19202: S'ha produït un error en el processament XML

ORA-31011: Echec d'analyse XML
ORA-19202: Une erreur s'est produite lors du traitement la fonction XML ()

ORA-31011: XML-Parsing nicht erfolgreich
ORA-19202: Fehler bei XML-Verarbeitung aufgetreten

ORA-31011: Η ανάλυση XML απέτυχε
ORA-19202: Παρουσιάστηκε σφάλμα στην επεξεργασία XML

ORA-31011: XML-analyse fejlede
ORA-19202: Fejl opstod ved XML-behandling

ORA-31011: XML-analys misslyckades
ORA-19202: Ett fel uppstod vid XML-bearbetningen

ORA-31011: XML-analysen mislyktes
ORA-19202: Det oppstod en feil i XML-behandlingen

ORA-31011: XML-jäsennys epäonnistui
ORA-19202: Virhe XML-käsittelyssä

ORA-31011: Az XML-elemzés nem sikerült
ORA-19202: Hiba lépett fel az XML-feldolgozás során:

ORA-31011: Nu s-a reuşit analizarea XML
ORA-19202: Eroare la procesarea XML

ORA-31011: Ontleden van XML is mislukt.
ORA-19202: Fout in XML-verwerking ().

ORA-31011: falha na análise XML
ORA-19202: Ocorreu um erro no processamento XML

ORA-31011: Falha na análise de XML
ORA-19202: Ocorrência de erro no processamento de XML

ORA-31011: сбой разбора XML
ORA-19202: Возникла ошибка при обработке XML

ORA-31011: selhala analýza XML
ORA-19202: Vyskytla se chyba při zpracování XML

ORA-31011: Syntaktická analýza XML zlyhala
ORA-19202: Pri spracovaní XML sa vyskytla chyba

ORA-31011: Niepowodzenie analizy składniowej XML
ORA-19202: Wystąpił błąd podczas przetwarzania XML

ORA-31011: XML ayrıştırılamadı
ORA-19202: XML işlenirken hata ortaya çıktı