游子的博客
慈母手中线,游子身上衣, 临行密密缝,意恐迟迟归, 谁言寸草心,报得三春晖。 数据读取中,请稍候......
posts - 337,  comments - 546,  trackbacks - 0

12.9. XML Functions

This section discusses XML and related functionality in MySQL.

Note that it is possible to obtain XML-formatted output from MySQL in the mysql and mysqldump clients by invoking them with the --xml option. See Section 8.6, “mysql — The MySQL Command-Line Tool”, and Section 8.11, “mysqldump — A Database Backup Program”.

//注意:你可以通过带-XML的MYSQL命令行来获得XML格式的输出,请参照上述链接。

Beginning with MySQL 5.1.5, two functions providing basic XPath (XML Path Language) capabilities are available.

Note that these functions remain under development. We continue to improve these and other aspects of XML and XPath functionality in MySQL 5.1 and onwards. You may discuss these, ask questions about them, and obtain help from other users with them in the MySQL XML User Forum.
//从5.15开始,MYSQL提供了两个函数用于基本的XPATH操作。
//这些函数仍在发展,您可以通过上述链接获得最新帮助。

  • 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(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.
    //入口参数:xml_frag,就是一个xml文档。
    //xpath_expr,是要查找的路径表达式。加不加/text(),返回都是一样的。
    //返回:元素的值

    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 (augmented) expression — for whatever reason, as long as xpth_expr is valid, and xml_frag is well-formed — 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)
    //如果你要区别上述情况,请用count()函数。

    Note that 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).
    //函数只返回CDATA,不返回任何标记和他们中间的内容。

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

    Beginning with MySQL 5.1.8, this function uses the current SQL collation for making comparisons with contains(). (Previously, binary — that is, case-sensitive — comparison was always used.)
    //从5.1.8开始,这个函数...?

  • UpdateXML(xml_target, xpath_expr, new_xml)

    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. 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 must be strings.
    //这个函数用于更改xml文档的元素值。
    //入口参数:xml_target, xpath_expr同上,new_xml:新的元素值 。
    //如果没有找到或找到多个匹配的元素,则返回原始的xml文档。

    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>
    

Descriptions and examples of some basic XPath expressions follow:
//以下是一些XPATH表达式的示例:

  • /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"].

A discussion in depth of XPath syntax and usage are beyond the scope of this Manual. Please see the XML Path Language (XPath) 1.0 standard for definitive information. A useful resource for those new to XPath or who are wishing a refresher in the basics is the Zvon.org XPath Tutorial, which is available in several languages.

The XPath syntax supported by these functions is currently subject to the following limitations:

  • 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.

  • 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).

  • The following XPath functions are not supported:

    • id()

    • lang()

    • Priot to MySQL 5.1.8, the last() function was not supported (see Bug#16318).

    • local-name()

    • name()

    • namespace-uri()

    • normalize-space()

    • starts-with()

    • string()

    • substring-after()

    • substring-before()

    • translate()

  • The following axes are not supported:

    • following-sibling

    • following

    • preceding-sibling

    • preceding

Beginning with MySQL 5.1.10, XPath expressions passed as arguments to ExtractValue() and UpdateXML() may contain the colon character (“:”) in element selectors, which enables their use with markup employing XML namespaces notation. For example:
//从5.1.10开始支持“:”

mysql> SET @xml = '<a>111<b:c>222<d>333</d><e:f>444</e:f></b:c></a>';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ExtractValue(@xml, '//e:f');
+-----------------------------+
| ExtractValue(@xml, '//e:f') |
+-----------------------------+
| 444                         |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT UpdateXML(@xml, '//b:c', '<g:h>555</g:h>');
+--------------------------------------------+
| UpdateXML(@xml, '//b:c', '<g:h>555</g:h>') |
+--------------------------------------------+
| <a>111<g:h>555</g:h></a>                   |
+--------------------------------------------+
1 row in set (0.00 sec)

This is similar in some respects to what is allowed by Apache Xalan and some other parsers, and is much simpler than requiring namespace declarations or the use of the namespace-uri() and local-name() functions.

posted on 2006-09-19 11:09 游子 阅读(1230) 评论(0)  编辑 收藏 引用 所属分类: 软件
只有注册用户登录后才能发表评论。

欢迎大家扔鸡蛋!送鲜花!

博客可以收入过千吗?

<2006年9月>
日一二三四五六27282930311
234567891011121314151617181920212223242526272829301234567

常用链接

留言簿(8)

随笔分类(314)

随笔档案(337)

文章分类(7)

文章档案(10)

相册

收藏夹(1)

其它

友情链接

数字电视

生活、旅游

自己的链接

计算机

搜索

  •  

积分与排名

  • 积分 - 403156
  • 排名 - 9

最新评论

阅读排行榜

评论排行榜