Solved

Oracle Xpath

Posted on 2011-03-02
11
761 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 76

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 76

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
 
LVL 73

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 76

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
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.

 
LVL 73

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 76

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 73

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

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

759 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

21 Experts available now in Live!

Get 1:1 Help Now