用户变量（弱检查）。 使用语法 $@variable_name（即用户变量）的变量不会被检查。如果变量类型错误或之前未被赋值，服务器不会发出任何警告或错误。这也意味着用户需自行承担任何拼写错误的责任，因为如果使用了 $@myvairable 而原本意图是 $@myvariable，不会发出任何警告。User variables (weak checking).  Variables using the syntax $@variable_name (that is, user variables) are not checked. No warnings or errors are issued by the server if a variable has the wrong type or has previously not been assigned a value. This also means the user is fully responsible for any typographical errors, since no warnings are given if (for example) $@myvairable is used where $@myvariable was intended.

示例：Example:

mysql> SET @xml = '<a><b>X</b><b>Y</b></a>';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @i =1, @j = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @i, ExtractValue(@xml, '//b[$@i]');
+------+--------------------------------+
| @i   | ExtractValue(@xml, '//b[$@i]') |
+------+--------------------------------+
|    1 | X                              |
+------+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @j, ExtractValue(@xml, '//b[$@j]');
+------+--------------------------------+
| @j   | ExtractValue(@xml, '//b[$@j]') |
+------+--------------------------------+
|    2 | Y                              |
+------+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @k, ExtractValue(@xml, '//b[$@k]');
+------+--------------------------------+
| @k   | ExtractValue(@xml, '//b[$@k]') |
+------+--------------------------------+
| NULL |                                |
+------+--------------------------------+
1 row in set (0.00 sec)

存储程序中的变量（强类型检查）。 使用语法 $variable_name 的变量可以在这些函数在存储程序内部调用时声明和使用。此类变量仅在定义它们的存储程序中是局部的，并且会进行类型和值的严格检查。Variables in stored programs (strong checking).  Variables using the syntax $variable_name can be declared and used with these functions when they are called inside stored programs. Such variables are local to the stored program in which they are defined, and are strongly checked for type and value.

示例：Example:

mysql> DELIMITER |

mysql> CREATE PROCEDURE myproc ()
    -> BEGIN
    ->   DECLARE i INT DEFAULT 1;
    ->   DECLARE xml VARCHAR(25) DEFAULT '<a>X</a><a>Y</a><a>Z</a>';
    ->
    ->   WHILE i < 4 DO
    ->     SELECT xml, i, ExtractValue(xml, '//a[$i]');
    ->     SET i = i+1;
    ->   END WHILE;
    -> END |
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

mysql> CALL myproc();
+--------------------------+---+------------------------------+
| xml                      | i | ExtractValue(xml, '//a[$i]') |
+--------------------------+---+------------------------------+
| <a>X</a><a>Y</a><a>Z</a> | 1 | X                            |
+--------------------------+---+------------------------------+
1 row in set (0.00 sec)

+--------------------------+---+------------------------------+
| xml                      | i | ExtractValue(xml, '//a[$i]') |
+--------------------------+---+------------------------------+
| <a>X</a><a>Y</a><a>Z</a> | 2 | Y                            |
+--------------------------+---+------------------------------+
1 row in set (0.01 sec)

+--------------------------+---+------------------------------+
| xml                      | i | ExtractValue(xml, '//a[$i]') |
+--------------------------+---+------------------------------+
| <a>X</a><a>Y</a><a>Z</a> | 3 | Z                            |
+--------------------------+---+------------------------------+
1 row in set (0.01 sec)

参数。 在存储例程中作为参数传入的、用于 XPath 表达式的变量也需要进行严格检查。Parameters.  Variables used in XPath expressions inside stored routines that are passed in as parameters are also subject to strong checking.

ExtractValue(xml_frag, xpath_expr)

ExtractValue() 接受两个字符串参数，一个 XML 标记片段 xml_frag 和一个 XPath 表达式 xpath_expr（也称为 定位器）；它返回由 XPath 表达式匹配的元素的第一个子文本节点的文本 (CDATA)。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.

使用此函数等同于在附加了 /text() 之后使用 xpath_expr 进行匹配。换句话说，ExtractValue('<a><b>Sakila</b></a>', '/a/b') 和 ExtractValue('<a><b>Sakila</b></a>', '/a/b/text()') 产生相同的结果。如果 xml_frag 或 xpath_expr 为 NULL，该函数返回 NULL。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 xml_frag or xpath_expr is NULL, the function returns NULL.

