This discussion is archived
9 Replies Latest reply: Feb 1, 2012 3:48 AM by odie_63 RSS

special character is converted while reading XML data

789077 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    <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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    ;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points