Solved

problem using XML extractvalue in PL/SQL

Posted on 2004-08-06
5
5,851 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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now