Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions




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 or mysqldump client by invoking it with the --xml option. See Section 8.5, “mysql — The MySQL Command-Line Tool”, and Section 8.10, “mysqldump — A Database Backup Program”.

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.

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

Descriptions and examples of some basic XPath expressions follow:

  • /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 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[@[email protected]]') 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:

    • id()

    • lang()

    • last()

    • 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

  Published under the terms of the GNU General Public License Design by Interspire