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.