Update xml tag in a CLOB column in Oracle

Question

I have this xml value in a CLOB column in Oracle 11g:

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

I want to update the value of InserDate for several rows.

I was using next below sql command:

update tmp_tab_noemail_test p1 
set p1.sce_msg = updatexml(xmltype(p1.sce_msg),
                 '//Energy/InsertDate/text()','Not Valid').getClobVal()

But is not working.

Do you have some ideas to modify only the values of the xml tag of InsertDate?

Thanks in advances

score 5 · Accepted Answer · answered Jun 23 '15 at 18:05

You have a namespace in your top-level Energy node, so you aren't matching without; the UPDATEXML documentation shows you can optionally supply a namespace string.

So you can do this, using your example data:

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();

After which you end up with:

select sce_msg from tmp_tab_noemail_test;

SCE_MSG                                                                         
--------------------------------------------------------------------------------
<Energy xmlns="http://euroconsumers.org/notifications/2009/01/notification"><Gender>M</Gender><FirstName>MAR</FirstName><Name>VAN HALL</Name><Email/><Telephone>000000000</Telephone><InsertDate>Not Valid</InsertDate></Energy>

Or with slightly less scrolling:

select XMLQuery('//*:InsertDate' passing XMLType(sce_msg) returning content) as insertdate
from tmp_tab_noemail_test;

INSERTDATE                                                                      
--------------------------------------------------------------------------------
<InsertDate xmlns="http://euroconsumers.org/notifications/2009/01/notification">Not Valid</InsertDate>

You could also wildcard the update:

update tmp_tab_noemail_test p1 
set p1.sce_msg = updatexml(xmltype(p1.sce_msg),
  '/*:Energy/*:InsertDate/text()','Not Valid').getClobVal();

... but it's probably better to specify the namespace.

@Alex Poole: I have similar requirement, but instead of updating value inside tag, I need to update tag name itself. For example I need to update 'InsertDate' tag to 'insertDate' tag. Any suggestion please? Thanks in Advance. — Sivaram Chintalapudi, Jun 08 '21 at 02:38
@SivaramChintalapudi - you should ask a new question giving all the relevant information. (But look at FLWOR `modify rename node` first maybe.) — Alex Poole, Jun 08 '21 at 07:52

Update xml tag in a CLOB column in Oracle

1 Answers1

Linked