Khuzi Khuzi - 1 month ago 12
SQL Question

Replace a node in xml data column in DB2 database

I need to replace particular node in xml stored in DB2 database with another node using SQL query.

e.g.

<Data>
<node1>test</node1>
</Data>


After update I need xml as:

<Data>
<node2>test</node2>
</Data>


Please suggest how can this be done.

Thanks!

Answer

I was able to solve this with below query:

update TABLE_NAME
set XML_COLUMN = xmlquery(
  'copy $new := $XML_COLUMN
   modify do rename $new/node1 as "node2" 
   return $new' 
)