I'm currently producing XML files from Oracle SQL but have come up against a problem when opening up the XML file in a web browser, I get the below message. This indicates the offending character
XML Parsing Error: not well-formed
Location: file://///data2/data/Download/d7prdv1/prsrepreports/test_error_2.xml
Line Number 80, Column 29: <musicTitle>NATURES SHADOW LOUNGE</musicTitle>
which is due to the musicTitle field having an apostrophe (i.e. the offending apostrophe is shown in the following "NATURE'S SHADOW LOUNGE" ).
After doing a dump of the string above it was found that the apostrophe was in fact chr(146) which is a different apostrophe.
How do I overcome the apostrophe problem in my XML output, as this could happen in other fields? I've added an extract of the SQL below just for info purposes, would I need to replace the apostrophe with a space? The data is loaded into the database from different companies from around the world, so they could be using differing characters.
XMLELEMENT ("musicWork", -- start level 6 tag for music title
XMLFOREST (cc.title AS "musicTitle"), -- start level 7 tag for music title
XMLFOREST (
XMLFOREST ( cc.source_album_title AS "albumTitle",
cc.product_album_promo_title AS "promoTitle",
cc.label AS "label",
cc.catalogue_no AS "catalogNumber",
cc.isrc AS "isrc") AS "recordingInfo"
) -- end level 7 tag for music title
I was thinking of creating the below function, replacing each invalid character with a NULL. Is this the way to go and how would I put chr(146) in the below FUNCTION rather than the character, to ensure I'm getting the offending character?
FUNCTION CONVERT_VALUE (INPUT_STRING IN VARCHAR2)
RETURN VARCHAR2
IS
l_string_converted VARCHAR2(300);
BEGIN
l_string_converted := REGEXP_REPLACE (INPUT_STRING, '*|£|~|^|_', '', 1, 0, 'i');
RETURN l_string_converted;
END CONVERT_VALUE;
So the CONVERT_VALUE function would then be called within my SQL XML script.
XMLELEMENT ("musicWork", -- start level 6 tag for music title
XMLFOREST (CONVERT_VALUE(cc.title) AS "musicTitle"), -- start level 7 tag for music title
XMLFOREST (
XMLFOREST ( CONVERT_VALUE(cc.source_album_title) AS "albumTitle",
CONVERT_VALUE(cc.product_album_promo_title) AS "promoTitle",
cc.label AS "label",
cc.catalogue_no AS "catalogNumber",
cc.isrc AS "isrc") AS "recordingInfo"
) -- end level 7 tag for music
thanks in advance.