Использование 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 – Каждая строка представляется в виде элемента <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-схема, позволяющая проверить правильность его содержимого.

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

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

<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.

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

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 записей) я приводить не буду, отмечу только, что он содержит большое количество повторяющихся названий издательств, т.к. результат представлен в реляционной форме. Мы же хотим получить следующее:

<pubs PubName="Binnet & Hardley" City="Washington">

 <employee First_Name="Anabela" Last_Name="Domingues" />

 <employee First_Name="Daniel" Last_Name="Tonini" />

 <employee First_Name="Elizabeth" Last_Name="Lincoln" />

 <employee First_Name="Helen" Last_Name="Bennett" />

 <employee First_Name="Lesley" Last_Name="Brown" />

 <employee First_Name="Martine" Last_Name="Rance" />

 <employee First_Name="Paolo" Last_Name="Accorti" />

 <employee First_Name="Paul" Last_Name="Henriot" />

 <employee First_Name="Peter" Last_Name="Franken" />

 <employee First_Name="Victoria" Last_Name="Ashworth" />

</pubs>

<pubs PubName="New Moon Books" City="Boston">

 <employee First_Name="Gary" First_Name="Thomas" />

 <employee First_Name="Howard" First_Name="Snyder" />

 <employee First_Name ="Karin" First_Name="Josephs" />

 <employee First_Name ="Laurence" Last_Name="Lebihan" />

 <employee First_Name ="Martin" Last_Name="Sommer" />

 <employee First_Name ="Mary" Last_Name="Saveley" />

 <employee First_Name ="Matti" Last_Name="Karttunen" />

 <employee First_Name ="Palle" Last_Name="Ibsen" />

 <employee First_Name ="Roland" Last_Name="Mendel" />

 <employee First_Name ="Timothy" Last_Name="O'Rourke" />

</pubs>

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

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

Давайте рассмотрим все по порядку. Сначала выполняется первый подзапрос. Его результат приведен в таблице 1.

tag

parent

pubs!1!PubName

pubs!1!City

employee!2!First_Name

employee!2!Last_Name

1

0

New Moon Books

Boston

NULL

NULL

1

0

Binnet & Hardley

Washington

NULL

NULL

Таблица 1.

Затем второй (Таблица 2).

tag

parent

pub_name

city

fname

lname

2

1

Binnet & Hardley

Washington

Paolo

Accorti

2

1

Binnet & Hardley

Washington

Victoria

Ashworth

2

1

Binnet & Hardley

Washington

Helen

Bennett

2

1

Binnet & Hardley

Washington

Lesley

Brown

...

...

...

...

...

...

Таблица 2.

Затем происходит сортировка, и на основе полей tag и parent SQL Server формирует иерархический XML документ.

ПРИМЕЧАНИЕ

Для отладки подобных запросов лучше не указывать оператор FOR XML EXPLICIT. Тогда данные будут представлены в обычной реляционной форме.

На этом мы, пожалуй, закончим рассмотрение оператора FOR XML EXPLICIT – приведение примеров использования всех атрибутов заняло бы слишком много места.

OPENXML

Функция OPENXML является аналогом OPENROWSET, OPENDATASOURCE и OPENQUERY, которые позволяют выполнять запросы из удаленных источников. Вот ее синтаксис:

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]])

[WITH (SchemaDeclaration | TableName)]

Аргументы:

idoc – хендл XML-документа, полученный при помощи хранимой процедуры sp_xml_preparedocument;

rowpattern – локализуемая группа XPath или, проще говоря, XPath-выражение;

flags – набор флагов, указывающих на то, как должны быть сопоставлены данные документа XML и реляционного набора строк;

ShemaDeclaration – определение полей реляционного набора строк в формате:

ColName ColType [ColPattern | MetaProperty]

Где

ColName – имя поля.

ColType – тип поля. Допускаются все типы SQL Server.

ColPattern - локализуемая группа XPath для поля.

