EXTRACTVALUE

Syntax:

ExtractValue(xml_frag, xpath_expr)

Description:

ExtractValue() takes two string arguments, a fragment of XML markup xml_frag and an XPath expression xpath_expr (also known as a locator); it returns the text (CDATA) of the first text node which is a child of the element(s) matched by the XPath expression. It is the equivalent of performing a match using the xpath_expr after appending /text(). In other words,

ExtractValue('<a><b>Sakila</b></a>', '/a/b')

and

ExtractValue('<a><b>Sakila</b></a>', '/a/b/text()')

produce the same result.

If multiple matches are found, then the content of the first child text node of each matching element is returned (in the order matched) as a single, space-delimited string.

If no matching text node is found for the expression (including the implicit /text()) — for whatever reason, as long as xpath_expr is valid, and xml_frag consists of elements which are properly nested and closed — an empty string is returned. No distinction is made between a match on an empty element and no match at all. This is by design.

If you need to determine whether no matching element was found in xml_frag or such an element was found but contained no child text nodes, you should test the result of an expression that uses the XPath count() function. For example, both of these statements return an empty string, as shown here:

MariaDB [(none)]> SELECT ExtractValue('<a><b/></a>', '/a/b');
+-------------------------------------+
| ExtractValue('<a><b/></a>', '/a/b') |
+-------------------------------------+
|                                     |
+-------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT ExtractValue('<a><c/></a>', '/a/b');
+-------------------------------------+
| ExtractValue('<a><c/></a>', '/a/b') |
+-------------------------------------+
|                                     |
+-------------------------------------+
1 row in set (0.00 sec)

However, you can determine whether there was actually a matching element using the following:

MariaDB [(none)]> SELECT ExtractValue('<a><b/></a>', 'count(/a/b)');
+--------------------------------------------+
| ExtractValue('<a><b/></a>', 'count(/a/b)') |
+--------------------------------------------+
| 1                                          |
+--------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT ExtractValue('<a><c/></a>', 'count(/a/b)');
+--------------------------------------------+
| ExtractValue('<a><c/></a>', 'count(/a/b)') |
+--------------------------------------------+
| 0                                          |
+--------------------------------------------+
1 row in set (0.00 sec)

Important: ExtractValue() returns only CDATA, and does not return any tags that might be contained within a matching tag, nor any of their content (see the result returned as val1 in the following example).

Examples:

MariaDB [(none)]> SELECT
    -> ExtractValue('<a>ccc<b>ddd</b></a>', '/a')            AS val1,
    -> ExtractValue('<a>ccc<b>ddd</b></a>', '/a/b')          AS val2,
    -> ExtractValue('<a>ccc<b>ddd</b></a>', '//b')           AS val3,
    -> ExtractValue('<a>ccc<b>ddd</b></a>', '/b')            AS val4,
    -> ExtractValue('<a>ccc<b>ddd</b><b>eee</b></a>', '//b') AS val5;
+------+------+------+------+---------+
| val1 | val2 | val3 | val4 | val5    |
+------+------+------+------+---------+
| ccc  | ddd  | ddd  |      | ddd eee |
+------+------+------+------+---------+
1 row in set (0.00 sec)