Oracle-Xmltype:How to Update a Value

Update xml element value in Oracle

Pretty much the same as this post but uglier...

UPDATE myTable
   SET myColumn = updatexml(myColumn ,
                 '/a/*',
                 updatexml(extract(myColumn , '/a/*'),
                           'b/c/text()',
                           'my new value',
                           'xmlns=urn:www.someSite.com/myModel'));

EDIT: If you have more then one b element in a you'll have to change the whole text within a and not for each child, so you can try:

UPDATE myTable
   SET myColumn = updatexml(myColumn ,
                 '/a/text()',
                 updatexml(extract(myColumn , '/a/*'),
                           'b/c/text()',
                           'my new value',
                           'xmlns=urn:www.someSite.com/myModel'));

Updating Oracle XMLType Column Content For a Given Value

this will update all answer nodes for question 2 that has 105.

UPDATE SECTION_ANSWER sa
SET sa.section_answerxml = updatexml(sa.section_answerxml,
'//section[@sectionID="1"]/question[@questionID="2"]/answer[text()="105"]/text()', '205')

or you can update by position

UPDATE SECTION_ANSWER sa
SET sa.section_answerxml = updatexml(sa.section_answerxml,
'//section[@sectionID="1"]/question[@questionID="2"]/answer[position()=3]/text()', '205')

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

Check XMLType node value and update if necessary

How about this? If using in PL*SQL, just use only the function.

  select updatexml(xmltype('<Parameters>
         <Parameter>
          <Index>0</Index>
          <Name>Date</Name>
          <Value>@Today</Value>
         </Parameter>
         <Parameter>
          <Index>1</Index>
          <Name>Id</Name>
          <Value>22</Value>
         </Parameter>
        </Parameters>'), '/Parameters/Parameter/Value[text()="@Today"]', to_char(sysdate, 'dd/mm/yyyy')) 
from dual;

Note that it is a function I call, replace with your own if desiered.

For many different variants, just add one such change after another in the upatexml such as:

  select updatexml(xmltype('<Parameters>
         <Parameter>
          <Index>0</Index>
          <Name>Date</Name>
          <Value>@Today</Value>
         </Parameter>
         <Parameter>
          <Index>1</Index>
          <Name>Id</Name>
          <Value>22</Value>
         </Parameter>
         <Parameter>
          <Index>2</Index>
          <Name>Id</Name>
          <Value>@Yesterday</Value>
         </Parameter>
        </Parameters>'), '/Parameters/Parameter/Value[text()="@Today"]/text()'    , to_char(sysdate    , 'dd/mm/yyyy')
                       , '/Parameters/Parameter/Value[text()="@Yesterday"]/text()', to_char(sysdate - 1, 'dd/mm/yyyy')) 
from dual;

Update clob column with xml value in oracle

Please try the following solution.

Oracle documentation recommends to use XQuery to update XML

db-fiddle

SQL

-- DDL and sample data population, start
CREATE TABLE HOLDS_XML
(xml_col XMLTYPE)
XMLTYPE xml_col STORE AS SECUREFILE BINARY XML;

INSERT INTO HOLDS_XML (xml_col)
VALUES (xmltype('<Employee>
    <Name>John</Name>
    <Age>26</Age>
    <Addresses>
        <Address>
            <Street>Street1</Street>
            <City>Sacramento</City>
            <ZipCode>95813</ZipCode>
        </Address>
        <Address>
            <Street>Street2</Street>
            <City>Los Angeles</City>
            <ZipCode>95300</ZipCode>
        </Address>
    </Addresses>
</Employee>'));
-- DDL and sample data population, end

-- before
select * from  HOLDS_XML;

UPDATE HOLDS_XML
SET xml_col = XMLQuery('copy $i := $p1 
modify(
  replace value of node $i/Employee/Addresses/Address[ZipCode="95813"]/City with $p2,
  replace value of node $i/Employee/Addresses/Address[ZipCode="95813"]/ZipCode with $p3
)
return $i'
PASSING xml_col AS "p1", 'Abcd' AS "p2", '1000' AS "p3"
RETURNING CONTENT)
WHERE XMLExists('$p/Employee/Addresses/Address[ZipCode="95813"]'
PASSING xml_col AS "p"
);

-- after
select * from  HOLDS_XML;