Oracle SQL XML Parsing Error because of apostrophe

2019-09-15 23:07发布

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.

0条回答
登录 后发表回答