13 апреля 2009 г. MySQL XML XPath

Работаем с XML данными и XPath в MySQL

Как известно, в MySQL 5.1 внедрили функции для работы с XML, сегодня я расскажу про эти две функции более подробно, а так же вкратце опишу как работать с XPath в MySQL.

Для работы с XML есть две функции:

1. ExtractValue() - Позволяет выбирать записи средствами XPAth;


2. UpdateXML() - Возвращает измененный XML-фрагмент.

Функция ExtractValue и XPath

Синтаксис следующий:

ExtractValue(xml_frag, xpath_expr)

Для примеров, я буду работать с переменной, но с тем же успехом вы можете записать XML-фрагмент в любое поле с текстовым типом данных. XPath поддерживается на любом движке.
Создадим переменную @xml:

mysql> set @xml = '<x id="xx"><y id="yy"><z id="zz">test</z></y></x>';

Выберем XML-фрагмент с помощью 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)

Как видно, мы выбрали содержимое ноды <z> -> "test".

Примеры:

Немного ознакомимся с языком запросов XPath и приведу несколько примеров.
Создадим для примера такую вот переменную:

mysql> set @xml = '<z><x>x1</x><x>x2</x><y id="y-id">y-value</y></z>';

1) Посчитаем количество элементов с нодой <x>:

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) Выведем содержимое первой ноды <x>:

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) Выведем содержимое ноды с атрибутом id и значением атрибута "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)

В данный момент MySQL поддерживает XPath версии 1.0, рекомендую посмотреть переведенную спецификацию по этой версии языка.

Функция UpdateXML

С помощью функции UpdateXML мы можем изменять какие-либо XML-фрагменты. Используется для изменения XML-фрагмента и сохранения в базу данных, например при UPDATE. Синтаксис следующий:

UpdateXML(xml_target, xpath_expr, new_xml)

Пример:

1) Заменим первую ноду "<x>" на "<c>test</c>":

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) Заменим содержимое ноды с атрибутом id и значением атрибута "y-id" на "<c>test</c>":

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)

Еще больше примеров на странице руководства по XML-функциям, так же рекомендую почитать о XPath в википедии и ознакомится с переведенной спецификацией XPath версии 1.0.

Производительность

Потестим производительность на моей машине для разработки:

Microsoft Windows XP [Версия 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)

Для теста создадим переменную:

mysql> set @xml = '<a><b></b><b><c id="c-id">c-value</c></b></a>';

Проверим на выборку 1 000 000 раз.

mysql> select benchmark(1000000, ExtractValue(@xml, '/a/b/c'));

+--------------------------------------------------+
| benchmark(1000000, ExtractValue(@xml, '/a/b/c')) |
+--------------------------------------------------+
|                                                0 |
+--------------------------------------------------+
1 row in set (4.77 sec)

Проверим на изменение XML-фрагмента 1 000 000 раз.

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)

Выводы никакие делать не буду, так как тесты слишком приблизительны. Надо тестировать на конкретной машине, желательно не под Windows (на Linux или под OpenSolaris с конкретно собранной для этой ОСи движком MySQL) и надо тестить конкретные XPath-запросы!

UPD: Moscow MySQL User Group: Для чего XPath в MySQL?