How to update a child element in an XML document at a given location using the UPDATEXML() function?

UPDATEXML(xml, path, subxml) is a MySQL built-in function that updates a child element in an XML document at a given location. For example:

SET @xml = 'XY';

SELECT UPDATEXML(@xml, '/a', 'Z');
+-----------------------------------+
| UPDATEXML(@xml, '/a', 'Z') |
+-----------------------------------+
| Z                          |
+-----------------------------------+

SELECT UPDATEXML(@xml, '//b', 'Z');
+------------------------------------+
| UPDATEXML(@xml, '//b', 'Z') |
+------------------------------------+
| XY            |
+------------------------------------+

SELECT UPDATEXML(@xml, '//b[2]', 'Z');
+---------------------------------------+
| UPDATEXML(@xml, '//b[2]', 'Z') |
+---------------------------------------+
| XZ               |
+---------------------------------------+

Note that variables used in the location path must be escaped with the '$' prefix. For example,

SET @i =1;

SELECT UPDATEXML(@xml, '//b[$@i]', 'Z');
+-----------------------------------------+
| UPDATEXML(@xml, '//b[$@i]', 'Z') |
+-----------------------------------------+
| ZY                 |
+-----------------------------------------+

Reference information of the UPDATEXML() function:

UPDATEXML(xml, path, subxml): newxml
  Updates a child element in an XML document at a given location.

Arguments, return value and availability:
  xml: Required. The XML document to be updated.
  path: Required. The location path where the existing element is updated.
  subxml: Required. The XML element to replace with.
  newxml: Return value. The updated XML document.
  Available since MySQL 4.0.

⇒ VALUES() - Column Value for "ON DUPLICATE KEY UPDATE"

⇐ SLEEP() - Holding Statement Execution

⇑ MySQL Functions on Miscellaneous Purposes

⇑⇑ MySQL Function References