ORA-31011:XML解析失败-无效字符(oracle sql) [英] ORA-31011: XML parsing failed - invalid characters (oracle sql)

问题描述

我正在使用SQL在Oracle 11g数据库上生成XML文档.但是我对数据库字段有问题，因为标题字段包含许多字符，其中一些字符被XML视为无效，所以我试图使用以下语句捕获尽可能多的字符并将它们转换为NULL.

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.

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

我正在使用XML版本'1.0"encoding =" UTF-8，是否有简便的方法还是我必须找到失败的记录(可能是200万条记录中的任何一条)?标题字段包含世界各地的歌曲标题，我可以使用REGEXP_REPLACE在char(32)之间获取一定范围的字符，并说char(255)不在此范围内的任何内容替换为NULL.

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.

或者还有另一种解决方案.

OR is there another solution.

先谢谢大家

推荐答案

您是否考虑过只保留想要的字符?我不知道它们是什么，但是像这样的东西

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

这篇关于ORA-31011:XML解析失败-无效字符(oracle sql)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！