Использование XML совместно с SQL
Использование 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()» можно заменить на точку (.);
Другие сокращения можно найти в спецификации 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:
|
#"67306.files/image001.gif">#"67306.files/image002.gif">
Рисунок 1.
|
Возможность
|
Формирование на клиенте
|
Формирование на сервере
|
|
Использование предиката
GROUP BY и агрегатных функций совместно с FOR XML AUTO (NESTED)
|
Да
|
Нет
|
|
Возможность получения
одновременно нескольких рекордсетов
|
Нет
|
Да
|
|
Задание имен элементов
результирующего документа с помощью псевдонимов таблиц
|
Нет
|
Да
|
Другие отличия вы можете найти в MSDN. Давайте
перейдем к примерам.
Шаблон с агрегатными функциями
Ниже приведен пример использования FOR XML NESTED и
атрибута client-side-xml. Шаблон возвращает документ с названиями городов и
количеством авторов в каждом городе. Атрибут client-side-xml устанавливается в
1, что говорит об использовании клиентского форматирования.
|
<?xml
version="1.0" ?>
<my_root
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query
client-side-xml="1">
select count(*) as
number_of_authors,city
from authors
group by city
order by number_of_authors
desc
for xml nested
</sql:query>
</my_root>
|
Формирование XML-документа на клиенте с помощью ADO
У объекта Command есть свойство ClientSideXML, которое
позволяет формировать XML-документ на клиенте. Рассмотрим пример:
|
<%
Const adCmdText
= 1
Const
adExecuteStream = &H400
Response.ContentType
= "text/xml"
' Creating the
objects
Dim conn,cmd
Set conn =
Server.CreateObject("ADODB.Connection")
Set cmd =
Server.CreateObject("ADODB.Command")
conn.Provider =
"sqlxmloledb"
conn.Open
"Data Source=server;Initial catalog=pubs;Data Provider=sqloledb", _
"user","password"
Dim s
s = "select
count(*) as number_of_authors,city" & _
"from
authors" & _
"group by
city" & _
"order by
number_of_authors desc" & _
"for xml
nested"
Set
cmd.ActiveConnection = c
cmd.Properties("ClientSideXML")
= "True"
cmd.CommandType
= adCmdText
cmd.CommandText
= s
cmd.Properties("Output
Stream") = Response
cmd.Properties("xml
root") = "root"
cmd.Execute , ,
adExecuteStream
Set cmd =
nothing
Set conn = nothing
%>
|
В качестве основного провайдера задан провайдер
SQLXMLOLEDB. Провайдер SQL Server’а задается с помощью строки Data
Provider=sqloledb. Чтобы указать, что используется клиентское форматирование,
свойство ClientSideXML установлено в true. Следует заметить, что это свойство
(т.е. клиентское форматирование) можно использовать только для типа adCmdText.
При вызове хранимых процедур его устанавливать нельзя.
Апдейтаграммы и XML Bulk Load
В этом разделе мы рассмотрим способы изменения данных
(удаление, добавление и правка) с помощью XML-документов. Какие достоинства у
этого метода перед использованием SQL-инструкций непосредственно в шаблоне?
Во-первых, апдейтаграммы намного понятнее простым пользователям или
неосведомленным программистам. Хотя синтаксис написания апдейтаграмм не так уж
и прост, он подчиняется обычным правилам XML и, если вы совсем не разбираетесь
в SQL, апдейтаграммы – лучший выбор. Кроме этого, вы просто не имеете другой
столь же эффективной альтернативы, как XML Bulk Load для загрузки больших,
очень больших объемов XML-данных. Но обо всем по порядку.
Апдейтаграммы
Апдейтаграммы – это особый тип шаблонов, в котором
вместо запроса на выборку данных хранится информация, необходимая для изменения
данных. Синтаксис их таков:
|
<your_root
xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
[mapping-schema="your_schema.xml"]>
<updg:header>
<updg:param
name="param1_name" />
<updg:param
name="param2_name" />
</updg:header>
<updg:sync>
<updg:before>
xml-узлы
</updg:before>
<updg:after
[updg:returnid="your_id"]>
xml-узлы
</updg:after>
</updg:sync>
</your_root>
|
Как видно из синтаксиса, апдейтаграмма может иметь
параметры. Они, как обычно, задаются в URL и, как обычно, могут опускаться.
Раздел header не является обязательным, но на практике без него обойтись
трудно. Раздел before, по существу, определяет запись, над которой будет
производиться действие. Он обязателен для изменения или удаления данных. В
случае вставки его можно опустить. Раздел after определяет, как будет выглядеть
найденная запись после выполнения апдейтаграммы. В случае удаления его
указывать не нужно. Раздел sync определяет границы транзакции и буквально
трансформируется в команды begin trans и commit trans. В случае неудачного
выполнения шаблона транзакция откатывается с помощью rollback trans.
|
ПРИМЕЧАНИЕ
Все аспекты работы апдейтаграмм хорошо
видны в профайлере. Если у вас возникают вопросы, рекомендую сразу же им
воспользоваться.
|
В каком случае может возникнуть ошибка? Причин много:
Раздел before не может однозначно определить запись,
над которой будет производиться действие. Например, если вы захотите удалить
все записи, у которых поле age равно 33, то в ответ получите ошибку Ambiguous
delete, unique identifier required. Другая ошибка возникает, когда такую запись
невозможно найти совсем. В этом случае генерируется сообщение Empty delete, no
deletable rows found для операции удаления, или Empty update, no updatable rows
found для операции обновления.
Откат транзакции в триггере.
Ошибка ограничения (constraint violation).
Для того чтобы SQL Server понял, какие таблицы и поля
ему изменять, вы можете использовать аннотированные схемы XDR или XSD. Если
схема не используется, по умолчанию названия элементов соответствуют названиям
таблиц, названия и значения атрибутов – названию и значению полей
соответственно.
Добавление данных
Для вставки данных используется раздел after. Раздел
before опускается. Если вы хотите получить на выходе значение колонки identity,
укажите атрибут returned (и атрибут at-identity, см. далее). Для элемента,
обозначающего таблицу, вы можете дополнительно указать три атрибута:
id – будет рассмотрен в разделе Обновление данных;
at-identity – позволяет получить в результирующий
XML-документ значение вставленного поля для колонки identity. Например, пусть
имеется такая таблица
|
create table test1(_id int
identity,fld1 int)
|
Вот шаблон, который вставляет в нее данные:
|
<ROOT
xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync>
<updg:after
updg:returnid="ident_field">
<test1
fld1="23" updg:at-identity="ident_field"/>
</updg:after>
</updg:sync>
</ROOT>
|
Результатом выполнения команды
«#"#">http://www.rsdn.ru/
Похожие работы на - Использование XML совместно с SQL
|