ORA-19202：XML 处理 LPX-00210 时出错：第 1 行预期出现“<”而不是“M”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33544697/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-19202: Error occurred in XML processing LPX-00210: expected '<' instead of 'M' Error at line 1
提问by MCarn5
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:
我让这个查询正常工作多年，直到最近我在收集器中更改了另一个查询并保存。我根本没有改变这个 xml 查询，但现在它在 xml 部分失败了。我绝对不精通 xml，所以我正在寻找一些帮助以查看问题所在。这是我收到的错误消息：
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
ORA-31011：XML 解析失败 ORA-19202：处理 XML 时出错 LPX-00210：第 1 行预期出现“<”而不是“M” ORA-06512：在“SYS.XMLTYPE”第 0 行 ORA-06512：在第 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'
回答by Alex Poole
You have data in your PLPROD_MORT.MENUtable's MENUDATAcolumn, 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.
您的PLPROD_MORT.MENU表的MENUDATA列中有数据，该列包含字符串值（在本例中为 CLOB），这是无效的 XML。从您得到的错误中，您至少有一个以 开头的值M，但是您在评论中的测试表明您有 31 行具有非 XML 值。
I'd speculate the value might be the literal string 'MENUDATA'as that gets the same error:
我推测该值可能是文字字符串，'MENUDATA'因为它会得到相同的错误：
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:
但它只是告诉你它以M. 但是，如果它确实具有该文字值，我会进一步推测有人插入或更新了数据并意外使用了文字而不是标识符，例如使用以下方法复制现有条目：
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.
但这是几层推测，导致原始数据问题的原因与您所询问的查询无关 - 这只是看到了错误数据的症状。
回答by Raghu Kasturi
Oracle is complaining that "SYS.XMLTYPE" is not a valid xml column or type. It expects <element>....</element>format
Oracle 抱怨“SYS.XMLTYPE”不是有效的 xml 列或类型。它期望<element>....</element>格式
回答by Aravinda Meewalaarachchi
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.
我遇到了同样的问题，我能够通过更正extractvalue()函数中的 Xpath 来解决这个问题。我为 CLOB 值提取输入了错误的 Xpath。希望这可能有助于完全解决您的问题。
