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

x
?
Solved

Oracle Xpath

Posted on 2011-03-02
11
Medium Priority
?
771 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 78

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 78

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 78

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

916 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