Re: pl/sql xmltype extract and single quotes

> Is there a way to have it returned as a single quote?
> Is there a way to do the same process above with XML
> saved in a varchar2 field?

Have a look at the Oracle package DBMS_XMLGEN, the specific subprogram of interest to you is CONVERT

Regards
Hex

<karlman_at_pacbell.net> wrote in message
news:1108192413.730948.40520_at_f14g2000cwb.googlegroups.com...
>I am using PL/SQL and have created a table with a column of XMLTYPE. I
> am able to put XML data in it and extract it into columns in a view.
>
> If XML that has a single quote it, the single quote itself is returned
> as &apos;
>
> Is there a way to have it returned as a single quote?
>
> This is the code I am using.
>
> To insert:
> =====================
> INSERT INTO sa_test (test_id,test_xml)
> VALUES (1,
> sys.xmltype.createxml('<data><fname>Joe''s</fname><lname>Cool</lname></data>'));
>
> To extract:
> =====================
> SELECT
> test_id,t.test_xml.extract('//fname/text()').getstringval() fname ,
> t.test_xml.extract('//lname/text()').getstringval() lname
> FROM sa_test t
>
> OUTPUT
> =====================
> 1 Joe&apos;s Cool
>
>
>
> Second question: Is there a way to do the same process above with XML
> saved in a varchar2 field?
>
>
>
> Thank you!!!
>
> Karl Albrecht
>
Received on Sun Feb 13 2005 - 05:59:15 CST