Update Xml Tag in a Clob Column in Oracle

Update xml tag in a CLOB column in Oracle

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.

How to update XML attribute in clob Oracle using XMLQuery

Use getclobval() like this:

UPDATE SR_DATA
  SET XMLDATA = 
     XMLTYPE.GETCLOBVAL(XMLQuery('copy $i := $p1 modify
                            ((for $j in $i/module/tmEsObjective/@modelCodeValue
                              return replace value of node $j with $p2))  
                            
                            return $i'
                         PASSING XMLType(REPLACE(xmldata, 'xmlns="http://www.mytest.com/2008/FMSchema"', '')) AS "p1",
                                 'ES_B' AS "p2"
                         RETURNING CONTENT ));

Update xml tag in Oracle - joining another table

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;

Updating CLOB XML through updatexml

Your XPath doesn't make much sense. You are looking for a node called Parameter with an attribute called Name, where that attribute value is 'MaxLatenessAllowed'. There is nothing like that in your XMl document.

You can supply the full path to the node you want to change, including namespace info:

UPDATE dfxha_catchup_queue
SET request_xml = UPDATEXML(xmltype(request_xml),
   '/n0:CreateOrder/n0:SalesOrders/n0:SalesOrder/n0:SalesOrderLines/n0:SalesOrderLine/n0:MaxLatenessAllowed/@Value',
    400,
    'xmlns:n0="http://www.i2.com/DFX"').getClobVal()
where transaction_id = '2017051907471800000000187725';

Or to shorten that you can look for that node name anywhere, if that's safe in your schema:

UPDATE dfxha_catchup_queue
SET request_xml = UPDATEXML(xmltype(request_xml),
    '//n0:MaxLatenessAllowed/@Value',
    400,
    'xmlns:n0="http://www.i2.com/DFX"').getClobVal()
where transaction_id = '2017051907471800000000187725';

The updateXML() function is deprecated, so you might want to investigate other ways of achieving this.

---

Related Topics