MetaProperty – метасвойство. Его мы рассматривать не будем.

XML-документ подготавливается с помощью хранимой процедуры sp_xml_preparedocument. Процедура использует анализатор MSXML для проверки документа на правильность и возвращает хендл документа. После завершения работы с OPENXML хендл нужно закрыть с помощью процедуры sp_xml_removedocument.

ПРИМЕЧАНИЕ

sp_xml_preparedocument подготавливает XML-документ, представляя его в виде объектной модели DOM (Document Object Model). Если вы работаете с большими документами, это может вызвать некоторые проблемы.

Как видно из синтаксиса, вы можете не указывать флаги и определения полей для реляционного набора строк. В этом случае SQL Server создаст внутреннее представление XML-документа в так называемом "edge table"-формате. Он практически не читаем, однако при большом желании его можно использовать. Описание этого формата выходит за рамки данной статьи, но в качестве доказательства того, что с ним можно работать, приведу пример. Пусть у нас имеется такой XML-документ:

<?xml version="1.0" encoding="windows-1251" ?>

<rsdn>

 <forums date="09.01.03">

  <forum name="WinAPI" totalposts="16688"

      description="Системное программирование">

   <moderators/>

   <top-poster>Alex Fedotov</top-poster>

  </forum>

  <forum name="COM" totalposts="10116"

      description="Компонентные технологии">

   <moderators/>

   <top-poster>Vi2</top-poster>

  </forum>

  <forum name="Delphi" totalposts="5001"

      description="Delphi и Builder">

   <moderators>

    <moderator name="Sinclair"/>

    <moderator name="Hacker_Delphi"/>

   </moderators>

   <top-poster>Sinclair</top-poster>

  </forum>

  <forum name="DB" totalposts="6606" description="Базы данных">

   <moderators>

    <moderator name="_MarlboroMan_"/>

   </moderators>

   <top-poster>Merle</top-poster>

  </forum>

 </forums>

</rsdn>

Вот запрос, возвращающий общее количество сообщений для каждого форума:

exec sp_xml_preparedocument @hdoc out, @_xmlbody

select [text] as totalposts

 from openxml(@hdoc,'/rsdn/forums/forum') as f

  join (select [id],localname

      from openxml(@hdoc,'/rsdn/forums/forum')

      where localname = 'totalposts') as d on d.[id] = f.parentid

exec sp_xml_removedocument @hdoc

Результатом его будет следующая таблица:

totalposts

16688

10116

5001

6606

Не советую использовать подобный метод в рабочих проектах, и не только потому, что он неэффективен (как видно из примера, XML-документ сканируется дважды). Рассмотрим пример, выдающий тот же самый результат с использованием XPath.

exec sp_xml_preparedocument @hdoc out, @_xmlbody

select *

 from openxml(@hdoc,'/rsdn/forums/forum')

  with(totalposts varchar(100) 'attribute::totalposts')

exec sp_xml_removedocument @hdoc

Здесь, чтобы ограничить реляционный набор строк, я воспользовался XPath-выражением.

Выражение attribute::totalposts означает, что для поля totalposts будет использоваться значение одноименного атрибута. Гораздо чаще в XPath-выражениях используется сокращенная запись:

«attribute::» можно заменить символом @;

«self::node()» можно заменить на точку (.);

«parent::node()» можно заменить на две точки (..).

Другие сокращения можно найти в спецификации XPath.

Давайте рассмотрим более сложный пример: выберем название форума, модератора и дату создания статистики для всех форумов, у которых больше 6000 сообщений.

exec sp_xml_preparedocument @hdoc out, @_xmlbody

select

 forum as 'Форум',

 case when moders is null

  then 'нет'

  else moders

  end as 'Модератор',

 [date] as 'Дата создания'

 from openxml(@hdoc,'/rsdn/forums/forum[attribute::totalposts > "6000"]') with

 (

  moders varchar(50) 'moderators/moderator/attribute::name',

  forum varchar(50) 'attribute::name',

  [date] varchar(50) 'parent::node()/attribute::date'

 )

