How UPDATEXML() Works in MariaDB

In MariaDB, UPDATEXML() is a built-in function that replaces a single portion of a given fragment of XML markup with a new XML fragment, then returns the updated XML.

Syntax

The syntax goes like this:

UPDATEXML(xml_target, xpath_expr, new_xml)

Where:

  • xml_target is the XML markup that contains the XML fragment you want to update
  • xpath_expr is the specific XML fragment that you want to update
  • new_xml is the XML fragment that you want to replace xpath-expr with

Example

Here’s a basic example:

SELECT
    UPDATEXML(
        '<pet><name>Wag</name><type></type></pet>', 
        '/pet/type', 
        '<type>Dog</type>'
        ) AS Result;

Result:

+---------------------------------------------+
| Result                                      |
+---------------------------------------------+
| <pet><name>Wag</name><type>Dog</type></pet> |
+---------------------------------------------+

In this example, the original XML markup has an empty <type> element. We used UPDATEXML() to update that element, so that it contains content (Dog).

Null Arguments

Passing null for any argument returns null:

SELECT 
    UPDATEXML('<pet></pet>', '/pet', null) AS a,
    UPDATEXML(null, '/pet', '<friend></friend>') AS b,
    UPDATEXML('<pet></pet>', null, '<friend></friend>') AS c,
    UPDATEXML(null, null, null) AS d;

Result:

+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
| NULL | NULL | NULL | NULL |
+------+------+------+------+

Missing Arguments

Calling UPDATEXML() with the wrong number of arguments, or without passing any arguments results in an error:

SELECT UPDATEXML();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'UPDATEXML'