Re: XMLType problem

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: 7 Apr 2004 20:10:38 -0700
Message-ID: <cf15dee4.0404071910.5c65987c@posting.google.com>

"Bruno D." <bdurren_avirer__at_hotmail.com> wrote in message news:<40743139$0$19647$4d4eb98e_at_read.news.fr.uu.net>...
> Hi everyone,
>
> Under oracle 9.2i if you execute this:
>
> select xmltype('<tag>It''s
> cool</tag>').extract('//tag/text()').GetStringVal() from dual;
>
> you get: It's cool
>
> The question is simple: How to get --> It's cool ????
>
> Thanks in advance for any response.

Bruno,

You are going to have create a local function to do the decoding of the XML text. This is true for 9.2.0.1 and 10.1.0.2.

According to p.85-7 of the "Oracle9 iSupplied PL/SQL Packages and Types Reference" manual, there is a second parameter (FLAG) to the DBMS_XMLGEN.CONVERT function which determines whether encoding or decoding is done. The default is to encode.

In theory, one should be able to do the following: SELECT

      DBMS_XMLGEN.convert(
         XMLTYPE(
            '<tag>It''s cool</tag>'
         ).extract(
            '//tag/text()'
         ).GetStringVal(),
         DBMS_XMLGEN.ENTITY_DECODE
      )
   FROM
      dual

However in 9.2.0.1 and 10.1.0.2, the following error message appears:

         DBMS_XMLGEN.ENTITY_DECODE
         *

ERROR at line 8:
ORA-06553: PLS-221: 'ENTITY_DECODE' is not a procedure or is undefined

To get around this, I created a function to wrap the CONVERT function: CREATE OR REPLACE FUNCTION
xml_decode(

      i_xml_string IN VARCHAR2
   )
   RETURN VARCHAR2
IS
BEGIN
   RETURN

      DBMS_XMLGEN.convert(
         i_xml_string,
         DBMS_XMLGEN.ENTITY_DECODE
      );

END;
/

And I modify the query as follows to call my new function:

SELECT

      xml_decode(
         XMLTYPE(
            '<tag>It''s cool</tag>'
         ).extract(
            '//tag/text()'
         ).GetStringVal()
      )
   FROM
      dual

;

I get the following result:
XML_DECODE(XMLTYPE('<TAG>IT''SCOOL</TAG>').EXTRACT('//TAG/TEXT()').GETSTRING VAL(

It's cool

Which is what you want.

Douglas Hawthorne Received on Wed Apr 07 2004 - 22:10:38 CDT