MariaDB UPDATEXML() Function

In MariaDB, UPDATEXML() is a built-in function that replaces a specified portion of the original XML document with a new XML fragment.

MariaDB UPDATEXML() Syntax

Here is the syntax for the MariaDB UPDATEXML() function:

UPDATEXML(xml_doc, xpath_expr)

Parameters

xml_doc

Required. A XML document to be updated. If you provide an invalid XML fragment, MariaDB will issue a warning: Warning (Code 1525): Incorrect XML value: 'parse error at line 1 pos 11: unexpected END-OF-INPUT'.

xpath_expr

Required. An XPATH path expression to be updated. If this is an illegal XPATH value, MariaDB will report an error: ERROR 1105 (HY000): XPATH syntax error: ')'.

new_xml

Required. New XML fragment. If this is an invalid XML fragment, MariaDB will report an error: Warning (Code 1525): Incorrect XML value: 'parse error at line 1 pos 11: unexpected END-OF-INPUT'.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'UPDATEXML'.

Return value

The MariaDB UPDATEXML() function returns the updated XML document that replaced with the content new_xml specified by xpath_expr.

If the specified XPATH path does not exist in the original XML document, UPDATEXML() will return the original XML document.

If either argument is NULL, the UPDATEXML() function will return NULL:

MariaDB UPDATEXML() Examples

Here first define an XML fragment for demonstration:

SET @xml = '<user>
  <id>123456789</id>
  <name>Alice</name>
  <age>15</age>
  <hobbies>Piano<hobby>Football</hobby></hobbies>
</user>';

Basic example

To update the user with the new id 987654321, use the following statement:

SELECT
  UPDATEXML(@xml, '/user/id', '<id>987654321</id>');

Output:

<user>
  <id>987654321</id>
  <name>Alice</name>
  <age>15</age>
  <hobbies>Piano<hobby>Football</hobby></hobbies>
</user>

You can update a node to anything what you want:

SELECT
  UPDATEXML(@xml, '/user/id', '<any>what</any>');

Output:

<user>
  <any>what</any>
  <name>Alice</name>
  <age>15</age>
  <hobbies>Piano<hobby>Football</hobby></hobbies>
</user>

path does not exist

If the specified XPATH path does not exist in the original XML document, UPDATEXML() will return the original XML document.

SELECT
  UPDATEXML(@xml, '/user/nothing', '<any>what</any>');

Output:

<user>
  <id>123456789</id>
  <name>Alice</name>
  <age>15</age>
  <hobbies>Piano<hobby>Football</hobby></hobbies>
</user>

Conclusion

In MariaDB, UPDATEXML() is a built-in function that replaces a specified portion of the original XML document with a new XML fragment.