EXTRACTVALUE

Syntax:
ExtractValue(xml_frag, xpath_expr)

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 or elements matched by the XPath expression.

Using this function 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, 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:

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

mysql> 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:

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

mysql> SELECT ExtractValue('<a><c/></a>', 'count(/a/b)');
+-------------------------------------+
| ExtractValue('<a><c/></a>', 'count(/a/b)') |
+-------------------------------------+
| 0 |
+-------------------------------------+
1 row in set (0.01 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).

URL: https://dev.mysql.com/doc/refman/8.0/en/xml-functions.html