Работа с XML в SQL запросах

В современных проектах XML встречается довольно часто. Несмотря на избыточность, этот язык разметки данных стал очень популярным из-за своей универсальности. Если нам нужно распарсить какой-то конфиг файл на XML, то практически в каждом языке программирования есть удобная библиотека. В C++ мне нравится Xerces-C++ своей кроссплатформенностью, однако, готов согласиться, если кто-то скажет, что это, местами, не самый удобный инструмент.

Сейчас я хочу рассмотреть сценарий работы с данными в виде XML, которые могут храниться в базе данных. Современные серверы БД поддерживают, в разной степени, тип данных XML. Я буду приводить примеры в MySql, т. к. под рукой в данный момент есть только он. Например, у нас есть таблица xmltest:

CREATE TABLE `xmltest` (
  `conf` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Далее, будем считать, что в поле conf хранятся данные каждого пользователя системы следующего вида:

<Root>
 <CoreOptions>
  <Option type="str" value="test" />
 </CoreOptions>
 <UserOptions>
  <Favorites>
    <Page id="1">http://www.codeatcpp.com</Page>
    <Page id="2">http://www.google.com</Page>
    <Page id="3">http://www.yandex.ru</Page>
    <Page id="4">http://www.stackoverflow.com</Page>
  </Favorites>
 </UserOptions>
</Root>

Итак, можно загрузить все данные пользователей и каждый конфиг разобрать своим любимым парсером XML, но что, если мы всего лишь хотим посмотреть сколько у каждого пользователя любимых сайтов? Это можно узнать за один SQL запрос используя язык XQuery+XPath:

SELECT ExtractValue(conf, 'count(/Root[1]/UserOptions[1]/Favorites[1]/Page)') 
FROM `xmltest`

Этот запрос для каждого пользователя выдаст количество элементов Page в поле conf. Поскольку в XML элементы не уникальны и могут повторятся, то указываем в квадратных скобках, что нужен первый по порядку элемент. Если это не указать, то запрос вернет набор элементов, как в случае с Page.
Если мы хотим получить атрибут элемента, то показываем это символом @:

SELECT ExtractValue(conf, '/Root[1]/UserOptions[1]/Favorites[1]/Page[1]/@id') 
FROM `xmltest`

Такой запрос выдаст идентификатор первой страницы для каждого пользователя.

Стоит отметить, что для разных реализаций SQL синтаксис запроса будет отличаться, например, в MS SQL Server последний запрос будет выглядеть так:

SELECT conf.value('/Root[1]/UserOptions[1]/Favorites[1]/Page[1]/@id', 'int')
FROM xmltest

Поля XML можно не только выводить, но и использовать в условиях поиска, например:

SELECT ExtractValue(conf, '/Root[1]/CoreOptions[1]/Option[1]/@value') 
FROM `xmltest` 
WHERE
  ExtractValue(conf, 'count(/Root[1]/UserOptions[1]/Favorites[1]/Page)') > 0

Такой запрос выдаст параметр Option из раздела CoreOptions для всех пользователей, у которых есть закладки.

В такой маленькой статье невозможно рассмотреть все возможности работы с XML в SQL запросах, поэтому даю список ресурсов, с которыми стоит познакомиться:

1. Методы типа данных XML в MS SQL Server.
2. XML функции сервера MySQL.
3. xpathtester.com — сайт, где можно проверить свои XPath запросы.
4. Вопросы и ответы по использованию XML в SQL запросах.