Home » Developer & Programmer » JDeveloper, Java & XML » XMLCDATA in Oracle 9i (or how to write extended chars in a XML file) (Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production)
icon5.gif  XMLCDATA in Oracle 9i (or how to write extended chars in a XML file) [message #278821] Mon, 05 November 2007 15:33 Go to next message
gourmet
Messages: 5
Registered: June 2007
Junior Member
Hi,

I wonder if there are some kind of "clon" of XMLCDATA SQL function for Ora9. This function exists in Ora10 but sadly not in Ora9.

I need to include the <![CDATA[ ]]> in a XMLElement sentence, i.e
select xmlelement("test",'123') 
from dual

But when I try to hardcore the CDATA the A.I of XMLElement Wink converts it to entities and then get a result like this

SQL> select xmlelement("test",'<![CDATA[123]]>') from dual
  2  /

XMLELEMENT("TEST",'<![CDATA[123]]>')
-----------------------------------------------------------
<test>&lt;![CDATA[123]]&gt;</test>

SQL>

Are there some way to bypass the converts triggered by the XMLElement of Ora9?

If there are not a solution to this: someone knows how to convert characters others than <,> using a existent SQL function?, I mean a DBMS_XMLGen.Convert in steroids Razz.

i.e: I have a spanish word like "ESPAŅA" and need to write this in a XML file like
ESPA & #209;A
(the space before and after the ampersand must not exists in the XML file to write, I put them just to show in the example).

Any help will be welcome. Thanks a lot.

Cheers!

*** UPDATE, AUTORESPONSE ***


Must create this workaround function and then use it inside the xmlelement sentence:
create or replace
FUNCTION XMLCDATA (elementname VARCHAR2, cdatavalue VARCHAR2)
   RETURN XMLTYPE
AS
BEGIN
   RETURN XMLTYPE (   '<'
                   || elementname
                   || '><![CDATA['
                   || cdatavalue
                   || ']]></'
                   || elementname
                   || '>'
                  );
END; 

found this at Oracle Community Discussion Forums. Example to use it in Ora9:
SELECT XMLELEMENT ("usuario", XMLCDATA ('nombre', 'Juan Perez')).EXTRACT('/*') result
  FROM DUAL







[Updated on: Tue, 06 November 2007 08:33]

Report message to a moderator

Re: XMLCDATA in Oracle 9i (or how to write extended chars in a XML file) [message #293656 is a reply to message #278821] Mon, 14 January 2008 07:34 Go to previous message
lala1
Messages: 2
Registered: March 2007
Junior Member
The example posted will return
<usuario><nombre><![CDATA[Juan Perez]]></nombre>
</usuario>
I have tried to change the function to
create or replace
FUNCTION XMLCDATA (cdatavalue VARCHAR2)
RETURN XMLTYPE
AS
BEGIN
RETURN XMLTYPE ( '<![CDATA['
|| cdatavalue
|| ']]>'
);
END;
so that it would return
<usuario><![CDATA[Juan Perez]]>
</usuario>
but it does not like it: it returns error LPX-00231: invalid character 33 ('!') found in a Name or Nmtoken...
If I remove the '!', it then returns the error on '[' and so on
Any advise?
Thanks
Previous Topic: Load Java
Next Topic: XML namespace extraction
Goto Forum:
  


Current Time: Fri Mar 29 10:35:10 CDT 2024