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
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:
After which you end up with:
Or with slightly less scrolling:
You could also wildcard the update:
... but it's probably better to specify the namespace.