Skip to Main Content
  • Questions

  • XMLTYPE returning unknown special character

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Amit.

Asked: October 16, 2025 - 1:11 am UTC

Last updated: October 23, 2025 - 2:26 pm UTC

Version: 19.16.0.0.0

Viewed 100+ times

You Asked

Hello Sir,

We're using Oracle Database 19C Enterprise edition.
We're getting a XML tag in Varchar2 format. Then we're using XMLTYPE to convert that to XML. However, incase when there's special character in the XML tag it's giving a unknown special character in the output. What can we do to get the same output

Sample SQL:
select XMLTYPE('<tag1> a''bc </tag1>') from dual;


Output:
<tag1> a&apos;bc </tag1>


Expected output:
<tag1> a'bc </tag1>

and Chris said...

You can use XMLSerialize/XMLCast/XMLTable to convert the value to a string before returning it to the client:

select x
, xmlserialize ( content t.x as varchar2(20) ) xserialized
, xmlcast ( xmlquery ( '/tag1/text()' passing t.x returning content ) as varchar2(20) ) xcast
from (
  select xmltype ( '<tag1> a''bc </tag1>' ) x
  from   dual
) t;

X                              XSERIALIZED                    XCAST
------------------------------ ------------------------------ --------------------
<tag1> a&apos;bc </tag1>       <tag1> a'bc </tag1>             a'bc

select *
from   xmltable ( 
  '/' passing xmltype ( '<tag1> a''bc </tag1>' ) 
  columns 
    tag1 varchar2(20) path '/tag1'
) t;

TAG1
--------------------
 a'bc

Rating

  (1 rating)

Comments

A few questions for clarification

mathguy, October 24, 2025 - 3:19 am UTC

Amit - if you don't mind, a few questions for clarification.

First, what do you mean by "unknown special character"? &apos; is not an unknown character. It is an XML entity ("entity" is a technical XML term) representing "apostrophe".

Second, what do you mean by "the same output"? XMLTYPE returns a value of data type XMLTYPE. In XML, &apos; is exactly the same as a single apostrophe. Instead, I believe you are viewing the value on screen, as a string (the string representation of an XML tag) - and you want that to look like your input. That, as Chris demonstrated, is a matter of serializing your XMLTYPE value; but your value as you reported it is ALREADY the same as the input, as XMLTYPE values. (For comparison, 003 and 3 may not look the same on screen; but as NUMBERS, not as strings, they are the same!)

In any case, I am unable to reproduce what you reported. Using SQL Developer on a Windows 11 machine running Oracle 19.3, and running the query as a "script" (if I run it as a "query" the output will simply show <XMLTYPE> as the output, but then if I double-click on it I get the same output as when I run as "script"):

select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------------------------
"Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0"





select XMLTYPE('<tag1> a''bc </tag1>') from dual;

XMLTYPE('<TAG1>A''BC</TAG1>')
-----------------------------
<tag1> a'bc </tag1>



I see the same output (with an actual apostrophe, not an XML entity representing it) on LiveSQL.Oracle.com. This seems to suggest that your observation may be rather dependent on your user interface. What IDE are you using to run your queries?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.