ORA-31011: XML 解析失败 - 无效字符 (oracle sql)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40493076/
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-31011: XML parsing failed - invalid characters (oracle sql)
提问by Shaun Kinnair
I'm producing an XML document using SQL on Oracle 11g database. But I'm having a problem with a database field, because the title field holds many characters some of which XML see's as invalid, I'm trying to use the below statement to catch as many as possible and convert them to NULL.
我正在 Oracle 11g 数据库上使用 SQL 生成 XML 文档。但是我遇到了一个数据库字段的问题，因为标题字段包含许多字符，其中一些 XML 认为是无效的，我试图使用下面的语句来捕获尽可能多的字符并将它们转换为 NULL。
REGEXP_REPLACE (title, ''|￡|&|*|@|-|>|/|<|;|\', '', 1, 0, 'i') as title
I'm still getting the parse problem so I know there must be more invalid characters I've missed. I know it's failing on this field as when I change the field to a string 'Title' (as below), the document is parsed and it works fine.
我仍然遇到解析问题，所以我知道我错过了更多无效字符。我知道它在这个字段上失败了，因为当我将字段更改为字符串“标题”（如下所示）时，文档被解析并且工作正常。
REGEXP_REPLACE ('title', ''|￡|&|*|@|-|>|/|<|;|\', '', 1, 0, 'i') as title
I'm using XML version '1.0" encoding="UTF-8', is there an easy way around this or do I have to locate the records that are failing which could be any from 2 million records. The title field holds song titles from all over the world, could I use REGEXP_REPLACE to get a range of characters between char(32) and lets say char(255) anything not in this range replace with NULL.
我正在使用 XML 版本“1.0”编码 =“UTF-8”，有没有简单的方法可以解决这个问题，或者我是否必须找到失败的记录，可能是 200 万条记录。标题字段包含来自世界各地的歌曲标题，我可以使用 REGEXP_REPLACE 来获取 char(32) 之间的字符范围，然后说 char(255) 不在此范围内的任何内容都替换为 NULL。
OR is there another solution.
或者还有其他解决方案。
thanks in advance guys
提前谢谢你们
采纳答案by Gordon Linoff
Have you considered only keeping the characters you want? I don't know what they are, but something like this
你有没有考虑过只保留你想要的角色？我不知道它们是什么，但像这样
REGEXP_REPLACE('title', '[^a-zA-Z0-9 ,.!]', '', 1, 0, 'i') as title
回答by Wernfried Domscheit
The only illegal characters in XML are &, <and >(as well as "or 'in attributes).
XML 中唯一的非法字符是&,<和>（以及属性中的"或'）。
You can escape such characters with an Oracle function
您可以使用 Oracle 函数转义此类字符
Example:
例子：
select DBMS_XMLGEN.CONVERT(title) from ...
Details: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xmlgen.htm#i1013100
详情：https: //docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xmlgen.htm#i1013100