如果找到多个匹配项，则返回每个匹配元素的第一个子文本节点的内容（按匹配顺序）作为单个以空格分隔的字符串。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.

如果没有找到与表达式匹配的文本节点（包括隐式的 /text()）——无论出于何种原因，只要 xpath_expr 是有效的，并且 xml_frag 由正确嵌套和闭合的元素组成——则返回空字符串。对于空元素的匹配和根本没有匹配，不会做区分。这是设计使然。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.

如果您需要确定在 xml_frag 中是否未找到匹配的元素，或者找到了这样的元素但其中不包含子文本节点，您应该测试使用 XPath count() 函数的表达式的结果。例如，这两个语句都返回一个空字符串，如下所示：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)

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

该函数使用当前的 SQL 排序规则来与 contains() 进行比较，执行与其他字符串函数（例如 CONCAT()）相同的排序规则聚合，同时考虑其参数的排序规则强制性；有关控制此行为规则的解释，请参见 第 12.8.4 节，“表达式中的排序规则强制性”。This function uses the current SQL collation for making comparisons with contains(), performing the same collation aggregation as other string functions (such as CONCAT()), in taking into account the collation coercibility of their arguments; see Section 12.8.4, “Collation Coercibility in Expressions”, for an explanation of the rules governing this behavior.

（以前，总是使用二进制——即区分大小写——比较。）(Previously, binary—that is, case-sensitive—comparison was always used.)

NULL 会被返回，如果 xml_frag 包含未正确嵌套或未闭合的元素，并且会生成一个警告，如下例所示：NULL is returned if xml_frag contains elements which are not properly nested or closed, and a warning is generated, as shown in this example:

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

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1525
Message: Incorrect XML value: 'parse error at line 1 pos 11:
         END-OF-INPUT unexpected ('>' wanted)'
1 row in set (0.00 sec)

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

UpdateXML(xml_target, xpath_expr, new_xml)

此函数用一个新的 XML 片段 new_xml 替换给定 XML 片段 xml_target 的单个部分，然后返回更改后的 XML。被替换的 xml_target 部分匹配用户提供的 XPath 表达式 xpath_expr。This function replaces a single 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.

如果没有找到与 xpath_expr 匹配的表达式，或者找到多个匹配项，则函数返回原始的 xml_target XML 片段。三个参数都应为字符串。如果 UpdateXML() 的任何参数为 NULL，函数将返回 NULL。If no expression matching xpath_expr is found, or if multiple matches are found, the function returns the original xml_target XML fragment. All three arguments should be strings. If any of the arguments to UpdateXML() are NULL, the function returns NULL.

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,
    ->   UpdateXML('<a><d></d><b>ccc</b><d></d></a>', '/a/d', '<e>fff</e>') AS val5
    -> \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>
val5: <a><d></d><b>ccc</b><d></d></a>

/tag

仅当 <tag/> 是根元素时才匹配 <tag/>。Matches <tag/> if and only if <tag/> is the root element.

示例：/a 在 <a><b/></a> 中有匹配，因为它匹配最外层（根）标签。在 <b><a/></b> 中，它不会匹配内层 a 元素，因为在这种情况下它是另一个元素的子元素。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

当且仅当它是<tag2/>的子项时<tag1/>，以及<tag1/>是根元素。Matches <tag2/> if and only if it is a child of <tag1/>, and <tag1/> is the root element.

示例：/a/b 匹配 XML 片段 <a><b/></a> 中的 b 元素，因为它是根元素 a 的子元素。在 <b><a/></b> 中没有匹配，因为在这种情况下，b 是根元素（因此不是任何其他元素的子元素）。XPath 表达式在 <a><c><b/></c></a> 中也没有匹配；在这里，b 是 a 的后代，但实际上不是 a 的子元素。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.

这个结构可以扩展到三个或更多元素。例如，XPath 表达式 /a/b/c 匹配片段 <a><b><c/></b></a> 中的 c 元素。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

匹配任何 <tag> 的实例。Matches any instance of <tag>.

