ORA-31011: XML parsing failed - invalid characters (oracle sql)

Question

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

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.

thanks in advance guys

Gordon Linoff · Accepted Answer · 2016-11-08 17:34:22Z

1

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

answered Nov 8, 2016 at 17:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shaun Kinnair Over a year ago

Hi, that actually worked could you explain what the above is doing Gordon Linoff?

Gordon Linoff Over a year ago

The regular expression looks for any characters that are not numbers, characters or space, comma, period, or exclamation point.

Wernfried Domscheit · Accepted Answer · 2016-11-08 19:18:10Z

1

The only illegal characters in XML are &, < and > (as well as " or ' in attributes).

You can escape such characters with an Oracle function

Example:

select DBMS_XMLGEN.CONVERT(title) from ...

Details: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xmlgen.htm#i1013100

answered Nov 8, 2016 at 19:18

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

ORA-31011: XML parsing failed - invalid characters (oracle sql)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related