Working with XML Data and XPath in MySQL
As you may know, MySQL 5.1 introduced functions for working with XML. Today I’ll discuss these two functions in more detail and briefly describe how to work with XPath in MySQL.
There are two functions for working with XML:
- ExtractValue() - Lets you select records using XPath;
- UpdateXML() - Returns a modified XML fragment.
The ExtractValue Function and XPath
The syntax is as follows:
ExtractValue(xml_frag, xpath_expr)
For these examples, I’ll work with a variable, but you can just as easily store an XML fragment in any field with a text data type. XPath is supported with any storage engine. Let’s create a variable, @xml:
mysql> set @xml = '<x id="xx"><y id="yy"><z id="zz">test</z></y></x>';
Let’s select an XML fragment using XPath:
mysql> select @xml, ExtractValue(@xml, '//z');
+---------------------------------------------------+---------------------------+
| @xml | ExtractValue(@xml, '//z') |
+---------------------------------------------------+---------------------------+
| <x id="xx"><y id="yy"><z id="zz">test</z></y></x> | test |
+---------------------------------------------------+---------------------------+
1 row in set (0.00 sec)
As you can see, we selected the contents of the node -> “test”.
Examples:
Let’s get a little more familiar with the XPath query language, and I’ll give a few examples. For our examples, let’s create this variable:
mysql> set @xml = '<z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z>';
1) Count the number of elements:
mysql> select @xml, ExtractValue(@xml, 'count(//x)');
+---------------------------------------------------+----------------------------------+
| @xml | ExtractValue(@xml, 'count(//x)') |
+---------------------------------------------------+----------------------------------+
| <z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z> | 2 |
+---------------------------------------------------+----------------------------------+
1 row in set (0.00 sec)
2) Display the contents of the first node:
mysql> select @xml, ExtractValue(@xml, '//x[1]');
+---------------------------------------------------+------------------------------+
| @xml | ExtractValue(@xml, '//x[1]') |
+---------------------------------------------------+------------------------------+
| <z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z> | x1 |
+---------------------------------------------------+------------------------------+
1 row in set (0.00 sec)
3) Display the contents of the node with an id attribute whose value is “y-id”:
mysql> select @xml, ExtractValue(@xml, '//y[@id="y-id"]');
+---------------------------------------------------+---------------------------------------+
| @xml | ExtractValue(@xml, '//y[@id="y-id"]') |
+---------------------------------------------------+---------------------------------------+
| <z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z> | y-value |
+---------------------------------------------------+---------------------------------------+
1 row in set (0.00 sec)
At the moment, MySQL supports XPath version 1.0. I recommend looking at the translated specification for this version of the language.
The UpdateXML Function
With the UpdateXML function, we can modify XML fragments. It is used to modify an XML fragment and save it to the database, for example during an UPDATE. The syntax is as follows:
UpdateXML(xml_target, xpath_expr, new_xml)
Example:
1) Replace the first “” node with “test”:
mysql> select @xml, UpdateXML(@xml, '//x[1]', '<c>test</c>');
+---------------------------------------------------+-----------------------------------------------------+
| @xml | UpdateXML(@xml, '//x[1]', '<c>test</c>') |
+---------------------------------------------------+-----------------------------------------------------+
| <z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z> | <z><c>test</c><x>x2</x><y id="y-id">y-value</y></z> |
+---------------------------------------------------+-----------------------------------------------------+
1 row in set (0.00 sec)
2) Replace the contents of the node with an id attribute whose value is “y-id” with “test”:
mysql> SELECT @xml, UpdateXML(@xml, '//y[@id="y-id"]', '<c>test</c>');
+---------------------------------------------------+---------------------------------------------------+
| @xml | UpdateXML(@xml, '//y[@id="y-id"]', '<c>test</c>') |
+---------------------------------------------------+---------------------------------------------------+
| <z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z> | <z><x>x1</x><x>x2</x><c>test</c></z> |
+---------------------------------------------------+---------------------------------------------------+
1 row in set (0.00 sec)
There are even more examples on the XML functions manual page. I also recommend reading about XPath on Wikipedia and looking through the translated XPath version 1.0 specification.
Performance
Let’s test performance on my development machine:
Microsoft Windows XP [Version 5.1.2600] SP3 Mysql 5.1.26 Intel Core Duo T2050 1.6 GHz (L2 Cache 2MB, Bus Speed 133 MHZ, Rated FSB 533 MHz) Memory Type DDR2, Dual Channels, Size 1024 MB (Frequency 266 MHz)
Let’s create a variable for the test:
mysql> set @xml = '<a><b></b><b><c id="c-id">c-value</c></b></a>';
Let’s test selection 1,000,000 times.
mysql> select benchmark(1000000, ExtractValue(@xml, '/a/b/c'));
+--------------------------------------------------+
| benchmark(1000000, ExtractValue(@xml, '/a/b/c')) |
+--------------------------------------------------+
| 0 |
+--------------------------------------------------+
1 row in set (4.77 sec)
Let’s test modifying an XML fragment 1,000,000 times.
mysql> select benchmark(1000000, UpdateXML(@xml, '/a/b/c', '<c id="c-id">c-value</c>'));
+---------------------------------------------------------------------------+
| benchmark(1000000, UpdateXML(@xml, '/a/b/c', '<c id="c-id">c-value</c>')) |
+---------------------------------------------------------------------------+
| 0 |
+---------------------------------------------------------------------------+
1 row in set (4.47 sec)
I won’t draw any conclusions, since these tests are too approximate. You need to test on the actual machine, preferably not under Windows (on Linux or OpenSolaris, with the MySQL engine built specifically for that OS), and you need to test the actual XPath queries!
Comments are closed. Read the original discussion in Russian →