pl/sql xmltype extract and single quotes

From: <karlman_at_pacbell.net>
Date: 11 Feb 2005 23:13:33 -0800
Message-ID: <1108192413.730948.40520@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 Sat Feb 12 2005 - 01:13:33 CST