Solved

problem using XML extractvalue in PL/SQL

Posted on 2004-08-06
5
5,905 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

630 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