Extractvalue from XML using XML compliant format in Oracle SQL

Question

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

Alex Poole · Accepted Answer · 2015-07-17T08:57:57.527

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.

Extractvalue from XML using XML compliant format in Oracle SQL

1 Answers1