exec sp_xml_removedocument @hdoc

Часть запроса, использующую XPath, можно переписать в сокращенной форме :

openxml(@hdoc,'/rsdn/forums/forum[@totalposts > "6000"]') with

(

 moders varchar(50) 'moderators/moderator/@name',

 forum varchar(50) '@name',

 [date] varchar(50) '../@date'

)

Везде далее я буду пользоваться сокращенной записью.

Чтобы разобраться с флагами OPENXML, рассмотрим слегка модифицированный пример из MSDN:

DECLARE @idoc int

DECLARE @doc varchar(1000)

SET @doc ='

<root>

 <Customer cid= "C1" city="Issaquah">

  <name>Janine</name>

  <Order oid="O1" date="1/20/1996" amount="3.5" />

  <Order oid="O2" date="4/30/1997" amount="13.4">

   Customer was very satisfied

  </Order>

 </Customer>

 <Customer cid="C2" city="Oelde" >

  <name>Ursula</name>

  <Order oid="O4" date="1/20/1996" amount="10000">Happy Customer.</Order>

  <Order oid="O3" date="7/14/1999" amount="100"

          note="Wrap it blue white red">

   Sad Customer.

   <Urgency>Important</Urgency>

  </Order>

 </Customer>

</root>'

-- Создание внутреннего представления XML-документа.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *

 FROM OPENXML (@idoc, '/root/Customer', 2) WITH

 (

  cid char(5) '@cid',

  [name] varchar(20),

  oid char(5) 'Order/@oid',

  amount float 'Order/@amount',

  comment varchar(100) 'Order/text()'

 )

-- Очистка

EXEC sp_xml_removedocument @idoc

Результат будет следующим:

cid

name

oid

amount

comment

C1

Janine

O1

3.5

Customer was very satisfied

C2

Ursula

O4

10000.0

Happy Customer.

Отметим некоторые особенности:

В качестве режима отображения XML-данных на поля реляционной таблицы использовалось значение 2 (element-centric mapping). Это означает, что по умолчанию имена колонок получаемой реляционной таблицы будут соответствовать именам вложенных XML-элементов. Кроме этого, возможно использование значений 0, 1 и 8. 0 используется по умолчанию и означает использование attribute-centric mapping. 1, как ни странно, означает то же самое. Флаги 1 и 2 можно комбинировать по "или", т.е. если подставить 3, сначала будет произведена попытка найти атрибут с именем, соответствующим имени колонки, а затем (если атрибут не найден) будет произведен поиск элемента с соответствующим именем (иначе будет возвращен NULL). Благодаря тому, что в качестве флага было указано значение 2, для поля cid пришлось явно указать XPath-запрос, указывающий, что на эту колонку отображается атрибут cid. Для поля name не потребовалось непосредственного указания XPath-выражения. Если бы в качестве флага использовалось значение 1 (использование отображения атрибутов), то картина изменилась бы на противоположную: т.е. для cid не нужно бы было ничего указывать, а для name пришлось бы написать шаблон (т.е. просто выражение ‘name’).

Особенность применения XPath-выражений при отображении данных состоит в том, что возможна выборка данных, расположенных практически в любой части XML-документа (относительно текущей ветки). Так, можно обратиться к подветкам текущей ветки, родительским веткам, и даже получить данные на основе выполнения некоторого условия. Если бы вместо «comment varchar(100) 'Order/text()'» было написано «comment varchar(100) 'Order'», то колонка comment первой строки содержала бы пустую строку. Она бралась бы из первого заказа (O1). Но так как текста в этом элементе нет, функция text() возвратит для него false, что приведет к поиску текста в следующем по порядку элементе Order (заказе O2). Таким образом, в сформированной записи будет находиться информация из первого заказа и комментарий из второго. Прикладного смысла это действие не имеет, но замечательно демонстрирует гибкость техники отображения.

