XML Oracle SQL character sets ORA-31011: XML parsing failed

Question

I have a XML SQL statement that I'm using (see below Oracle11g SQL) to overcome a passing error (below), the SQL statement works but it does strip out characters that are not specified in the REGEXP_REPLACE statement for example the following É Ë È that need to be included in my XML output file, I could include these and more but is there a short cut to include these characters as well in the REGEXP_REPLACE without naming a long list of characters. I have tried SQL select DBMS_XMLGEN.CONVERT(T.title) as I've been told this strips out all invalid characters but this failed with the same parsing error as below.

The reason why I'm using REGEXP_REPLACE, as it was failing because of certain characters (for example char(160) none-breaking space) that were in the title database field.

Is there a simpler solution or do I have to included a long list of characters in the REGEXP_REPLACE.

Thanks in advance.

DECLARE 
*
ERROR at line:
ORA-31011: XML parsing failed
ORA-06512: at line 8

The trouble is because I'm dealing with different character

SELECT XMLAGG ( XMLELEMENT ("referenceAvGroup",                                                               
                   XMLFOREST (cre_surr_id AS "societySeriesNumber",
                              'SERIES' AS "avGroupType"),
                          XMLELEMENT ("prodTitle",                                                                   
                                 XMLFOREST (title AS "title",
                                            tt_code_description AS "titleType")
                                      )                                                                         
                            )
               )                                                                      
FROM (  SELECT trim(REGEXP_REPLACE(T.title, '[^a-zA-Z0-9 ,!@():.#’"*&+}/\©{ÂÀÃÏÎÔÜ€Ç¿%´_$()'',-`]', '', 1, 0, 'i')) AS title,
                'ORIGINAL'  AS tt_code_description,
                T.tt_code AS tt_codes,
                T.cre_surr_id
        FROM    titles T
        WHERE   T.tt_code = Com0300.title_type_registered) 
WHERE cre_surr_id = prod.prod_series_cre_surr_id

It might be easier to maintain a list of characters you want to replace... — Gottfried Lesigang
– Gottfried Lesigang, Commented Nov 10, 2016 at 10:29
Can you update your question to include sample data that replicates the issue you are seeing, please? — Boneist
– Boneist, Commented Nov 10, 2016 at 11:28
What is set in this parameter? SELECT * FROM V$NLS_PARAMETERS where parametrer= 'NLS_NCHAR_CONV_EXCP' — Arkadiusz Łukasiewicz
– Arkadiusz Łukasiewicz, Commented Nov 10, 2016 at 11:37
Looks like you have a lot of trouble with your XML; stackoverflow.com/questions/40493076/… and stackoverflow.com/questions/38877171/… Why the hell do you like to remove special characters? It is not the source of your problem. — Wernfried Domscheit
– Wernfried Domscheit, Commented Nov 10, 2016 at 11:42

Shaun Kinnair · Accepted Answer · 2016-11-18 14:24:01Z

1

Just an update in the end I went with the below. There seem to be a lot of control characters that had been loaded from files into the database, which were causing my SQL XML not to parse. After using the below and removing these characters my SQL XML ran fine. Thanks for all your advice on this subject.

regexp_replace(replace(prod.CAMPAIGN_NAME,chr(160),''),'[[:cntrl:]]')

answered Nov 18, 2016 at 14:24

Shaun Kinnair

5353 gold badges11 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

XML Oracle SQL character sets ORA-31011: XML parsing failed

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related