Solved

Oracle Xpath

Posted on 2011-03-02
11
767 Views
Last Modified: 2012-05-11
Experts... How do I replace the value in an element of xmltype.  ex: I have an xmltype and which has the following structure
<decsn>
    <fs>
      <fstype> 2 </fstype>
      <fscmp>abc</fscmp>
      <arp>3.4</arp>
     </fs>
</decsn>

If I want to replace the element <arp> with a value of 5 after the xml is built how do I do it.
I saw that there isa function UpdateXML in Oracle. Not sure how to use it.
0
Comment
Question by:chickanna
  • 4
  • 4
  • 3
11 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35019643
try this
drop table tab1 purge;
create table tab1(col1 xmltype);

insert into tab1 values(xmltype('<decsn> <fs> <fstype> 2 </fstype> <fscmp>abc</fscmp> <arp>3.4</arp> </fs> </decsn>'));
commit;


UPDATE tab1 SET col1 = UPDATEXML(col1, '/decsn/fs/arp/text()', '5');

select * from tab1;

Open in new window

0
 

Author Comment

by:chickanna
ID: 35019772
slightwv, I have the xml in a variable say v_xml. So I use the same 'UPDATEXML' statement, except in place of col1 use v_xml??
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35019789
just replace the variable name with the column name:

v_xml := UPDATEXML(v_xml, '/decsn/fs/arp/text()', '5');

0
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

 
LVL 74

Expert Comment

by:sdstuber
ID: 35019800
yes, and it's not an update statement,  you simply do an assignment

v_xml := updatexml(v_xml,....
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35019826
Correction:

depending on your version you might have to 'select' it since UPDATEXML is not a PL/SQL function.

see here:

declare
	v_xml xmltype := xmltype('<decsn> <fs> <fstype> 2 </fstype> <fscmp>abc</fscmp> <arp>3.4</arp> </fs> </decsn>');

begin

select UPDATEXML(v_xml, '/decsn/fs/arp/text()', '5') into v_xml from dual;
dbms_output.put_line(v_xml.getclobval());

end;
/

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35019838
ah, good catch

slightwv is correct,  BOTH of our direct assignment posts were wrong.

updatexml isn't available as pl/sql function in any version up through 11gR2.
perhaps someday when 12 comes out but not for now
0
 

Author Comment

by:chickanna
ID: 35019899
Thanks. One last twist in this.. If in the xmltype variable if the element 'arp' does not exist, will the updatexml statement throw an error. I have a situation where the element may not exist.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35019937
>>will the updatexml statement throw an error

No.  

In the future, just try it.  Take my quick simple test case and remove the arp node and try it.
0
 

Author Closing Comment

by:chickanna
ID: 35019950
Thanks a lot.
0
 

Author Comment

by:chickanna
ID: 35020253
slightwv, I created a function where in I pass the xml and am trying to update it within the function. It compiled fine but when I try to run it in an anonymous block I get this error
ORA-30625: method dispatch on NULL SELF argument is disallowed

here is my code

I have this xml in a variable v_xml

I pass it to a function

get_dtls_fn(i_xml xmltype)
return xmltype
IS
v_xml2 xmltype;
begin
select UPDATEXML(i_xml, '/decsn/fs/arp/text()', '5') into v_xml2 from dual;
return v_xml2;
end;

and when I call this function from an anonymous block like

set serveroutput on;
v_clob clob:=null;
v_xml1 xmltype;
begin
-- crdt_aplcn_oid,rte_one_fnc_src_id,crdt_dcsn_oid
v_xml1:=get_dtls_fn(v_xml);  -- v_xml I get if from another function call and this is a valid xml
v_clob := v_xml.getClobVal();
v_num:= 1;
dbms_output.put_line('vxml' || v_clob);
end;

I get the following error ORA-30625: method dispatch on NULL SELF argument is disallowed

what is the solution here
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35020345
I think the problem is somewhere external to the snippet you have posted
if you want to pursue your function that should be a new question
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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