На этом мы с вами закончим рассмотрение конструкции OPENXML. Более подробную информацию можно получить в MSDN. Спецификацию XPath можно найти в [2].

IIS и XML функции SQL Server

Чтобы выполнять запросы к SQL Server через HTTP, необходимо настроить соответствующим образом интернет-сервер. Делается это с помощью мастера "Configure SQL XML Support in IIS". Я не буду описывать полностью его работу, при необходимости можете обратиться к [3]. Кроме этого, настроить виртуальный каталог можно программно с помощью объекта VDirMgr. Он находится в файле sqlvdr3.dll. Для использования класса из VB нужно добавить ссылку на библиотеку типов Microsoft SQL Virtual Directory Control 1.0 Type Library.

Мастер "Configure SQL XML Support in IIS" создает виртуальный каталог, для обработки запросов к которому назначается специальное isapi-расширение: sqlisapi.dll. Эта библиотека, используя провайдер SQL OLEDB, связывается с SQL Server для отправки запросов и получения результатов. Результирующие выборки, представленные уже в формате XML, передаются обратно вызывающей стороне по HTTP. С помощью мастера вы можете указать:

Учетную запись SQL Server или Windows, под которой будут выполняться все запросы;

Компьютер, на котором расположен SQL Server и базу данных;

Подкаталоги данного виртуального каталога для хранения различных типов файлов (шаблонов, схем). Подкаталоги могут быть трех предопределенных типов: schema, template и dbobject. В подкаталоге schema хранятся XDR или XSD схемы данных, которые можно непосредственно исполнять в URL-запросе. В подкаталоге с типом template хранятся шаблоны, исполнение которых разрешено через URL-запросы. dbobject – псевдокаталог, его мы рассматривать не будем.

ПРИМЕЧАНИЕ

В SQLXML 3.0 появился еще один тип подкаталогов – soap. Его рассмотрение также выходит за рамки данной статьи.

Отдельно остановимся на настройках каталога. Вы можете:

Позволить или запретить указывать SQL-инструкции непосредственно в URL. Отмечу, что в качестве инструкций можно использовать не только запросы, но и любые другие операторы. Советую устанавливать эту опцию только для отладки, а в нормальном режиме работы выключать.

Позволить или запретить исполнение запросов, хранящихся в специальных шаблонах. Подробнее о шаблонах будет сказано далее.

Позволить или запретить использование запросов XPath;

Позволить или запретить использовать метод POST.

URL-запросы

Рассмотрим синтаксис URL-запроса к SQL Server:

http://iisserver/vroot?sql=sqlinstruction[¶m=value[¶m=value]...n]

Здесь:

iisserver – имя интернет-сервера;

vroot – имя виртуального каталога;

sqlinstruction – любая SQL-инструкция;

param – имя параметра. Это не параметр SQL-инструкции или хранимой процедуры, это параметр шаблона или один из следующих предопределенных параметров: contenttype, outputencoding, root и xsl;

value – значение параметра.

Итак, предположим, вы сконфигурировали виртуальный каталог для использования базы данных PUBS и назвали его server_pubs. Положим, ваш компьютер называется server. Попробуем написать первый URL-запрос:

http://server/server_pubs/?sql=select au_fname, au_lname, address from authors where au_fname like 'M%' for xml raw

Но не все так просто. Ответ будет таким:

XML document must have a top level element.

Первый блин, как всегда, комом! Дело в том, что XML-документ, формируемый SQL Server’ом, не имеет главного корневого элемента, без которого документ не может считаться правильно оформленным. Для указания корневого элемента нужно добавить параметр root.

http://server/server_pubs/?sql=select au_fname,au_lname,address from authors where au_fname like 'M%' for xml raw&root=my_root

В ответ будет выдано:

Incorrect syntax near 'M'.

