ExtractValue(xml_frag, xpath_expr)

This function takes two string arguments, a fragment of XML markup xml_frag and an XPath expression xpath_expr (also known as a locator), and returns the value matched by xpath_expr. Note that ExtractValue() returns only the CDATA that is contained directly by the tag matching xpath_expr, and does not return any tags that might be contained within the matching tag, nor any of their content (see the result returned as val1 in the following example).

If no match for xpath_expr is found, this function returns an empty string. If multiple matches are found, the contents of all matching elements are returned (in the order matched) as a single, space-delimited string.

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

UpdateXML(xml_target, xpath_expr, new_xml)

This function replaces a portion of a given fragment of XML markup xml_target with a new XML fragment new_xml and then returns the changed XML. The portion of xml_target that is replaced matches an XPath expression xpath_expr supplied by the user. If no expression matching xpath_expr is found, the function returns the original xml_target XML fragment. All three arguments must be strings.

mysql> SELECT
    ->   UpdateXML('<a><b>ccc</b><d></d></a>', '/a', '<e>fff</e>') AS val1,
    ->   UpdateXML('<a><b>ccc</b><d></d></a>', '/b', '<e>fff</e>') AS val2,
    ->   UpdateXML('<a><b>ccc</b><d></d></a>', '//b', '<e>fff</e>') AS val3,
    ->   UpdateXML('<a><b>ccc</b><d></d></a>', '/a/d', '<e>fff</e>') AS val4
    -> \G

*************************** 1. row ***************************
val1: <e>fff</e>
val2: <a><b>ccc</b><d></d></a>
val3: <a><e>fff</e><d></d></a>
val4: <a><b>ccc</b><e>fff</e></a>

/tag

Matches <tag/> if and only if <tag/> is the root element.

Example: /a has a match in <a><b/></a> because it matches the outermost (root) tag. It does not match the inner a element in <b><a/></b> because in this instance it is the child of another element.

/tag1/tag2

Matches <tag2/> if and only if it is a child of <tag1/>, and <tag1/> is the root element.

Example: /a/b matches the b element in the XML fragment <a><b/></a> because it is a child of the root element a. It does not have a match in <b><a/></b> because in this case, b is the root element (and hence the child of no other element). Nor does the XPath expression have a match in <a><c><b/></c></a>; here, b is a descendant of a, but not actually a child of a.

This construct is extendable to three or more elements. For example, the XPath expression /a/b/c matches the c element in the fragment <a><b><c/></b></a>.

//tag

Matches any instance of tag.

Example: //a matches the a element in any of the following: <a><b><c/></b></a>; <c><a><b/></a></b>; <c><b><a/></b></c>.

// can be combined with /. For example, //a/b matches the b element in either of the fragments <a><b/></a> or <a><b><c/></b></a>

The * operator acts as a “wildcard” that matches any element. For example, the expression /*/b matches the b element in either of the XML fragments <a><b/></a> or <c><b/></c>. However, the expression does not produce a match in the fragment <b><a/></b> because b must be a child of some other element. The wildcard may be used in any position: The expression /*/b/* will match any child of a b element that is itself not the root element.

Multiple locators may be matched using the | (logical OR) operator. For example, the expression //b|//c matches all b and c elements in the XML target.

It is also possible to match an element based on the value of one or more of its attributes. This done using the syntax tag[@attribute="value"]. For example, the expression //b[@id="idB"] matches the second b element in the fragment <a><b id="idA"/><c/><b id="idB"/></a>. To match against any element having attribute="value", use the XPath expression //*[attribute="value"].

To filter multiple attribute values, simply use multiple attribute-comparison clauses in succession. For example, the expression //b[@c="x"][@d="y"] matches the element <b c="x" d="y"/> occurring anywhere in a given XML fragment.

To find elements for which the same attribute matches one of several values, you must use multiple locators joined by the | operator. For example, to match all b elements whose c attributes have either of the values 23 or 17, use the expression //b[@c="23"]|b[@c="17"].

Nodeset-to-nodeset comparison (such as '/a/b[@c=@d]') is not supported. Only comparisons of the form [@attribute="const"], where const is a constant value, are currently possible. Note that equality and inequality (= and (!=)) are the only supported comparison operators.

Relative locator expressions are not supported. XPath expressions must begin with / or //.

The :: operator is not supported.

The following XPath functions are not supported:

The following axes are not supported: