Solved

problem using XML extractvalue in PL/SQL

Posted on 2004-08-06
5
5,873 Views
Last Modified: 2012-08-13
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
Comment
  • 2
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 500 total points
ID: 11738696
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 11738709
the problem is that extractvalue() is a SQL function, has not been integrated into PL/SQL engine as of yet.

good luck. slightvw
0
 
LVL 77

Author Comment

by:slightwv (䄆 Netminder)
ID: 11738830
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
 
LVL 2

Expert Comment

by:Elena-S
ID: 11738901
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
 
LVL 77

Author Comment

by:slightwv (䄆 Netminder)
ID: 11739067
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question