Что ж, опытные пользователи, наверное, сразу бы приметили знак процента в запросе. Он является зарезервированным символом в имени URL, его код равен 25. Учитывая это, перепишем запрос так:

http://server/server_pubs/?sql=select au_fname,au_lname,address from authors where au_fname like 'M%25' for xml raw&root=my_root

Ура! Получилось. Результат будет примерно таким же, как в самом первом примере этой статьи.

На случай, если вам нужно получить результаты в виде обычного HTML, можно создать шаблон преобразования на языке XSL и указать еще один параметр в URL – xsl. В качестве значения параметра нужно указать путь относительно выбранной вами виртуальной директории.

Составим шаблон трансформации:

<?xml version="1.0" ?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

        version="1.0">

 

 <xsl:template match = "*">

  <xsl:apply-templates />

 </xsl:template>

 <xsl:template match = "row">

 <li>

  <table><tr>

   <td><xsl:value-of select = "attribute::au_fname" /></td>

   <td><xsl:value-of select = "attribute::au_lname" />.</td>

   <td>Address: <xsl:value-of select = "attribute::address" /></td>

  </tr></table>

 </li>

 </xsl:template>

 <xsl:template match = "/">

  <html>

  <body>

   <ul>

  <xsl:apply-templates select = "my_root" />

   </ul>

  </body>

  </html>

 </xsl:template>

</xsl:stylesheet>

Более подробную информацию о XSL можно найти в [4].

В ответ на следующий URL-запрос вы получите преобразованный XML-документ.

http://server/server_pubs/?sql=select au_fname,au_lname,address from authors where au_fname like 'M%25' for xml raw&root = my_root&xsl = xsl_for_query.xsl

На самом деле работа с URL-запросами интересна только первые несколько минут. Действительно, очень неудобно возиться со строкой адреса и бесконечными символами процента в ней, к тому же URL-запросы не очень хороши в смысле безопасности. Альтернативой им являются шаблоны.

Шаблоны

Шаблоны в контексте этой статьи являются обычными XML-документами, составленными по определенным правилам. Основным их содержанием является SQL-запрос или вызов хранимой процедуры. Шаблоны также используются для запросов XPath, однако их я коснусь чуть позже. Параметры шаблона задаются в URL-строке. Если они там не указаны, берутся значения по умолчанию из соответствующих тегов <param>.

Шаблоны хранятся на сервере, поэтому в смысле безопасности их использование намного предпочтительнее, чем URL-запросов. Чтобы ISAPI-расширение, которое их обрабатывает, поняло, что вы вызываете шаблон, его нужно поместить в свой виртуальный каталог. Обычно он называется template. Настроить его можно в упоминавшемся ранее мастере "Configure SQL XML Support in IIS" на закладке Virtual Names.

Структура шаблона выглядит так:

<?xml version="1.0" ?>

<your_root xmlns:sql="urn:schemas-microsoft-com:xml-sql"

      sql:xsl="xsl file name">

 <xql:header>

  <xql:param name="your_param_name"> param_value </sql:param>

  <xql:param name="your_param_name"> param_value </sql:param>...n

 </xql:header>

 <sql:query>

  любое SQL-выражение

 </sql:query>

</your_root>

Для форматирования результатов исполнения шаблона может быть использована XSL-трансформация. Для этого необходимо задать атрибут xsl, значение которого есть относительный или полный путь до файла, содержащего шаблон трансформации. Атрибут xsl необязателен, как и раздел header.

Вот как будет выглядеть шаблон, основанный на изрядно уже поднадоевшем вам запросе.

<?xml version="1.0" ?>

<my_root xmlns:sql="urn:schemas-microsoft-com:xml-sql"

     sql:xsl="xsl_for_query.xsl">

 <sql:query>

  select au_fname,au_lname,address

   from authors where au_fname like 'M%' for xml raw

 </sql:query>

</my_root>

В нем используется та же схема преобразования, что и в предыдущем примере. Теперь попробуйте его вызвать (предположим, вы его сохранили под именем first_template.xml):

