This discussion is archived
5 Replies Latest reply: Sep 5, 2013 8:21 AM by Jason_(A_Non) RSS

how to insert special character(&) with in xml type column, which stores as clob within database table

veejai24 Pro
Currently Being Moderated

I have a table which has the structure like below.

 

create table XMLTypeTable(FileName varchar2(50) primary key, XMLFileContent XMLType)

XmlType XMLFileContent STORE AS CLOB;

 

but when i try to insert with the below insert query, the statement bombs.. throwing error.

 

insert into XMLTypeTable(FILENAME, XMLFileContent) values ('CurrencyCountryToCode',XMLType('<dvm name="CurrencyCountryToCode" xmlns="http://xmlns.oracle.com/dvm">

  <description>

  </description>

  <columns>

    <column name="CountryName"/>

    <column name="CurrencyCode"/>

  </columns>

  <rows>

    <row>

      <cell>USA</cell>

      <cell>USD &  US</cell>

    </row>

    </rows>

</dvm>

'));

 

The above statement is not going through, its not getting inserted into the table. errors out at the highlighted tag for &, but the above can be inserted into column which has varchar2 type.

 

Thanks,
Vijay

  • 1. Re: how to insert special character(&) with in xml type column, which stores as clob within database table
    odie_63 Guru
    Currently Being Moderated

    but when i try to insert with the below insert query, the statement bombs.. throwing error.

    Not that I don't have a precise idea of what the error is, but don't you think it would be interesting to give the error message you got in the first place? And the database version too? (CLOB storage is deprecated now)

     

    Yes, the ampersand character alone is invalid in XML, as well as <, >, and " and ' for attribute values.

    & must be escaped to &amp; :

     

    <cell>USD &amp;  US</cell>


  • 2. Re: how to insert special character(&) with in xml type column, which stores as clob within database table
    veejai24 Pro
    Currently Being Moderated

    Thanks for your reply.

     

    The below insert goes through but when i query i get the same way as it went in. But cant i get the clean way while reading from database.

     

    insert into XMLTypeTable(FILENAME, XMLFileContent) values ('CurrencyCountryToCode',XMLType('<dvm name="CurrencyCountryToCode" xmlns="http://xmlns.oracle.com/dvm">

      <description>

      </description>

      <columns>

        <column name="CountryName"/>

        <column name="CurrencyCode"/>

      </columns>

      <rows>

        <row>

          <cell>USA</cell>

          <cell>AUD &amp;: AUD</cell>    </row>

        </rows>

    </dvm>

    '));

     

     

    When i read from database table, by using the select query iam getting as below.

          <cell>AUD &amp;: AUD</cell>

     

    Cant i get it as below.

     

          <cell>AUD & AUD</cell>

     

    Cheers !!!
    Vijay

  • 3. Re: how to insert special character(&) with in xml type column, which stores as clob within database table
    odie_63 Guru
    Currently Being Moderated

    Cant i get it as below.

     

          <cell>AUD & AUD</cell>

    No.

    As long as you're selecting wellformed XML content (i.e. XMLType datatype) you'll get the &amp; entity instead of &.

    That's the way XML works, nothing specific to Oracle here.

     

    However, if you're extracting the node (scalar) value itself, Oracle will convert back the reference to its original character.

    For example :

    SQL> select x.*

      2  from XMLTypeTable t

      3     , XMLTable(

      4         XMLNamespaces(default 'http://xmlns.oracle.com/dvm')

      5       , '/dvm/rows/row'

      6         passing t.xmlfilecontent

      7         columns cell1 varchar2(10) path 'cell[1]'

      8               , cell2 varchar2(10) path 'cell[2]'

      9       ) x

    10  where filename = 'CurrencyCountryToCode' ;

     

    CELL1      CELL2

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

    USA        AUD &: AUD

     

  • 4. Re: how to insert special character(&) with in xml type column, which stores as clob within database table
    veejai24 Pro
    Currently Being Moderated

    Thanks Man,

     

    Is CLOB is replaced by BLOB ? If i use BLOB can i achieve to get only the below result or still i have to use the same procedure.

    <cell>AUD & AUD</cell>

     

    Iam running the above table on the below database version.

     

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

    PL/SQL Release 11.2.0.1.0 - Production

    "CORE 11.2.0.1.0 Production"

    TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production

    NLSRTL Version 11.2.0.1.0 - Production

     

    Cheers !!!
    Vijay

  • 5. Re: how to insert special character(&) with in xml type column, which stores as clob within database table
    Jason_(A_Non) Expert
    Currently Being Moderated

    Is CLOB is replaced by BLOB ?

    No.

    CLOB = Character Large Object

    BLOB = Binary Large Object

    By default, in 11.2.0.1 and earlier, the default storage for XMLTYPE was CLOB, as you are seeing with your table.  Starting with 11.2.0.2, Oracle changed the default type to be SECUREFILE BINARY XML.  This storage mechanism became available with 11.1.0.6, but still defaulted to using CLOB.  Not the question you asked but information you should be aware of.

     

    As Odie said

    "As long as you're selecting wellformed XML content (i.e. XMLType datatype) you'll get the &amp; entity instead of &.

    That's the way XML works, nothing specific to Oracle here."

     

    That does not matter what underlying storage type you use.  Oracle is following the W3C specifications for what wellformed XML looks like.

    List of XML and HTML character entity references - Wikipedia, the free encyclopedia

    Extensible Markup Language (XML) 1.0 (Fifth Edition)

    When you have Oracle (or any tool in general) show you the XML, it would show you the XML as stored.  When you retrieve contents of nodes, then the tool should auto-translate encodings for your.

Legend

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