Shaun Kinnair Shaun Kinnair - 21 days ago 5
SQL Question

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

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

Answer

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:]]')