?
Solved

Oracle Xpath

Posted on 2011-03-02
11
Medium Priority
?
769 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
[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
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

770 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