Ora 19202 возникла ошибка при обработке xml

I had this query working properly for years until recently where i changed another query in the collector and saved. I didnt change this xml query at all, but now its failing on the xml part. Im and definitely not versed well in xml so im looking for some help to see where the issue is. Here is the error message i am receiving:

ORA-31011: XML parsing failed ORA-19202: Error occurred in XML
processing LPX-00210: expected ‘<‘ instead of ‘M’ Error at line 1
ORA-06512: at «SYS.XMLTYPE», line 0 ORA-06512: at line 1

Here is my query that worked properly and now is not:

select Distinct trim(B.RoleID) as RoleID,
             replace(replace(extractvalue(value(C), '/item/menu') || ' >' ||
             extractvalue(value(C), '/item/submenu1') || ' >' ||
             extractvalue(value(C), '/item/submenu2') || ' >' ||
             extractvalue(value(C), '/item/label'), ' > > >', ' >'),' > >', ' >') as Res,
             extractvalue(value(C), '/item/name') as Action,
             extractvalue(value(C), '/item/visible') as visible
             from PLPROD_MORT.MENU A, 
             (SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Processing and Closing (Group A)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Branch Consumer (Group B)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Branch Level Input (Group C)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('DU (Group D)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Escrow Accounting (Group E)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Override Locking - PE (Group F)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Post Closing (Group G)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Servicing - Reconciliation - Loan Support (Group H)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Point of Sale Consumer (Group I)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Training (Group J)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Secondary Marketing (Group M)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Originator (Group O)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Appraisal (Group P)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Quality Control (Group Q)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Rate Lock (Group R)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Servicing (Group S)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Lock - PE (Group T)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Underwriting (Group U)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Test X Level (Group X)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Management Override (Group Y)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Test (Group Z)') AS RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('All screens and stages (No Groups)') As RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Inquiry Only (Access I)') As RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Operator (Access O)') As RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Preferred Operator (Access P)') As RoleID FROM PLPROD_MORT.MENU
             union  
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('Supervisor (Access S)') As RoleID FROM PLPROD_MORT.MENU
             union
             SELECT DISTINCT TRIM("MENUNAME") AS Menu, TRIM("MENUNAME") || ' - ' || upper('No Access') As RoleID FROM PLPROD_MORT.MENU) B,
             table
             (XMLSEQUENCE
             (extract
             (XMLTRANSFORM
             (XMLTYPE(A.MENUDATA),
              XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output method="xml" version="1.0" omit-xml-declaration="yes"/><xsl:template match="/"><menus><xsl:for-each select="//menu"><item><name>Menu</name><menu><xsl:value-of select="@label"/></menu><submenu1/><submenu2/><label/><mnemonic><xsl:value-of select="@mnemonic"/></mnemonic><visible><xsl:value-of select="@visible"/></visible></item></xsl:for-each><xsl:for-each select="//menu/item"><item><name>Menu Item</name><menu><xsl:value-of select="../@label"/></menu><submenu1/><submenu2/><label><xsl:value-of select="@label"/></label><mnemonic><xsl:value-of select="@mnemonic"/></mnemonic><visible><xsl:value-of select="@visible"/></visible></item></xsl:for-each><xsl:for-each select="//menu/submenu"><item><name>Submenu</name><menu><xsl:value-of select="../@label"/></menu><submenu1><xsl:value-of select="@label"/></submenu1><submenu2/><label/><mnemonic><xsl:value-of select="@mnemonic"/></mnemonic><visible><xsl:value-of select="@visible"/></visible></item></xsl:for-each><xsl:for-each select="//menu/submenu/item"><item><name>Submenu Item</name><menu><xsl:value-of select="../../@label"/></menu><submenu1><xsl:value-of select="../@label"/></submenu1><submenu2/><label><xsl:value-of select="@label"/></label><mnemonic><xsl:value-of select="@mnemonic"/></mnemonic><visible><xsl:value-of select="@visible"/></visible></item></xsl:for-each><xsl:for-each select="//menu/submenu/submenu"><item><name>Submenu</name><menu><xsl:value-of select="../../@label"/></menu><submenu1><xsl:value-of select="../@label"/></submenu1><submenu2><xsl:value-of select="@label"/></submenu2><label/><mnemonic><xsl:value-of select="@mnemonic"/></mnemonic><visible><xsl:value-of select="@visible"/></visible></item></xsl:for-each><xsl:for-each select="//menu/submenu/submenu/item"><item><name>Submenu Item</name><menu><xsl:value-of select="../../../@label"/></menu><submenu1><xsl:value-of select="../../@label"/></submenu1><submenu2><xsl:value-of select="../@label"/></submenu2><label><xsl:value-of select="@label"/></label><mnemonic><xsl:value-of select="@mnemonic"/></mnemonic><visible><xsl:value-of select="@visible"/></visible></item></xsl:for-each></menus></xsl:template></xsl:stylesheet>'))
              , '//item'))) C
              where trim(A.MENUNAME) = B.MENU and extractvalue(value(C), '/item/visible') = 'true'

I am trying to use new APEX JSON APIs in my code and when I try to generate JSON with large data and nesting, I am getting error as stated in my question. I have tried following code

SET SERVEROUTPUT ON
DECLARE
   l_cursor SYS_REFCURSOR;
BEGIN

  OPEN l_cursor FOR
    SELECT K.KB_ID "KB_ID",
           K.TITLE "TITLE",
           K.CONTENT "CONTENT",
           K.KEYWORDS "KEYWORDS",
           CURSOR(SELECT F.FILE_ID AS "FILE_ID",
                         F.FILE_NAME AS "FILE_NAME",
                         F.FILE_URL AS "FILE_URL"
                  FROM MY_FILES F
                  WHERE F.TYPE_PK = K.KB_ID AND F.TYPE='ITV_KB') AS "Files"
    FROM ITV_KB_ML_V K
    WHERE K.ENABLED = 1;

 APEX_JSON.initialize_clob_output;

 APEX_JSON.open_object;
 APEX_JSON.write('KNOWLEDGE_DATA', l_cursor);
 APEX_JSON.close_object;

 DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
 APEX_JSON.free_output;
END;

I am getting error at APEX_JSON.write('KNOWLEDGE_DATA',l_cursor) and my CONTENT column has large data, if I comment CONTENT then it works fine. Please suggest what I can use to generate JSON with large data in ORACLE APEX.

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ı