9 Replies Latest reply: Feb 1, 2012 5:48 AM by odie_63 RSS

    special character is converted while reading XML data

    789077
      Dear all,

      i am reading an xmltype column from a table and storing the value of the same in a Varchar2. i am using the below query.

      SELECT RECID , E.XMLRECORD.GETCLOBVAL () AS POXML FROM MY_TABLE E;

      Result
      -----------
      'MINOR&apos

      the issue is when reading the data all the special character is changed for example single quotes ' display as &apos and & display as &amp.

      i do not want this to happen i need the value as it is.

      Result Expected

      ------------------

      'MINOR'

      need to know how to do this asap.

      Thanks & Regards
        • 1. Re: special character is converted while reading XML data
          odie_63
          What are you doing exactly?
          The query you gave doesn't reflect what you described.

          Do you want to convert an XMLType to a VARCHAR2 datatype, or are you extracting separate elements from the XML?

          Please post the original XML here (or a representative sample of it), *between
           tags*, and give your database version.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
          • 2. Re: special character is converted while reading XML data
            789077
            below is the sample XMLTYPE

            <row id='20120131101253750.0503015306' xml:space='preserve'><c1>T24;ASA;051993677303;20120131101253;N;US0010300;5199_INPUTTER___OFS_;********;MBM.ASA.V.1.1;000;OK;1003;3015306;20120131101253;US0010300;20120131101253;024747474017;*MR WON &apos;MINOR&apos*;;USD;;3821.00;8432.3;921;BO TELL OF XYZ;</c1></row>

            i want to read this from and XMLTYPE Column into a varchar2 column for which i am using

            SELECT E.XMLRECORD.GETCLOBVAL () AS POXML from MY_TABLE E

            but when GETCLOBVAL () reads the XMLTYPE Column it converts all the special characters into XML Value. for e.g in the above XML data. MR WON &apos;MINOR&apos; should appear as MR WON *'MINOR'*;

            my database version is

            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
            PL/SQL Release 10.2.0.4.0 - Production
            • 3. Re: special character is converted while reading XML data
              odie_63
              That's expected behaviour.

              Using getClobVal() method just provides a serialized (character) representation of an XML content, but it's still valid XML, i.e. with the necessary escaped sequences.
              Granted, it's not mandatory to escape the apostrophe character ' to &amp;apos; in text() nodes but Oracle chooses to do so.

              If your ultimate goal is to retrieve the value in element "c1", you can directly do the following.
              In this case, since we extract a scalar value, Oracle will perform the conversion for us and output regular characters instead of escaped entities :
              SQL> SELECT extractValue(t.xmlrecord, '/row/@id') as row_id
                2       , extractValue(t.xmlrecord, '/row/c1') as c1
                3  FROM my_table t
                4  ;
               
              ROW_ID                         C1
              ------------------------------ ----------------------------------------------------------------------------------------------------
              20120131101253750.0503015306   T24;ASA;051993677303;20120131101253;N;US0010300;5199_INPUTTER___OFS_;********;MBM.ASA.V.1.1;000;OK;1
                                             003;3015306;20120131101253;US0010300;20120131101253;024747474017;MR WON 'MINOR';USD;;3821.00;8432.3;
                                             921;BO TELL OF XYZ;
               
              If, for some good reasons, you absolutely want to unescape character entities in the serialized representation, then try one of these two :
              SELECT dbms_xmlgen.convert(t.xmlrecord.getclobval(), 1)
              FROM my_table t
              ;
              or,
              SELECT utl_i18n.unescape_reference(t.xmlrecord.getclobval())
              FROM my_table t
              ;
              but that's definitely not the good approach.

              Edited by: odie_63 on 1 févr. 2012 11:17
              • 4. Re: special character is converted while reading XML data
                789077
                i guess the first option works for me but when i am using

                SELECT extractValue(t.xmlrecord, '/row/c1') as c1 FROM MY_TABLE t

                it give me the error

                ORA-19025: EXTRACTVALUE returns value of only one node

                how ever the query SELECT extractValue(t.xmlrecord, '/row/@id') as row_id FROM MY_TABLE t works fine.
                • 5. Re: special character is converted while reading XML data
                  odie_63
                  ORA-19025: EXTRACTVALUE returns value of only one node
                  That would mean there are multiple c1 elements per row.
                  Is that the case?

                  Could you post an example of the XML producing the error?
                  • 6. Re: special character is converted while reading XML data
                    789077
                    <row id="20120131101253750.0503015306" xml:space="preserve">
                    <c1>XYW;SMS;051993677303;20120131101253;N;US0010300;5199_INPUTTER___OFS_;********;MBM.SMS.V.1.1;000;OK;1003;3015306;20120131101253;AE0010300;20120131101253;023015306017;ABC XYZ TYU &apos;MINOR&apos;;USD;;0010.00;3817.3;921;BO GOVT OF US;</c1>
                    </row>


                    what would you mean by multiple element in c1 . as far i understand the whole thing between <c1> and </c1> is one record.

                    please let me know if this is what you were not looking for.
                    • 7. Re: special character is converted while reading XML data
                      odie_63
                      what would you mean by multiple element in c1 .
                      The error you're having generally occurs when the XPath expression in extractValue() targets multiple nodes, instead of a single element or attribute, hence my assumption.

                      Here's my complete test case. Tell me what you're doing differently :
                      SQL> create table my_table (xmlrecord xmltype);
                       
                      Table created
                       
                      SQL> set scan off
                      SQL> 
                      SQL> insert into my_table (xmlrecord) values (
                        2  xmltype('<row id="20120131101253750.0503015306" xml:space="preserve">
                        3  <c1>XYW;SMS;051993677303;20120131101253;N;US0010300;5199_INPUTTER___OFS_;********;MBM.SMS.V.1.1;000;OK;1003;3015306;20120131101253;AE0010300;20120131101253;023015306017;ABC XYZ TYU &apos;MINOR&apos;;USD;;0010.00;3817.3;921;BO GOVT OF US;</c1>
                        4  </row>')
                        5  );
                       
                      1 row inserted
                       
                      SQL> column row_id format a30
                      SQL> column c1 format a100
                      SQL> 
                      SQL> SELECT extractValue(t.xmlrecord, '/row/@id') as row_id
                        2       , extractValue(t.xmlrecord, '/row/c1') as c1
                        3  FROM my_table t
                        4  ;
                       
                      ROW_ID                         C1
                      ------------------------------ ----------------------------------------------------------------------------------------------------
                      20120131101253750.0503015306   XYW;SMS;051993677303;20120131101253;N;US0010300;5199_INPUTTER___OFS_;********;MBM.SMS.V.1.1;000;OK;1
                                                     003;3015306;20120131101253;AE0010300;20120131101253;023015306017;ABC XYZ TYU 'MINOR';USD;;0010.00;38
                                                     17.3;921;BO GOVT OF US;
                       
                      • 8. Re: special character is converted while reading XML data
                        789077
                        oh its working fine. but only when i am trying to fetch all the record that time its giving me that ora-19025 error. what could be the reason of that and how can i detect it and remove it.

                        thanks for all you efforts
                        • 9. Re: special character is converted while reading XML data
                          odie_63
                          Not sure what's going on...

                          Are you certain each xmlrecord column contains only one occurrence of <row> and <c1>?

                          You can run this to check if there's more than one c1 per record :
                          select xmlquery('count(//c1)' passing t.xmlrecord returning content).getnumberval() as count_c1
                          from my_table t
                          ;