Использование XML совместно с SQL
XML и современные базы данных
Алексей Ширшов
Введение
Этот материал посвящен тем нововведениям, которые появились в SQL и технологиях доступа к базам данных благодаря XML. Статья описывает базовые механизмы и возможности использования XML в MS SQL Server и ADO. Статья не претендует на фундаментальные исследования в этой области, так как XML и SQL Server вещи по природе своей необъятные. Кроме того, уровень поддержки XML в SQL Server постоянно увеличивается, и за всеми изменениями чрезвычайно трудно уследить. Например, после выхода SQLXML 3.0, стало возможным использовать SQL Server в качестве сервера Web-служб. К сожалению, эта тема в статье не освещается, но в будущем, возможно, найдется время и для нее.
Своим появлением статья обязана тому беспорядку, который был в голове у автора по данному вопросу.
Поддержка XML в Microsoft SQL Server 2000
Microsoft SQL Server 2000 содержит встроенные средства для работы с XML. Результирующую выборку можно представлять в формате XML с помощью ключевых слов for xml оператора select, а также сделать запрос из документа XML с помощью оператора OPENXML.
Этот оператор предназначен для представления результирующего набора строк в виде XML-документа. Рассмотрим его синтаксис:
| [ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT } [ , XMLDATA ] [ , ELEMENTS ] [ , BINARY BASE64 ] }] |
Назначение ключевого слова BROWSE не относится к теме нашей статьи.
FOR XML RAW – Каждая строка представляется в виде элемента <row/>. Название поля формирует название атрибута, а значение поля – значение атрибута.
FOR XML AUTO – Документ XML форматируется точно так же, как и при XML RAW, только название элемента, представляющего строку, заменяется на название таблицы.
FOR XML EXPLICIT – Самый сложный и гибкий вариант для создания XML-документов. В этом режиме можно формировать документы практически любой формы, однако для этого сам запрос должен быть написан по определенным правилам. Более подробно они рассматриваются ниже.
XMLDATA – Иногда бывает полезно получить не только сами данные, но и их схему. Схема данных также записывается в формате XML. Она определяет типы элементов и атрибутов, накладывает ограничения на их значения, и вообще представляет метаинформацию, позволяющую проверить документ на действительность (validity). Существует несколько разновидностей (форматов) схем данных. SQL Server использует XDR-схемы (XML Data Reduced). Подробную документацию по XDR можно найти в [1]. Ключевое слово XMLDATA может быть использовано для всех трех режимов формирования XML-документа (raw, auto и explicit).
ELEMENTS – Ключевое слово, использующееся только совместно с FOR XML AUTO. При его указании поля формируются как элементы: название поля соответствует названию элемента, а значение поля – значению элемента.
BINARY BASE64 – Определяет, как будут выведены двоичные данные (binary data).
| ПРЕДУПРЕЖДЕНИЕSQL Server не позволяет использовать предикат GROUP BY совместно с FOR XML AUTO. |
Примеры
Для простоты и удобства будем использовать стандартную базу данных PUBS из поставки SQL Server 2000. Надо сказать, что Query Analyzer – не лучшее средство для просмотра XML-документов, т.к. результат он помещает в одну ячейку, как текстовое поле (или в одну строку, обрезая текст, при выводе результата в виде текста). Поэтому, если вы хотите испробовать все примеры сами, обратитесь к разделу IIS и XML-функции SQL Server.
Начнем с рассмотрения FOR XML RAW:
| select au_fname, au_lname, address from authors where au_fname like 'M%' for xml raw |
Этот запрос возвращает имена всех авторов, начинающиеся с буквы M. Вот результаты в формате XML:
| <row au_fname="Marjorie" au_lname="Green" address="309 63rd St. #411" /> <row au_fname="Michael" au_lname="O'Leary" address="22 Cleveland Av. #14" /> <row au_fname="Meander" au_lname="Smith" address="10 Mississippi Dr." /> <row au_fname="Morningstar" au_lname="Greene" address="22 Graybar House Rd." /><row au_fname="Michel" au_lname="DeFrance" address="3 Balding Pl." /> |
Теперь заменим xml raw на xml auto:
| <authors au_fname="Marjorie" au_lname="Green" address="309 63rd St. #411" /> <authors au_fname="Michael" au_lname="O'Leary" address="22 Cleveland Av. #14" /><authors au_fname="Meander" au_lname="Smith" address="10 Mississippi Dr." /> <authors au_fname="Morningstar" au_lname="Greene" address="22 Graybar House Rd." /><authors au_fname="Michel" au_lname="DeFrance" address="3 Balding Pl." /> |
Как видите, изменения невелики. Вместо названия элемента «row» подставляется имя таблицы. Теперь добавим к этому запросу ключевое слово ELEMENTS.
| select au_fname, au_lname, address from authors where au_fname like 'M%' for xml auto, elements |
Вот результаты:
| <authors> <au_fname>Marjorie</au_fname> <au_lname>Green</au_lname> <address>309 63rd St. #411</address> </authors><authors> <au_fname>Michael</au_fname> <au_lname>O'Leary</au_lname> <address>22 Cleveland Av. #14</address> </authors><authors> <au_fname>Meander</au_fname> <au_lname>Smith</au_lname> <address>10 Mississippi Dr.</address> </authors><authors> <au_fname>Morningstar</au_fname> <au_lname>Greene</au_lname> <address>22 Graybar House Rd.</address> </authors><authors> <au_fname>Michel</au_fname> <au_lname>DeFrance</au_lname> <address>3 Balding Pl.</address> </authors> |
Документ получился более громоздким: все поля представлены элементами.
С помощью ключевого слова XMLDATA можно получить документ со схемой данных.
| select au_fname, au_lname, address from authors where au_fname like 'M%' for xml auto, xmldata |
Этот запрос вернет такой документ:
| <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes"> <ElementType name="authors" content="empty" model="closed"> <AttributeType name="au_fname" dt:type="string" /> <AttributeType name="au_lname" dt:type="string" /> <AttributeType name="address" dt:type="string" /> <attribute type="au_fname" /> <attribute type="au_lname" /> <attribute type="address" /> </ElementType></Schema><authors xmlns="x-schema:#Schema1" au_fname="Marjorie" au_lname="Green" address="309 63rd St. #411" /> <authors xmlns="x-schema:#Schema1" au_fname="Michael" au_lname="O'Leary" address="22 Cleveland Av. #14" /> <authors xmlns="x-schema:#Schema1" au_fname="Meander" au_lname="Smith" address="10 Mississippi Dr." /> <authors xmlns="x-schema:#Schema1" au_fname="Morningstar" au_lname="Greene" address="22 Graybar House Rd." /><authors xmlns="x-schema:#Schema1" au_fname="Michel" au_lname="DeFrance" address="3 Balding Pl." /> |
Как видите, теперь документу предшествует ХDR-схема, позволяющая проверить правильность его содержимого.
В данном режиме можно формировать практически любые документы; структура результирующего XML-документа определяется непосредственно в самом запросе. Запрос может принимать очень сложный вид, ниже будут рассмотрены правила, по которым он составляется.
Первое поле всегда называется tag и представляет собой числовой идентификатор таблицы. Вы можете указывать для него любые числовые значения. В результирующем документе он не появляется, и нужен только, чтобы определить связи между таблицами для формирования иерархического документа. Второе поле называется parent и тоже может представлять любой числовой идентификатор. Он определяет родительскую таблицу для данной таблицы и в результирующем документе также не присутствует. Если родитель отсутствует, указывается 0 или NULL. Для иерархических выборок нужно использовать несколько запросов, объединенных с помощью оператора UNION ALL.
После двух обязательных полей tag и parent следуют поля, которые требуется выбрать из таблицы. Для них должен быть задан псевдоним, определяющий тип XML-узла, его название и другую информацию. Вот синтаксис этого псевдонима:
| ElementName!TagNumber!AttributeName!Directive |
Рассмотрим назначение каждой из частей этого псевдонима:
ElementName – имя элемента, в котором будет находится элемент данного поля. Обычно здесь указывается имя таблицы.
TagNumber – идентификатор таблицы, в которой находится данное поле. Одному и тому же ElementName всегда должен соответствовать один TagNumber. Он также не отображается ни в каком виде в результирующем документе, однако нужен для определения иерархических связей между таблицами.
AttributeName – имя атрибута (или элемента), представляющего данное поле.
Directive – по существу, представляет собой тип узла. Может принимать следующие значения:
| element | поле представляется в виде элемента |
| xml | То же самое, что element, но не выполняет трансформации текста. Например, символ меньше (<) не превращается в ссылку < |
| cdata | значение поля обрамляется соответствующим образом. Имя атрибута указывать совместно с этим ключевым словом нельзя. |
| hide | позволяет скрыть поле |
| id,idref,idrefs | позволяют делать ссылки на другие элементы. Эти атрибуты имеют значение, только если создается схема данных. |
Рассмотрим небольшой пример. В самом начале мы рассматривали запрос с использованием FOR XML RAW:
| select au_fname,au_lname,address from authors where au_fname like 'M%' for xml raw |
Перепишем его с использованием FOR XML EXPLICIT:
| select 1 as tag, 0 as parent, au_fname as 'authors!1!fname', au_lname as 'authors!1!lname', address as 'authors!1!address' from authors where au_fname like 'M%'for xml explicit |
Результат будет точно таким же. Немного исправим запрос для демонстрации возможностей директивы FOR XML EXPLICIT.
| select 1 as tag, 0 as parent, au_fname as 'authors!1!fname', au_lname as 'authors!1!lname!element', address as 'authors!1!!cdata' from authors where au_fname like 'M%'for xml explicit |
Вот результат:
| <authors lname="Green"> <fname>Marjorie</fname> <![CDATA[ 309 63rd St. #411 ]]> </authors><authors lname="O'Leary"> <fname>Michael</fname> <![CDATA[ 22 Cleveland Av. #14 ]]> </authors><authors lname="Smith"> <fname>Meander</fname> <![CDATA[ 10 Mississippi Dr. ]]> </authors><authors lname="Greene"> <fname>Morningstar</fname> <![CDATA[ 22 Graybar House Rd. ]]> </authors><authors lname="DeFrance"> <fname>Michel</fname> <![CDATA[ 3 Balding Pl. ]]> </authors> |
Неплохо для одного запроса! Поскольку для поля au_lname указан атрибут element, оно представлено в виде элемента. Адрес находится в секции CDATA.