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'bc </tag1>
Expected output:
<tag1> a'bc </tag1>
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'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