http://server/server_pubs/template/first_template.xml

Результат получается довольно странным: IE представляет HTML-документ (получаемый при трансформации) в формате XML. Делает он это на вполне законных основаниях, и чтобы результат выдавался все-таки в формате HTML, нужно явно указать тип выходного потока. Это легко сделать с помощью параметра contenttype.

http://server/server_pubs/template/first_template.xml?contenttype=text/html

Достоинства шаблонов очевидны:

Так как шаблон находится на сервере, вы полностью контролируете его содержимое;

Шаблоны намного проще в использовании;

Тело шаблона и используемая схема преобразования скрыты от пользователя;

Шаблоны можно динамически изменять или создавать, что придает интернет-серверу дополнительную гибкость;

С помощью шаблонов можно выполнять запросы XPath, но об этом уже в следующем разделе.

В шаблонах можно использовать следующие атрибуты (рассмотрены наиболее используемые):

client-side-xml – Булева переменная, принимающая значение 0 или 1. Если указывается 1, то при выборке используется форматирование XML-документа на клиенте. Т.е. SQL Server выполняет обычный запрос, передает рекордсет клиенту, и уже там производится формирование документа. Более подробно клиентские курсоры будут рассмотрены в разделе ADO и XML.

ПРИМЕЧАНИЕ

В данном случае клиентом является компьютер, откуда поступает запрос к SQL Server-y, то есть машина, где расположен SQLXMLOLEDB-провайдер. В случае использования ADO – это машина клиента. В случае использования шаблонов – сервер IIS.

nullvalue – позволяет задавать строку, которая в URL-запросе и запросе XPath будет означать NULL.

is-xml – атрибут параметра, принимающий значение 0 и 1, используется в разделе header. По умолчанию он равен 1. Это означает, что значение параметра интерпретируется как фрагмент xml, поэтому, например, < не заменяется. Если задано значение 0, параметр интерпретируется как обычный текст.

Назначение других атрибутов можно найти в MSDN.

Запросы XPath

XPath не рассчитан на работу с реляционными данными. Чтобы использовать XPath-запросы для выборки реляционных данных, необходимо создать схему данных XDR или XSD. XDR была разработана несколько лет назад при активном участии Microsoft, т.к. в то время необходимость в схемах данных была, а, по существу, самих схем не было. С появлением XSD популярность и актуальность применения XDR начали падать.

ПРИМЕЧАНИЕ

Спецификацию XSD можно найти в [5].

Схема данных выполняет две важные функции: задает структуру будущего XML-документа и определяет, какие поля и таблицы должны использоваться при выполнении запроса XPath. Такие схемы называются аннотированными схемами запросов, а атрибуты, связывающие объекты базы данных с XML-узлами – аннотациями. До выхода в свет SQLXML 2.0 можно было использовать только аннотированные схемы на основе SDR [6]. Однако сейчас лучше использовать аннотированные схемы на основе спецификации XSD [7]. Некоторую информацию по преобразованию схем из XDR в более новый формат XSD можно найти в [8].

Вот синтаксис шаблонов с использованием запросов XPath:

<your_root xmlns:sql="urn:schemas-microsoft-com:xml-sql">

 <sql:header>

  <xql:param name="your_param_name"> param_value </sql:param>

  <xql:param name="your_param_name"> param_value </sql:param>...n

 </sql:header>

 <sql:xpath-query mapping-schema="your_schema.xml">

  XPath query

 </sql:xpath-query>

</your_root>

В этом примере аннотированная схема должна находится в файле your_schema.xml. Как видно из синтаксиса, возможно создание параметризированных запросов XPath. Параметр в запросе обозначается начальным символом $.

Рассмотрим пример аннотированной схемы XDR, который будет использоваться для запросов XPath. В результирующем документе будут присутствовать имена, фамилии и адреса всех авторов:

<?xml version="1.0" ?>