示例：//a 匹配以下任意情况中的 a 元素：<a><b><c/></b></a>；<c><a><b/></a></b>；<c><b><a/></b></c>。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>.

// 可以与 / 结合使用。例如，//a/b 匹配两个片段中的 b 元素：<a><b/></a> 或 <c><a><b/></a></c>。// can be combined with /. For example, //a/b matches the b element in either of the fragments <a><b/></a> or <c><a><b/></a></c>.

mysql> SET @xml = '<a><b><c>w</c><b>x</b><d>y</d>z</b></a>';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @xml;
+-----------------------------------------+
| @xml                                    |
+-----------------------------------------+
| <a><b><c>w</c><b>x</b><d>y</d>z</b></a> |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ExtractValue(@xml, '//b[1]');
+------------------------------+
| ExtractValue(@xml, '//b[1]') |
+------------------------------+
| x z                          |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ExtractValue(@xml, '//b[2]');
+------------------------------+
| ExtractValue(@xml, '//b[2]') |
+------------------------------+
|                              |
+------------------------------+
1 row in set (0.01 sec)

mysql> SELECT ExtractValue(@xml, '/descendant-or-self::*/b[1]');
+---------------------------------------------------+
| ExtractValue(@xml, '/descendant-or-self::*/b[1]') |
+---------------------------------------------------+
| x z                                               |
+---------------------------------------------------+
1 row in set (0.06 sec)

mysql> SELECT ExtractValue(@xml, '/descendant-or-self::*/b[2]');
+---------------------------------------------------+
| ExtractValue(@xml, '/descendant-or-self::*/b[2]') |
+---------------------------------------------------+
|                                                   |
+---------------------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT ExtractValue(@xml, '/descendant-or-self::b[1]');
+-------------------------------------------------+
| ExtractValue(@xml, '/descendant-or-self::b[1]') |
+-------------------------------------------------+
| z                                               |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ExtractValue(@xml, '/descendant-or-self::b[2]');
+-------------------------------------------------+
| ExtractValue(@xml, '/descendant-or-self::b[2]') |
+-------------------------------------------------+
| x                                               |
+-------------------------------------------------+
1 row in set (0.00 sec)

