MariaDB EXTRACTVALUE() Function

In MariaDB, EXTRACTVALUE() is a built-in function that extracts a value from a given XML fragment based on a given XPATH.

MariaDB UPDATEXML() Syntax

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

UPDATEXML(xml_frag, xpath_expr)

Parameters

xml_frag

Required. An XML fragment as a string. 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. The XPATH expression from where the value to be extracted. If this is an illegal XPATH value, MariaDB will report an error: ERROR 1105 (HY000): XPATH syntax error: ')'.

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 'EXTRACTVALUE'.

Return value

The MariaDB UPDATEXML() function returns a string that is a text value at the specified XPATH path from the XML fragment. It only returns the value of the XPATH path, excluding the node.

If the specified XPATH path does not exist in the XML fragment, UPDATEXML() will return an empty string ''.

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

MariaDB UPDATEXML() Examples

First, let’s 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

This example shows basic usages of the MariaDB UPDATEXML() function:

SELECT
  EXTRACTVALUE(@xml, '/user/id') id,
  EXTRACTVALUE(@xml, '/user/name') name,
  EXTRACTVALUE(@xml, '/user/age') age;

Output:

+-----------+-------+------+
| id        | name  | age  |
+-----------+-------+------+
| 123456789 | Alice | 15   |
+-----------+-------+------+

Text value

The MariaDB UPDATEXML() function only returns the text value of the XPATH path, excluding child nodes.

SELECT EXTRACTVALUE(@xml, '/user/hobbies') hobbies;

Output:

+---------+
| hobbies |
+---------+
| Piano   |
+---------+

In this case, the XPath is /user/hobbies, although it contains child nodes Piano<hobby>Football</hobby>, but the function just returns a text value.

This is equivalent to using text() directly in the XPATH expression as follows:

SELECT EXTRACTVALUE(@xml, '/user/hobbies/text()') hobbies;

Output:

+---------+
| hobbies |
+---------+
| Piano   |
+---------+

No match

If the specified XPATH path does not exist in the XML fragment, UPDATEXML() will return an empty string ''.

SELECT EXTRACTVALUE(@xml, '/user/gender');

Output:

+------------------------------------+
| EXTRACTVALUE(@xml, '/user/gender') |
+------------------------------------+
|                                    |
+------------------------------------+

Conclusion

In MariaDB, EXTRACTVALUE() is a built-in function that extracts a value from a given XML fragment based on a given XPATH.