UpdateXML ( Set value to a tag )

UPDATEXML takes as arguments an XMLType instance and an XPath-value pair and returns an XMLType instance with the updated value.

Syntax:
updatexml(Myxml, SearchString,NewValue)

SearchString => XMLPath

Example:

select empxml from
(select xmltype('
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <empNo>1</empNo>
    <ename>Kiran</ename>
    <dept>10</dept>
    <salary> 1000 </salary>
 </ROW>
  <ROW>
    <empNo>2</empNo>
    <ename>Vishali</ename>
    <dept>10</dept>
    <salary> 1500 </salary>
  </ROW>
</ROWSET>') empxml from dual);


-- Update Salary on empNo=2 and dept =10 to 2000

select updatexml(empxml,'/ROWSET/ROW[empNo="2" and dept="10"]/salary/text()', '2000')
from
(select xmltype('<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <empNo>1</empNo>
  <ename>Kiran</ename>
  <dept>10</dept>
  <salary> 1000 </salary>
 </ROW>
 <ROW>
  <empNo>2</empNo>
  <ename>Vishali</ename>
  <dept>10</dept>
  <salary> 1500 </salary>
 </ROW>
</ROWSET>
') empxml from dual);

-- Update XML on Null Tag (empty tag : <tag/>)

Tag need to be replaced along with the value : '<tag> NewValue </tag>'

On non-empty tags we just replace text() with the NewValue.

select updatexml(empxml,'/ROWSET/ROW[empNo="3" and dept="20"]/salary', '<salary> 3000 </salary>')
from
(select xmltype('<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <empNo>1</empNo>
  <ename>Kiran</ename>
  <dept>10</dept>
  <salary> 1000 </salary>
 </ROW>
 <ROW>
  <empNo>2</empNo>
  <ename>Vishali</ename>
  <dept>10</dept>
  <salary> 1500 </salary>
 </ROW>
 <ROW>
  <empNo>3</empNo>
  <ename> KiVi </ename>
  <dept>20</dept>
  <salary/>
 </ROW>
</ROWSET>
') empxml from dual);