Update xml tag in Oracle - joining another table

Question

I found the following link very useful, but I would like to ask further to it

Update xml tag in a CLOB column in Oracle

Using the same data in previous post:

create table tmp_tab_noemail_test (sce_msg clob);
insert into tmp_tab_noemail_test values (
'<Energy xmlns="http://euroconsumers.org/notifications/2009/01/notification">    
    <Gender>M</Gender>
    <FirstName>MAR</FirstName>
    <Name>VAN HALL</Name>
    <Email/><Telephone>000000000</Telephone>
    <InsertDate>2013-10-09</InsertDate>
</Energy>');

update tmp_tab_noemail_test p1 
set p1.sce_msg = updatexml(xmltype(p1.sce_msg),
  '/Energy/InsertDate/text()','Not Valid',
  'xmlns="http://euroconsumers.org/notifications/2009/01/notification"').getClobVal();

Now, what if I wanna look up to another table Account. It has column: acct_num, name, date_of_birth

How can I update InsertDate tag value = Account.date_of_birth where Name tag value = Account.name?

Is it possible to do? Thanks!

score 0 · Accepted Answer · answered Sep 15 '17 at 08:05

If I correctly understood the question, then you can extract Name and rowid from tmp_tab_noemail_test, join them with Account table, get updated xml and using merge (by rowid) update in destination folder -

create table Account (acct_num varchar2(20), name varchar2(255), date_of_birth date);

insert into Account
values(123, 'VAN HALL', sysdate);
commit;

merge into tmp_tab_noemail_test t
using
(
select rid, updatexml(xmltype(sce_msg),
        '/Energy/InsertDate/text()', to_char(date_of_birth, 'yyyy-mm-dd'),
        'xmlns="http://euroconsumers.org/notifications/2009/01/notification"').getClobVal() as sce_msg
 from
   (select t.sce_msg, t.rid, t.name, a.date_of_birth
     from
       (select sce_msg, extractvalue(xmltype(sce_msg), 
                '/Energy/Name', 
                'xmlns="http://euroconsumers.org/notifications/2009/01/notification"') as name,
                rowid as rid
        from tmp_tab_noemail_test) t, Account a
    where t.name = a.name)
) s
ON (t.ROWID = s.RID)
WHEN MATCHED THEN
UPDATE SET 
  T.sce_msg = S.sce_msg;

Update xml tag in Oracle - joining another table

1 Answers1