* 运算符充当一个 “通配符”，可以匹配任何元素。例如，表达式 /*/b 匹配 XML 片段 <a><b/></a> 或 <c><b/></c> 中的 b 元素。然而，该表达式在片段 <b><a/></b> 中不会匹配，因为 b 必须是其他元素的子元素。通配符可以用于任何位置：表达式 /*/b/* 匹配任何不是根元素的 b 元素的子元素。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/* matches any child of a b element that is itself not the root element.

你可以使用 | (UNION) 运算符匹配任意多个定位器。例如，表达式 //b|//c 会匹配 XML 目标中的所有 b 和 c 元素。You can match any of several locators using the | (UNION) operator. For example, the expression //b|//c matches all b and c elements in the XML target.

也可以根据一个或多个属性的值来匹配元素。这可以使用语法 tag[@attribute="value"] 来完成。例如，表达式 //b[@id="idB"] 会匹配片段 b 中的第二个 <a><b id="idA"/><c/><b id="idB"/></a> 元素。要匹配具有 任意 属性值为 attribute="value" 的元素，可以使用 XPath 表达式 //*[attribute="value"]。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"].

要筛选多个属性值，只需连续使用多个属性比较子句。例如，表达式 //b[@c="x"][@d="y"] 匹配出现在给定 XML 片段中的元素 <b c="x" d="y"/>。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.

要查找同一属性匹配多个值的元素，可以使用由 | 运算符连接的多个定位器。例如，要匹配所有 b 元素，其 c 属性的值为 23 或 17，可以使用表达式 //b[@c="23"]|//b[@c="17"]。你也可以使用逻辑 or 运算符来实现此目的：//b[@c="23" or @c="17"]。To find elements for which the same attribute matches any of several values, you can 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"]. You can also use the logical or operator for this purpose: //b[@c="23" or @c="17"].

不支持节点集到节点集的比较（例如 '/a/b[@c=@d]'）。Nodeset-to-nodeset comparison (such as '/a/b[@c=@d]') is not supported.

支持所有标准的 XPath 比较运算符。（错误 #22823）All of the standard XPath comparison operators are supported. (Bug #22823)

相对定位器表达式是在根节点的上下文中解析的。例如，考虑以下查询和结果：Relative locator expressions are resolved in the context of the root node. For example, consider the following query and result:

mysql> SELECT ExtractValue(
    ->   '<a><b c="1">X</b><b c="2">Y</b></a>',
    ->    'a/b'
    -> ) AS result;
+--------+
| result |
+--------+
| X Y    |
+--------+
1 row in set (0.03 sec)

在这种情况下，定位器 a/b 解析为 /a/b。In this case, the locator a/b resolves to /a/b.

相对定位器在谓词中也受支持。在以下示例中，d[../@c="1"] 被解析为 /a/b[@c="1"]/d：Relative locators are also supported within predicates. In the following example, d[../@c="1"] is resolved as /a/b[@c="1"]/d:

mysql> SELECT ExtractValue(
    ->      '<a>
    ->        <b c="1"><d>X</d></b>
    ->        <b c="2"><d>X</d></b>
    ->      </a>',
    ->      'a/b/d[../@c="1"]')
    -> AS result;
+--------+
| result |
+--------+
| X      |
+--------+
1 row in set (0.00 sec)

带有求值为标量值的表达式前缀的定位器——包括变量引用、字面量、数字和标量函数调用——是不允许的，使用它们会导致错误。Locators prefixed with expressions that evaluate as scalar values—including variable references, literals, numbers, and scalar function calls—are not permitted, and their use results in an error.

:: 运算符不支持与以下节点类型组合使用：The :: operator is not supported in combination with node types such as the following:

然而，名称测试（例如 axis::name 和 axis::*）是支持的，如以下示例所示：However, name tests (such as axis::name and axis::*) are supported, as shown in these examples:

mysql> SELECT ExtractValue('<a><b>x</b><c>y</c></a>','/a/child::b');
+-------------------------------------------------------+
| ExtractValue('<a><b>x</b><c>y</c></a>','/a/child::b') |
+-------------------------------------------------------+
| x                                                     |
+-------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT ExtractValue('<a><b>x</b><c>y</c></a>','/a/child::*');
+-------------------------------------------------------+
| ExtractValue('<a><b>x</b><c>y</c></a>','/a/child::*') |
+-------------------------------------------------------+
| x y                                                   |
+-------------------------------------------------------+
1 row in set (0.01 sec)

“上下”导航在路径会导致“超出”根元素的情况下不被支持。也就是说，你不能使用匹配给定元素的祖先的子孙的表达式，其中当前元素的一个或多个祖先也是根元素的祖先（见 Bug #16321）。“Up-and-down” navigation is not supported in cases where the path would lead “above” the root element. That is, you cannot use expressions which match on descendants of ancestors of a given element, where one or more of the ancestors of the current element is also an ancestor of the root element (see Bug #16321).

以下 XPath 函数不受支持，或存在已知问题，如所示：The following XPath functions are not supported, or have known issues as indicated:

以下轴不受支持：The following axes are not supported:

- 永远不要在应用程序中接受未经测试的用户数据。Never accepted untested data from users in your application.

- 检查所有用户提交的数据类型；拒绝或转换类型错误的数据。Check all user-submitted data for type; reject or convert data that is of the wrong type

- 测试数值数据是否超出范围；截断、四舍五入或拒绝超出范围的值。测试字符串是否含有非法字符，并将其去掉或拒绝包含非法字符的输入。Test numeric data for out of range values; truncate, round, or reject values that are out of range. Test strings for illegal characters and either strip them out or reject input containing them.

- 不要输出可能为未授权用户提供可用于破坏系统线索的明确错误信息；将这些错误记录到文件或表中。Do not output explicit error messages that might provide an unauthorized user with clues that could be used to compromise the system; log these to a file or database table instead.

始终测试输出数据的类型和允许的值。Always test outgoing data for type and permissible values.

绝不允许未经授权的用户查看可能提供有关应用程序信息的错误信息，这些信息可能被用于利用应用程序。Never permit unauthorized users to view error messages that might provide information about the application that could be used to exploit it.