Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5943
  • Last Modified:

problem using XML extractvalue in PL/SQL

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>'));
commit;

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;


begin
      for x in (select col1 from tab1) loop
            --this works but converts the " to &quot;
            --dbms_output.put_line(x.col1.extract('//DOC/text()').getstringval());

            -- can't get this to work at all
            dbms_output.put_line(extractvalue(x.col1,'//DOC'));
      end loop;

end;
/

0
slightwv (䄆 Netminder)
Asked:
slightwv (䄆 Netminder)
  • 2
  • 2
1 Solution
 
seazodiacCommented:
Here is a workaround:


begin
    for x in (select extractvalue(col1, '//DOC') val from tab1) loop
         -- can't get this to work at all
         dbms_output.put_line(x.val);
    end loop;

end;
/
0
 
seazodiacCommented:
the problem is that extractvalue() is a SQL function, has not been integrated into PL/SQL engine as of yet.

good luck. slightvw
0
 
slightwv (䄆 Netminder) Author Commented:
Thx for the update.

Leave it to Oracle to integrate extract and not extractvalue (That's what I sort of figured.  Just wanted confirmation.).  I really wasn't looking forward to the 'select...into...from dual;' method.  I have a lot of values that I pull out of the XML and store relationaly (the provided loop was a very simplified test case).

I'll leave this open over the weekend and award the points on Monday (I have to have this completed on Monday).  I'm hoping for the 'smoke and mirrors' solution.
0
 
Elena-SCommented:
You could try something like this:

begin
   for x in (select col1 from tab1) loop
      dbms_output.put_line(replace(x.col1.extract('//DOC/text()').getstringval(), '&quot;', '"'));
   end loop;
end;
/

Basically, if you know how XML encoding is performed for special characters, you can replace them with originals.  On the other hand, performing these extracts in the cursors may be a cleaner solution.

Hope this helps.
0
 
slightwv (䄆 Netminder) Author Commented:
Elena-S,  thanks for the post.  

Your soultion would work if I only had to worry about that 1 character.  To do this in reality would involve chaining something like 15 replace calls together (I'm thinking that there are around 10-15 characters that XML encodes).  At that point the 'select into' soultion would be much more efficient.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now