Updating CLOB XML through updatexml

Question

I have one tag in my table having clobe xml data like as below:

I need to update this value to 400. I am doing it as below. But its not updating. Please help.

UPDATE XYZ SET request_xml = UPDATEXML(xmltype(request_xml), 'Parameters/Parameter[@Name="ABC"]/@Value', 400,'xmlns:n0="http://www.iQWE.com/LKJ"').getClobVal() where transaction_id = '2017051907471800000000187725';

Answer 1

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.