XPath Functionality

In this section, we look at the XPath functions added in MySQL 5.1.5. ExtractValue() allows you to use an XPath expression on a fragment of XML in order to return the content of one or more elements. UpdateXML() makes it possible to replace an existing XML fragment with a new one, using XPath to specify the fragment to be replaced.

The ExtractValue() Function

The example in Using LOAD_FILE() demonstrates how to get the content of an XML file into a MySQL database, but the problem of getting at the actual data remains. One way to accomplish this is to use the ExtractValue() function.

The syntax for this function is shown here:

ExtractValue(xml_fragment, xpath_expression) 

ExtractValue() takes two arguments. The first of these is the XML fragment to be tested; the second is the XPath expression to be matched.

ExtractValue() example. Let' see how we might obtain the name of the first city in the document (which we saved as 2cities.xml). We do this in two steps. First, we get the XML from the xmldocs table and place into a user variable:

mysql> SELECT doc_content FROM xmldocs LIMIT 1 INTO @xml;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @xml\G
*************************** 1. row ***************************
@xml: <?xml version="1.0"?>

<resultset statement="SELECT name,country FROM cities LIMIT 2
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
        <field name="name">Mumbai (Bombay)</field>
        <field name="country">India</field>
  </row>

  <row>
        <field name="name">Seoul</field>
        <field name="country">South Korea</field>
  </row>
</resultset>

1 row in set (0.00 sec)

Now we use ExtractValue() with @xml as the first argument. For the second argument, we employ an XPath expression that means “the content of the first <field> element contained in the second <row> element found anywhere in the document”:

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

Another way to do this would be to use an XPath expression that means “the content of the second <field> having the name attribute "name"”:

mysql> SELECT ExtractValue(@xml, '//field[@name="name"][2]');
+------------------------------------------------+
| ExtractValue(@xml, '//field[@name="name"][2]') |
+------------------------------------------------+
| Seoul      |
+------------------------------------------------+
1 row in set (0.00 sec)

As you can see, the result (Seoul) is the same as before, which is exactly what we would expect.

The UpdateXML() Function

This function allows you to do replace a portion of an XML fragment (identified by an XPath locator) with different XML markup. For example, consider the XML fragment <book><chapter/></book>. Now suppose you wish to change this to <book><part><chapter/></part></book>. This shows how you can do so using UpdateXML(), saving the result into a user variable @new_xml:

mysql> SELECT @new_xml:=UpdateXML('<book><chapter/></book>', 
    ->    '//chapter', 
    ->    '<part><chapter/></part>')
    -> AS uxml;
+--------------------------------------------------------------------+
| uxml                                                               |
+--------------------------------------------------------------------+
| <book><part><chapter/></part></book> |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @new_xml;
+--------------------------------------+
| @new_xml                             |
+--------------------------------------+
| <book><part><chapter/></part></book> |
+--------------------------------------+
1 row in set (0.00 sec)

The syntax for this function is shown here:

UpdateXML(xml, locator, replacement)

xml is the XML markup to be tested for a match. locator is the XPath expression used to obtain the match. replacement is the markup to be used to replace the XML that matches the locator . Unlike ExtractValue(), UpdateXML() matches both elements and content, then returns the updated XML as a string. If no match for the locator is found, then the original xml is returned.