Использование XML совместно с SQL (45889)

Посмотреть архив целиком

Использование 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.

FOR XML

Этот оператор предназначен для представления результирующего набора строк в виде XML-документа. Рассмотрим его синтаксис:

[ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT }

[ , XMLDATA ]

[ , ELEMENTS ]

[ , BINARY BASE64 ]

}

]

Назначение ключевого слова BROWSE не относится к теме нашей статьи.

FOR XML RAW – Каждая строка представляется в виде элемента . Название поля формирует название атрибута, а значение поля – значение атрибута.

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:

Теперь заменим xml raw на xml auto:

Как видите, изменения невелики. Вместо названия элемента «row» подставляется имя таблицы. Теперь добавим к этому запросу ключевое слово ELEMENTS.

select au_fname, au_lname, address

from authors

where au_fname like 'M%'

for xml auto, elements

Вот результаты:

Marjorie

Green

309 63rd St. #411

Michael

O'Leary

22 Cleveland Av. #14

Meander

Smith

10 Mississippi Dr.

Morningstar

Greene

22 Graybar House Rd.

Michel

DeFrance

3 Balding Pl.

Документ получился более громоздким: все поля представлены элементами.

С помощью ключевого слова XMLDATA можно получить документ со схемой данных.

select au_fname, au_lname, address

from authors

where au_fname like 'M%'

for xml auto, xmldata

Этот запрос вернет такой документ:

xmlns:dt="urn:schemas-microsoft-com:datatypes">

au_fname="Marjorie" au_lname="Green" address="309 63rd St. #411" />

au_fname="Michael" au_lname="O'Leary" address="22 Cleveland Av. #14" />

au_fname="Meander" au_lname="Smith" address="10 Mississippi Dr." />

au_fname="Morningstar" au_lname="Greene" address="22 Graybar House Rd." />

au_fname="Michel" au_lname="DeFrance" address="3 Balding Pl." />

Как видите, теперь документу предшествует ХDR-схема, позволяющая проверить правильность его содержимого.

FOR XML EXPLICIT

В данном режиме можно формировать практически любые документы; структура результирующего 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

Вот результат:

Marjorie

Michael

Meander

Morningstar

Michel

Неплохо для одного запроса! Поскольку для поля au_lname указан атрибут element, оно представлено в виде элемента. Адрес находится в секции CDATA.

Теперь рассмотрим, как формировать иерархические документы. Возьмем такой запрос:

select pub_name,city,fname,lname

from publishers as p

join employee as e on p.pub_id = e.pub_id

where pub_name like 'Binnet%' or pub_name like 'New Moon%'

order by pub_name,city,fname,lname

Он возвращает имена всех служащих издательств Binnet & Hardley и New Moon Books. Результат запроса (20 записей) я приводить не буду, отмечу только, что он содержит большое количество повторяющихся названий издательств, т.к. результат представлен в реляционной форме. Мы же хотим получить следующее:

И как, спросите вы? Примерно так:

select 1 as tag, -- первый подзапрос

0 as parent,

pub_name as 'pubs!1!PubName',

city as 'pubs!1!City',

NULL as 'employee!2!First_Name',

NULL as 'employee!2!Last_Name'

from publishers as pubs

where pub_name like 'Binnet%' or pub_name like 'New Moon%'

union all select 2 as tag, -- второй подзапрос

1 as parent,

pubs.pub_name,

pubs.city,

fname,

lname

from employee as e, publishers as pubs

where (pub_name like 'Binnet%' or pub_name like 'New Moon%')

and pubs.pub_id = e.pub_id

order by 'pubs!1!PubName', 'pubs!1!City',

'employee!2!First_Name', 'employee!2!Last_Name'

for xml explicit


Случайные файлы

Файл
138395.rtf
45500.doc
9597-1.rtf
47287.rtf
118032.rtf




Чтобы не видеть здесь видео-рекламу достаточно стать зарегистрированным пользователем.
Чтобы не видеть никакую рекламу на сайте, нужно стать VIP-пользователем.
Это можно сделать совершенно бесплатно. Читайте подробности тут.