Reading XML with Reserved Characters

I'm not understanding what you are needing.  If you have invalid characters in the 'XML', then you really don't have XML.

Hopefully you are referring to 'encoded' characters that need to be decoded for proper display.

Please clarify and provide some sample data and expected results.

Also the Oracle database version you are running.

are you trying to generate the entities?


"&lt;"  for "<"

if so,  the htf package has an function to do that

select htf.escape_sc('<>&') from dual

I guess they are encoded. I'm obviously not clear on the terminology.  

An example: The result should be "'RUMSON-FAIR HAVEN BANK & TRUST CO."  The data coming in is: "GLENS FALLS NATL BK&amp;TR CO"

DB Version: We're on 11g.

I will research how to use the htf.escape. Thanks for the tip.

I think the questioner may be trying to go the other direction (e.g '&lt;' to '<'). I have used the following function to do that:
create or replace function convert_reserved_chars(p_string in varchar2)
return varchar2 is
begin
 return REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(v_text_string, '&amp;', '&'), '&apos;', ''''), '&quot;', '"'),'&lt;','<'),'&gt;','>');
end;

just small note,  I recommend doing the & replace LAST since that character is part of the entity definitions
it's not likely to create an overlap, but it's simple to mitigate the problem by merely changing the order


create or replace function convert_reserved_chars(p_string in varchar2)
return varchar2 is
begin
 return REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p_string, '&apos;', ''''), '&quot;', '"'),'&lt;','<'),'&gt;','>'),'&amp;', '&');
end;
/

>>An example: The result should be

How are you extracting them?

If you use Oracle's extractvalue call it should decode them for you:

using sqlplus:
set define off
select extractvalue(xmltype('<a>BANK &amp; TRUST</a>'),'/a/text()') from dual;
set define on

That is correct. I am "going the opposite direction" reading data and needing to transform it. Thanks for the info.  I'd used a  REPLACE but thought there should be something with Oracle that should already be decoding which, slightwv, you pointed out.  It just didn't make sense to me that this wasn't already being done automatically.  

This is the code I was using and I will need to either tweak or use a REPLACE function.
 l_return_value := p_response_in_out.doc.extract('//'||p_name_in||'/child::text()',p_namespace_in).getstringval();

If you only have a few extracts to do take a look at:
dbms_xslprocessor.valueOf:

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_xslpro.htm#i1004102

I tried extractvalue and for some reason it is still not transforming the &amp; values.  I will read up on dbms_xslprocessor; however, since I have to get the fix into production quickly for now I will be using a REPLACE function.  Thank you all for all of your help.

In the end I was able to get the extractvalue working in my database package.
For some reason it wasn't working for me querying directly against stored values in an XMLTYPE field.  I'm sure there's an easy explanation, but thank you, slightwv, for your help with this issue.  

Sure, if that is helpful for record keeping purposes.

don't reopen for this,  just throwing it out as an add on


In 10g Oracle has a unescape that corresponds to the htf  escape above


select utl_i18n.unescape_reference('BANK &amp; TRUST') FROM DUAL