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)