Q:

Extractvalue from XML using XML compliant format in Oracle SQL

I have a  requirement . I need to query a table and display the column value by transforming escape character into XML format 
example " & " to "&"  
Details
I have table name "AppName"
select NAME from AppName

NAME
---------
Oracle & Database

What  i need to do is get the output in XML compliant format. something like below
eg:
NAME
------
Oracle &amp; Database

I have used the DBMS_XMLGEN to make it XML Complaint and trying to using extractvalue to get the value. but it return as "Oracle & Database"
Below is query to transform to XML format
select SYS.DBMS_XMLGEN.getXML( 'select NAME from APPName ') XML_out from dual

"<?xml version="1.0"?>
 <ROWSET>
 <ROW>
  <NAME>Oracle &amp; Database<NAME>
  </ROW>
 </ROWSET>
 "

And then i try to extract the Name
select extractvalue( XMLType.createXML(SYS.DBMS_XMLGEN.getXML( 'select NAME from APPNAme' )),'/ROWSET/ROW/NAME') XML_out from dual

which return back the output same as "Oracle & Database"  
But i need output as "Oracle &amp; Database"

How can i get this output using DBMS_XMLGEN or is there is any easy way.
Any help is greatly appreciated

A:

You're doing more work than you need to; dbms_xmlgen has a convert() function, which escapes special characters (entities) by default:
select dbms_xmlgen.convert(name) from appname;

DBMS_XMLGEN.CONVERT(NAME)                                                      
--------------------------------------------------------------------------------
Oracle &amp; Database                                                           

The second argument defaults to ENTITY_ENCODE, and you can either set that explicitly (using the underlying value 0 in plain SQL) to encode:
select dbms_xmlgen.convert('Oracle & Database', 0) from dual;

DBMS_XMLGEN.CONVERT('ORACLE&DATABASE',0)                                       
--------------------------------------------------------------------------------
Oracle &amp; Database                                                           

Or to 1 (ENTITY_DECODE) to do the reverse operation and decode:
select dbms_xmlgen.convert('Oracle &amp; Database', 1) from dual;

DBMS_XMLGEN.CONVERT('ORACLE&AMP;DATABASE',1)                                   
--------------------------------------------------------------------------------
Oracle & Database                                                               

If you already had the XMLType, or wanted it in that format for some other reason, you could use an XMLQuery instead of extractValue:
select xmlquery('/ROWSET/ROW/NAME/text()'
  passing XMLType.createXML(SYS.DBMS_XMLGEN.getXML('select name from appname'))
  returning content) XML_out from dual;

XML_OUT                                                                        
--------------------------------------------------------------------------------
Oracle &amp; Database

But in this case the conversion to XML is a bit pointless.