<Schema xmlns="urn:schemas-microsoft-com:xml-data"

 xmlns:sql="urn:schemas-microsoft-com:xml-sql"

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

 <ElementType name="address" />

 <ElementType name="Authors" sql:relation="Authors">

  <AttributeType name="au_fname" dt:type="string" />

  <AttributeType name="au_lname" dt:type="string" />

  <attribute type="au_fname"/>

  <attribute type="au_lname" />

  <element type="address" sql:field="address"/>

 </ElementType>

</Schema>

Здесь используется аннотация relation для того, чтобы указать, с какой таблицей будет связан элемент Authors. Дочерние элементы наследуют связь с таблицей, указанной для родительского ElementType. Связывание полей таблицы или представления (view) можно выполнять явно, с использованием аннотации field. В данном примере для элементов AttributeType этого делать не нужно, т.к. отображения на соответствующие поля выполняются автоматически. Однако для дочерних элементов ElementType, которые по умолчанию связываются с таблицами, такая аннотация может быть необходима. Наиболее часто используемые аннотации приведены далее.

Теперь можно перейти к самому шаблону. Предположим, аннотированную схему вы сохранили под именем MySchema.xml.

ПРИМЕЧАНИЕ

IIS различает тип XML-документа только на основе каталога, где он находится. Даже если мы не настроили специальным образом IIS на исполнение схем, их лучше хранить в одном месте. Я рекомендую хранить схемы и шаблоны в разных виртуальных каталогах. Например, template для шаблонов, schema – для схем.

Вот так выглядит шаблон, выбирающий имена, фамилии и адреса всех авторов:

<my_root xmlns:sql="urn:schemas-microsoft-com:xml-sql">

  <sql:xpath-query mapping-schema="../Schema/MySchema.xml">

    /Authors

  </sql:xpath-query>

</my_root>

Так как схемы XDR постепенно вытесняются схемами XSD, перепишем пример с использованием XSD.

ПРЕДУПРЕЖДЕНИЕ

Я полтора дня потерял, когда первый раз пытался выполнить запрос XPath на XSD-схеме. ISAPI-расширение упорно выдавало ошибку «XPath: unable to find /authors in the schema». В конце концов, после непродолжительных консультаций с одним из участников форума сайта www.sql.ru, проблема была решена. Суть ее в следующем: при создании виртуального каталога я использовал оснастку mmc SQL IIS Admin.MSC, которая входит в стандартный комплект MS SQL Server’а и ничего не знает о новых возможностях SQLXML 3.0. Новая оснастка лежит в %Program Files%SQLXML 3.0 и называется sqlisad3.msc. Ее можно запустить из меню Start->Programs->SQLXML 3.0->Configure IIS Support. Всегда пользуйтесь только ею.

Вот список основных отличий XDR от XSD[9], к которому я очень часто обращаюсь:

XDR

XSD

Schema

schema

ElementType

element

AttributeType

attribute

attribute

none

С учетом этого схема будет выглядеть так:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

      xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

 <xsd:element name="authors" sql:relation="authors">

  <xsd:complexType>

   <xsd:attribute name="au_fname" sql:field="au_fname" />

   <xsd:attribute name="au_lname" sql:field="au_lname" />

   <xsd:attribute name="address" sql:field="address" />

  </xsd:complexType>

 </xsd:element>

</xsd:schema>

Здесь явно указаны аннотации, позволяющие связать XML-элементы с таблицей authors и соответствующими полями. В данном случае все они не обязательны, т. к. SQLXML может вывести связи из названий узлов. Вот пример, где аннотации действительно необходимы. Для разнообразия адрес и фамилия вынесены в отдельные элементы:

<?xml version="1.0" encoding="windows-1251" ?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

 xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

 <xsd:element name="Авторы" sql:relation="authors">

  <xsd:complexType>

   <xsd:sequence>

    <xsd:element name="Фамилия" sql:field="au_lname"/>

    <xsd:element nam