how to update a column containing xml in mysql table?

Question

I have a table with columns ID, XML where the XML column contains xml string. The xml is in the flowing form:

<list>
  <element>
    <key>key</key>
    <value>value</value>
  </element>
...
</list>

I need to find and remove all the elements in the xml that contain a certain SPECIFIC_XXX_VALUE for example:

<element>
   <key>SPECIFIC_XXX_VALUE</key>
   <value>ANY_VALUE</value>
</element>

This elements should be removed from the xml for all the rows in the table.

Answer 1

Update TABLE set XML = UpdateXML(XML, '//element[key="valueOfKey"]','') where "%valueOfKey%";