Forum Stats

  • 3,839,816 Users
  • 2,262,538 Discussions
  • 7,901,063 Comments

Discussions

Unescape of special characters < and > in Oracle sql

User_1KK7K
User_1KK7K Member Posts: 6 Red Ribbon

Hi,

I am storing in my Oracle SQL database table string field with tag values having '<' and '>'. when I try to to retrieve them for Xmlhttp request the values displayed are '&lt' and '&gt'. How can I prevent this conversion and display '<' and '>' symbols?

I used the following functions that I found but did not work(Also tried using xmlgen but it did not work form me)

select UTL_I18N.UNESCAPE_REFERENCE(string_field) from test where id='123'

Here the string_field column of test table of type Char has the value <test><tags>ABC<test><tags> and I want to display the same value with out < and > being replaced by &lt and &gt.

How is it possible?

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,610 Red Diamond
    edited Dec 12, 2020 7:44PM

    You not telling whole story. If string field is CHAR/VARCHAR2 then select would disply it as is:

    with test as (
                  select '<test><tags>ABC<test><tags>' string_field from dual
                 )
    select string_field from test
    /
    
    STRING_FIELD
    ---------------------------
    <test><tags>ABC<test><tags>
    
    with test as (
                  select '<test><tags>ABC<test><tags>' string_field from dual
                 )
    select UTL_I18N.UNESCAPE_REFERENCE(string_field) from test
    /
    
    
    UTL_I18N.UNESCAPE_REFERENCE(STRING_FIELD)
    -----------------------------------------
    <test><tags>ABC<test><tags>
    
    
    SQL>
    

    SY.

  • User_1KK7K
    User_1KK7K Member Posts: 6 Red Ribbon
    edited Dec 12, 2020 8:26PM

    No this is in XML when using xml http request it converts < to &lt and to &gt. These functions didnt work for me the issue is still there. Just querying the database with Select has not issue. SO its printed as

    &lttest&gt&lttags&gtABC&lttest&gt&lttags&gt
    

    Anyway I found a function DMS_XMLGEN in Oracle sql since this issue arises when converting to xml http request but not sure how to use it here.

  • Paulzip
    Paulzip Member Posts: 8,722 Blue Diamond
    edited Dec 13, 2020 3:12AM

    You are definitely not telling the full picture. What do you mean "these functions didn't work for me"? What exactly are you trying to do?

    Are you sending or receiving data?

    If you are receiving data, you have to extract the content from the XML. Oracle will take care of the unescaping, if you use the XML DB functions available.

    If you are sending the data, XML HAS to escape those characters, it's part of the standard. The other end will unescape it when deserializing.

    Here's an example....

    with data(before) as (
      select '<test><tags>ABC<test><tags>'  -- Your data 
      from dual
    )
    , some_xml as (
      select before, 
             XMLElement(                -- Your data encoded in XML
               "Content",  before
             ) the_xml
      from data
    )
    select x.before, x.the_xml, t.after -- Your data extracted again
    from some_xml x,
         XMLTable(
           '/'
           passing x.the_xml
           columns
             after varchar2(100) path '/Content'
         ) t
    
    BEFORE                      | THE_XML                                                                | AFTER
    ----------------------------------------------------------------------------------------------------------------------------------
    <test><tags>ABC<test><tags> | <Content>&lt;test&gt;&lt;tags&gt;ABC&lt;test&gt;&lt;tags&gt;</Content> | <test><tags>ABC<test><tags>