Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Thursday, September 13, 2007

Operating on XMLType containing & (Ampersands)

I was reading today Lewis post on How to Deal With Oracle XML and Ampersands ("&") in XML Documents. Personally I prefer different way. I use XMLElement function to generate XML documents. In my opinion this is more elegant way. The sample from Lewis post will look like that:
set define off
declare
  v_xml xmltype;
begin
select xmlelement("ROWSET",
xmlelement("ROW",
xmlelement("COLA", 'lewis&me')))
into v_xml
from dual;
end;
/
Disadvantage of this method is that you have to use SQL. But in most cases it's used while retrieving data from database, so it should not be a problem. Some time ago I was proposing to read document Mastering XML Generation in Oracle Database 10g Release 2. It is really good source of knowledge on XML manipulation inside Oracle database.
If you want to extract correctly ampersands and other special characters like < and > from XML document I suggest to read my post on differences between extract and extractvalue functions. If you want to manually convert &amp; to & you can use function dbms_xmlgen.convert with second parameter set to 1:
SQL> select dbms_xmlgen.convert('me&amp;lewis', 1) from dual;
me&lewis


Cheers, Paweł

2 comments:

LewisC said...

Pawel,

When generating xml, I also recommend SQL/X (i.e. XMLElement) or XQuery. However this post was about handling an ampersand in the XML. Had the post been about generating XML,I would have used SQL/X.

When I post answers to questions I try to keep the example as specific as possible. I did not want to clutter the example with extraneous detail (which XMLElement would be in this context).

Below are some of my XML posts.

XML in the Database: A Brief Overview (6/2/2005)
XE, XML and WebDAV - Access your XML data in Oracle XE (12/14/2005)
Get the XML out of your database (4/10/2006)
Get the XML out of your database, via HTTP (5/7/2006)
Binary XML - Compressing, Encrypting and Encoding Data in Oracle (11/28/2005)
Oracle and XML In Action - A Real World Example (8/10/2005)
Generate XML Schemas for your Data (6/13/2006)
XML and OO in Oracle, A Scenario (11/17/2005)

Thanks for the post.

LewisC

Paweł Barut said...

Lewis,

Thanks for your comment, and bunch of links.

Paweł

 

Copyright © Paweł Barut
Printing from DOS to USB Printer