set define offDisadvantage 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.
declare
v_xml xmltype;
begin
select xmlelement("ROWSET",
xmlelement("ROW",
xmlelement("COLA", 'lewis&me')))
into v_xml
from dual;
end;
/
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 & to & you can use function dbms_xmlgen.convert with second parameter set to 1:
SQL> select dbms_xmlgen.convert('me&lewis', 1) from dual;
me&lewis
Cheers, Paweł
2 comments:
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
Lewis,
Thanks for your comment, and bunch of links.
Paweł
Post a Comment