pl/sql xmltype extract and single quotes

karlman · *nix forums beginner Joined: 14 Feb 2005 Posts: 1

Works perfectly! Thank you!

Hexathioorthooxalate · *nix forums beginner Joined: 31 May 2005 Posts: 36

karlman@pacbell.net · *nix forums beginner Joined: 12 Feb 2005 Posts: 1

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 '

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'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

Google