How to get ampersand value inside an XMLTYPE

Question

I have the below query:

select XMLType('<root>
  <TPL>
    <fld>f7</fld><val>v1v2</val>
  </TPL>
</root>') from dual;

When i execute the above query,it executes perfectly fine and i get the following output:

<root>
  <TPL>
    <fld>f7</fld><val>v1v2</val>
  </TPL>
</root>

But when i execute the following(which is my requirement):

select XMLType('<root>
  <TPL>
    <fld>f7</fld><val>v1&v2</val>
  </TPL>
</root>') from dual;

I am being asked to enter the value for '&' in a popup screen.

I have tried the following two ways:

select XMLType('<root>
  <TPL>
    <fld>f7</fld><val>v1&amp;v2</val>
  </TPL>
</root>') from dual;


select XMLType('<root>
  <TPL>
    <fld>f7</fld><val>v1''&''v2</val>
  </TPL>
</root>') from dual;

But still unable to get the OUTPUT correctly which should be:

<root>
  <TPL>
    <fld>f7</fld><val>v1&v2</val>
  </TPL>
</root>

How can i achieve this?

You didn't say what client you're using, but these options should work for most of them: https://stackoverflow.com/questions/118190/how-do-i-ignore-ampersands-in-a-sql-script-running-from-sql-plus — kfinity, Mar 19 '19 at 13:36

score 1 · Answer 1 · answered Mar 19 '19 at 14:32

There's two problems here - & is a special character in both Oracle SQL and XML, and it has to be escaped in both. cdb_dba gave some examples of how to escape it in SQL (CHR(38) or & at the end of a string), and mkuligowski gave examples of how to escape it in XML (& or CDATA), but the two kinds of escaping need to be combined. Here's a couple of examples:

-- with CHR() + CDATA
select XMLType('<root>
  <TPL>
    <fld>f7</fld><val><![CDATA[v1'|| chr(38) || 'v2]]></val>
  </TPL>
</root>') from dual;

-- with ampersand at the end of a string + "amp" character reference 
select XMLType('<root>
  <TPL>
    <fld>f7</fld><val>v1&' || 'amp;v2</val>
  </TPL>
</root>') from dual;

Thank you for the clarification! I don't typically work with XMLType data, so I will add that to my list of things to watch out for if I ever happen to find myself in that area. :) — 1991DBA, Mar 19 '19 at 15:22

score 0 · Answer 2 · answered Mar 19 '19 at 13:36

0

Instead of using v1''&''v2 or v1&v2, try using v1'|| chr(38) || 'v2 or 'v1 &'||'v2'

chr(38) is ASCII for the amperstand, and the second option is just a different method of concatenating. Hope this helps!

answered Mar 19 '19 at 13:36

1991DBA

805
1
9
18

I am sorry but both the ways are actually not working – vikky Mar 19 '19 at 13:40

mkuligowski · Answer 3 · 2019-03-19T13:41:38.177

0

I bet that you are using SQLDeveloper when querying. Try run SET DEFINE OFF; and you won't be prompted for the value .

Moreover, when you creating XMLType object then you should escape illegal characters, so following will work:

SET DEFINE OFF;
select XMLType('<root>
 <TPL>
  <fld>f7</fld><val>v1&amp;v2</val>
 </TPL>
 </root>') from dual;

On the other hand, you can always wrap illegal character in CDATA:

select XMLType('<root>
 <TPL>
  <fld>f7</fld><val><![CDATA[v1&v2]]></val>
 </TPL>
</root>') from dual;

edited Mar 19 '19 at 13:41

answered Mar 19 '19 at 13:37

mkuligowski

1,544
1
17
27

I am using Toad For Oracle ,Not SQL Developer – vikky Mar 19 '19 at 13:39
Well, I am using Oracle Version 11.0.0.116 and SET DEFINE OFF is not working – vikky Mar 19 '19 at 13:46
SET DEFINE is a SQLPlus command and must be executed with the script engine (F5). F9 executes single statements only and does not handle SQLPlus. – mkuligowski Mar 19 '19 at 13:50
Thanks for all the help.But none of the above solution is working – vikky Mar 19 '19 at 14:06

How to get ampersand value inside an XMLTYPE

3 Answers3