problem using XML extractvalue in PL/SQL
Posted on 2004-08-06
I've given up on the RTFM method of solving this issue. I have a PL/SQL block that has been using the xml extract() function. I now have a problem with some of my data due to the XML encoding that is performed for special characters (quote,ampersand,etc...).
The extractvalue function decodes these characters back into there original form but I am unable to get it to work in native PL/SQL and I really don't want to perform "select.....into..... from dual;" in the code each time I wish to extract a decoded version of the text.
Hopefully the follwing test case will show what I mean (I need the PL/SQL block to work for "extractvalue". The 2 select statements are there to demonstrate the difference.
drop table tab1;
create table tab1(col1 xmltype);
insert into tab1 values(xmltype('<DOC>This is a "test"</DOC>'));
prompt Docs say the following 2 statements should be equal. One remains encoded and one does not
select extract(t.col1,'//DOC/text()').getstringval() from tab1 t;
select extractvalue(t.col1,'//DOC') from tab1 t;
for x in (select col1 from tab1) loop
--this works but converts the " to "
-- can't get this to work at all