Syntax
------ 
EXTRACTVALUE(xml_frag, xpath_expr)
 
Description
----------- 
The EXTRACTVALUE() function takes two string arguments: a
fragment of XML markup and an XPath expression, (also known
as a locator). It returns the text (That is, CDDATA), of the
first text node which is a child of the element or elements
matching the XPath expression. 
 
In cases where a valid XPath expression does not match any
text nodes in a valid XML fragment, (including the implicit
/text() expression), the EXTRACTVALUE() function returns an
empty string.
 
Invalid Arguments
 
When either the XML fragment or the XPath expression is
NULL, the EXTRACTVALUE() function returns NULL. When the XML
fragment is invalid, it raises a warning Code 1525:
 
Warning (Code 1525): Incorrect XML value: 'parse error at
line 1 pos 11: unexpected END-OF-INPUT'
 
When the XPath value is invalid, it generates an Error 1105:
 
ERROR 1105 (HY000): XPATH syntax error: ')'
 
Explicit text() Expressions
 
This function is the equivalent of performing a match using
the XPath expression after appending /text(). In other
words:
 
SELECT
 EXTRACTVALUE('example', '/cases/case') AS 'Base
Example',
 EXTRACTVALUE('example', '/cases/case/text()') AS
'text() Example';
 
+--------------+----------------+
| Base Example | text() Example |
+--------------+----------------+
| example | example |
+--------------+----------------+
 
Count Matches
 
When EXTRACTVALUE() returns multiple matches, it returns the
content of the first child text node of each matching
element, in the matched order, as a single, space-delimited
string.
 
By design, the EXTRACTVALUE() function makes no distinction
between a match on an empty element and no match at all. If
you need to determine whether no matching element was found
in the XML fragment or if an element was found that
contained no child text nodes, use the XPath count()
function. 
 
For instance, when looking for a value that exists, but
contains no child text nodes, you would get a count of the
number of matching instances:
 
SELECT
 EXTRACTVALUE('', '/cases/case') AS 'Empty Example',
 EXTRACTVALUE('', '/cases/case/count()') AS 'count()
Example';
 
+---------------+-----------------+
| Empty Example | count() Example |
+---------------+-----------------+
| | 1 |
+---------------+-----------------+
 
Alternatively, when looking for a value that doesn't exist,
count() returns 0.
 
SELECT
 EXTRACTVALUE('', '/cases/person') AS 'No Match
Example',
 EXTRACTVALUE('', '/cases/person/count()') AS 'count()
Example';
 
+------------------+-----------------+
| No Match Example | count() Example |
+------------------+-----------------+
| | 0|
+------------------+-----------------+
 
Matches
 
Important: The EXTRACTVALUE() function only returns CDDATA.
It does not return tags that the element might contain or
the text that these child elements contain.
 
SELECT EXTRACTVALUE('Personx@example.com', '/cases') AS
Case;
 
+--------+
| Case |
+--------+
| Person |
+--------+
 
Note, in the above example, while the XPath expression
matches to the parent  instance, it does not return the
contained  tag or its content.
 
Examples
-------- 
SELECT
 ExtractValue('cccddd', '/a') AS val1,
 ExtractValue('cccddd', '/a/b') AS val2,
 ExtractValue('cccddd', '//b') AS val3,
 ExtractValue('cccddd', '/b') AS val4,
 ExtractValue('cccdddeee', '//b') AS val5;
 
+------+------+------+------+---------+
| val1 | val2 | val3 | val4 | val5 |
+------+------+------+------+---------+
| ccc | ddd | ddd | | ddd eee |
+------+------+------+------+---------+
 

 
 
 
 
 
 
 

URL: https://mariadb.com/kb/en/extractvalue/