ORA-19202: Error occurred in XML processing LPX-00210: expected '<' instead of 'M' Error at line 1

Question

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'

Answer 1

You have data in your PLPROD_MORT.MENU table's MENUDATA column, which holds string values (CLOB in this case), that is not valid XML. From the error you're getting you have at least one value that starts with M, but you your testing in comments suggests you have 31 rows with non-XML values.

I'd speculate the value might be the literal string 'MENUDATA' as that gets the same error:

select xmltype('MENUDATA') from dual;

Error starting at line : 1 in command -
select xmltype('MENUDATA') from dual
Error report -
SQL Error: 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 310
ORA-06512: at line 1
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

but it's only really telling you it starts with an M. If it does have that literal value, though, I'd speculate further that someone has inserted or updated data and accidentally used a literal instead of an identifier, e.g. copying an existing entry using:

insert into PLPROD_MORT.MENU(MENUDATA) 
select 'MENUDATA' from PLPROD_MORT
where ...

instead of:

insert into PLPROD_MORT.MENU(MENUDATA) 
select "MENUDATA" from PLPROD_MORT
where ...

But that is several layers of speculation, and whatever caused that original data problem is nothing to do with the query you asked about - that is just seeing the symptoms of that bad data.

Answer 2

Oracle is complaining that "SYS.XMLTYPE" is not a valid xml column or type. It expects <element>....</element> format

Answer 3

I have faced the same issue and I was able to solve that by correcting the Xpath within the extractvalue() function. I have entered a wrong Xpath for the CLOB value extraction. Hope this might be help full to solve your problem.