Microsoft SQL Server 2000: поддержка XML

Алексей Федоров

В этой статье мы ознакомимся с некоторыми новыми возможностями Microsoft SQL Server 2000, связанными с поддержкой языка XML. Мы рассмотрим использование SQL-запросов через URL, использование XML-шаблонов, XDR-схем и XPath-запросов, а также приведем некоторые примеры работы с механизмом updategrams, позволяющим применять XML-шаблоны для вставки, удаления и обновления данных.

Данная статья подразумевает, что читатели знакомы с XML, XSL и SQL-запросами.

Настройка Web-сервера

Прежде чем мы сможем начать использование поддержки XML в Microsoft SQL Server 2000, нам необходимо произвести ряд настроек Web-сервера. Для этого следует выполнить команду Programs / Microsoft SQL Server / Configure SQL XML Support in IIS и указать ряд параметров в панели IIS Virtual Directory Management for SQL Server.

Щелкните мышью по элементу Default Web Site и выполните команду New / Virtual Directory. Появится диалоговая панель New Virtual Directory Properties, состоящая из ряда вкладок. В табл. 1 показаны настройки на компьютере автора.

Таблица 1

Вкладка General
Virtual Directory Name	Northwind
Local Path	c:\inetpub\northwind
Вкладка Security
Установите параметры согласно вашему способу обращения к SQL Server
Вкладка Data Source
SQL Server	local
Database	Northwind
Вкладка Settings
Allow URL Queries
Allow Template Queries
Allow XPath
Allow POST
Вкладка Virtual Names
Виртуальный каталог northwind	Тип dbobject
Виртуальный каталог templates	Тип template, физический каталог c:\inetpub\northwind\templates
Вкладка Advanced
На этой вкладке можно установить дополнительные параметры OLE DB-провайдера для Microsoft SQL Server. Подробнее см. Books Online

URL-запросы

Используя созданный выше виртуальный каталог, мы можем выполнять запросы к базе данных Northwind, помещая их непосредственно в URL. Чтобы проверить эту возможность в действии, запустим браузер Microsoft Internet Explorer и введем в строке Address следующий URL:

http://terra/northwind?sql= _
SELECT+*+FROM+Customers+FOR+XML+AUTO&root=ROOT

Результат (в виде XML) отображается в браузере так, как показано на рис. 1.

Рис. 1

Рассмотрим указанный нами адрес более подробно. Он состоит из следующих частей:

типа протокола - http;
имени сервера и виртуального каталога - terra/northinwd. Если вы используете локальный сервер, то в качестве имени сервера можете указать localhost;
параметра sql;
собственно SQL-запроса -

SELECT+*+FROM+Customers+FOR+XML+AUTO&root=ROOT

Этот запрос практически не отличается от обычного запроса, выполняемого нами в коде или в Query Analyzer. Заметим, что пробелы в нем заменены на знак «+» (это требование форматирования URL) и в конце запроса добавлена директива FOR XML AUTO. Собственно, эта директива и указывает SQL Server на необходимость генерации XML вместо обычного набора данных. Наш URL-запрос завершается заданием корневого элемента XML-документа - это необходимо в тех случаях, когда мы используем директиву FOR XML AUTO и получаем более одной «записи».

Как видно из приведенного примера, при использовании директивы FOR XML AUTO мы получаем XML-документ, где значения полей представлены в виде атрибутов элемента, имя которого соответствует имени запрашиваемой таблицы:

Чтобы получить XML-документ, в котором значения полей представлены дочерними элементами, следует использовать ключевое слово Elements:

http://terra/northwind?sql=SELECT+*+FROM+Customers+FOR+XML+AUTO,ELEMENTS&root=Northwind

Результат выполнения этого запроса показан на рис. 2.

Рис. 2

Одна из записей в таблице Customers будет представлена в виде следующего XML-элемента и его дочерних элементов:

<Customers>
<CustomerID>ALFKI</CustomerID>
<CompanyName>Alfreds Futterkiste</CompanyName>
<ContactName>Maria Anders</ContactName>
<ContactTitle>Sales Representative</ContactTitle>
<Address>Obere Str. 57</Address>
<City>Berlin</City>
<PostalCode>12209</PostalCode>
<Country>Germany</Country>
<Phone>030-0074321</Phone>
<Fax>030-0076545</Fax>
</Customers>

Отметим, что директива FOR XML AUTO создает структурированный XML-документ в тех случаях, когда используются более сложные запросы. Например, запрос:

SELECT Customers.CustomerID, Customers.ContactName,
Orders.OrderID, Orders.CustomerID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID

возвращает набор записей из двух таблиц, содержащий идентификаторы всех заказов, выполненных всеми клиентами. Фрагмент такого набора записей показан в табл. 2.

Таблица 2

CustomerID	ContactName	OrderID	CustomerID
ALFKI	Maria Anders	10643	ALFKI
ALFKI	Maria Anders	10692	ALFKI
ALFKI	Maria Anders	10702	ALFKI
ALFKI	Maria Anders	10835	ALFKI
ALFKI	Maria Anders	10952	ALFKI
ALFKI	Maria Anders	11011	ALFKI
ANATR	Ana Trujillo	10308	ANATR
ANATR	Ana Trujillo	10625	ANATR
ANATR	Ana Trujillo	10759	ANATR
ANATR	Ana Trujillo	10926	ANATR
...	...	...	...

Выполнив этот же запрос с использованием директивы FOR XML AUTO, мы получим структурированный XML-документ, показанный на рис. 3.

Рис. 3

Поддержка XML в Microsoft SQL Server 2000 не ограничивается возможностью выполнения простых URL-запросов. Помимо этого мы можем использовать хранимые процедуры и XML-шаблоны. Эти темы мы рассмотрим в следующих разделах.

Использование хранимых процедур

Запустим Query Analyzer и введем код, который определяет хранимую процедуру sp_GetXML, возвращающую данные о клиенте, имя которого задано первой буквой:

IF EXISTS (SELECT name FROM sysobjects
WHERE name='sp_GetXML' AND type='P')
DROP PROCEDURE sp_getXML
GO

CREATE PROCEDURE sp_GetXML(
@CID varchar(5)
)
AS
BEGIN
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE CustomerID LIKE @CID + '%'
FOR XML AUTO
END

Как и в случае с рассмотренными выше SQL-запросами, мы используем директиву FOR XML AUTO для указания SQL Server на то, что мы хотим получить XML-документ. Чтобы выполнить хранимую процедуру в URL-запросе, необходимо указать в браузере следующий адрес (рис. 4):

http://terra/northwind?sql=exec+sp_GetXML+'A'&root=Northwind

Рис. 4

Если же нам необходим более структурированный XML-документ, значения полей в котором хранятся как атрибуты, в директиве FOR XML AUTO необходимо добавить ключевое слово Elements. В результате мы получим документ, показанный на рис. 5.

Рис. 5

Отметим, что использование хранимых процедур более предпочтительно непосредственному указанию SQL-запроса в URL. В первую очередь по соображениям безопасности, во вторую - с точки зрения универсальности кода. Как видно в последнем примере, для изменения XML-документа достаточно изменить лишь код хранимой процедуры - сам URL-запрос остается таким же. Впрочем, как только мы расскажем, что ту или иную базу данных можно запрашивать через URL, кому-нибудь обязательно захочется выполнить команды INSERT, UPDATE или DELETE…

Использование XML-шаблонов

Чтобы полностью обезопасить нашу базу данных от непредвиденной активности, но сохранить при этом возможность генерации XML-документов, мы должны использовать XML-шаблоны. Идея использования данных шаблонов заключается в том, что мы помещаем SQL-запрос в XML-документ и запрещаем выполнение сервером запросов, находящихся в URL. Для этого необходимо отключить опцию Allow URL Queries на вкладке Settings в панели New Virtual Directory Properties.

Затем следует перейти на вкладку Virtual Names и задать новый каталог - в нашем примере это будет каталог Templates типа template, находящийся в физическом каталоге C:\Inetpub\Northwind\Templates.

Теперь мы готовы приступить к использованию XML-шаблонов. Начнем с создания шаблона, в котором воспользуемся написанным нами ранее SQL-шаблоном:

SELECT Customers.CustomerID, Customers.ContactName,
Orders.OrderID, Orders.CustomerID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
XML-шаблон для выполнения этого запроса будет выглядеть так:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT Customers.CustomerID, Customers.ContactName,
Orders.OrderID, Orders.CustomerID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
FOR XML AUTO
</sql:query>
</ROOT>

Выделенным шрифтом показаны различия между обычным SQL-запросом и XML-шаблоном на его основе. В этом XML-документе мы используем пространство имен sql (urn:schemas-microsoft-com:xml-sql) и элемент query данного пространства имен. Для выполнения такого шаблона и получения XML-документа необходимо указать имя шаблона в URL (шаблон находится в файле LIST1.XML в подкаталоге Templates):

http://terra/northwind/templates/list1.xml

Результат выполнения этого запроса будет точно таким же, что и при использовании SQL-запроса в URL, но сам запрос базируется на XML-шаблоне. Как и в случае с хранимыми процедурами, мы можем изменить текст шаблона, не меняя самого URL-запроса. Например, если мы захотим структурировать XML-документ и добавить ключевое слово Elements, мы должны сделать это в XML-шаблоне.

XML-шаблоны имеют еще одно преимущество по сравнению с обычными SQL-запросами в URL и использованием хранимых процедур: в шаблонах мы можем указать XSL-шаблон и таким образом вместо XML- получать, например, HTML-документы, текстовые документы и т.п. Ниже мы рассмотрим пример использования XML- и XSL-шаблонов для генерации HTML-документов.

Сначала создадим еще один XML-шаблон, на этот раз более простой, чем мы рассматривали выше. Вот текст этого шаблона:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT FirstName, LastName, Title, Notes
FROM Employees
FOR XML AUTO
</sql:query>
</ROOT>

Сохраним этот шаблон в файле с именем LIST2.XML и выполним его. Как и следовало ожидать, мы получаем XML-документ. Для того чтобы превратить его в HTML-документ, необходимо создать XSL-шаблон. В нашем примере мы будем отображать данные о каждом сотруднике в виде одного ряда в таблице. Вот текст XSL-шаблона:

<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet xmlns:xsl='http://www.w3.org/TR/WD-xsl'
version='1.0'>
<xsl:template match='*'>
<xsl:apply-templates />
</xsl:template>
<xsl:template match='Employees'>
<TR>
<TD><xsl:value-of select='@FirstName' /></TD>
<TD><xsl:value-of select='@LastName' /></TD>
<TD><xsl:value-of select='@Title' /></TD>
<TD><xsl:value-of select='@Notes' /></TD>
</TR>
</xsl:template>
<xsl:template match='/'>
<HTML>
<BODY>
<TABLE BORDER='1'>
<TR>
<TH COLSPAN='4'>Employees</TH>
</TR>
<xsl:apply-templates select='ROOT' />
</TABLE>
</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>

Для того чтобы указать используемый XSL-шаблон, необходимо изменить первую строку описанного выше XML-шаблона; изменения показаны выделенным шрифтом.

Вызвав данный XML-шаблон в браузере, мы получим результат трансформации нашего XML-документа в HTML-документ, представленный в виде... XML-документа, показанного на рис. 6.

Рис. 6

Чтобы превратить этот документ в реальный HTML-документ, в конце URL необходимо добавить параметр contenttype=text/html. Теперь результат преобразования полностью соответствует нашим ожиданиям (рис. 7):

Рис. 7

Отметим, что XSL-шаблоны можно указывать и в качестве параметров. Для этого в конец URL следует добавить имя файла в виде &xsl=list2.xsl.

Завершим наше рассмотрение использования XML-шаблонов кратким примером использования параметров в шаблонах. Вернемся к нашему предыдущему шаблону:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT FirstName, LastName, Title, Notes
FROM Employees
FOR XML AUTO
</sql:query>
</ROOT>

Он содержит SQL-запрос, возвращающий данные о каждом сотруднике в таблице Employees. Если мы хотим получать данные только об одном сотруднике, нам необходимо задать в нашем XML-шаблоне параметры. Для этого надо добавить элемент <sql:header>, описать передаваемый параметр посредством элемента <sql:param> и изменить код самого SQL-запроса. Ниже выделенным шрифтом показаны изменения, внесенные в наш XML-шаблон.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
<sql:param name='EmpID'>1</sql:param>
</sql:header>
<sql:query>
SELECT FirstName, LastName, Title, Notes
FROM Employees
WHERE EmployeeID = @EmpID
FOR XML AUTO
</sql:query>
</ROOT>

Теперь мы можем указывать идентификатор сотрудника при вызове нашего шаблона:

http://terra/northwind/templates/list2a.xml?EmpID=2

Таким образом, механизм XML-шаблонов предоставляет более гибкие средства для извлечения данных, в том числе и возможность их трансформации.

XDR-схемы и XPath-запросы

Помимо рассмотренных выше способов извлечения данных, SQL Server 2000 поддерживает XPath-запросы, называемые также XPath-представлениями. XPath - это язык запросов, позволяющий выбирать отдельные элементы XML-документа. Более подробно язык XPath описан в документе «XML Path Language (XPath Version 1.0: W3C Recommendation 16 November 1999)», доступном на Web-сайте World Wide Web Consortium по адресу: http://www.w3.org/tr/xpath.

Для создания подобного запроса мы должны выполнить два действия. Во-первых, создать XDR-схему, описывающую, как элементы документа отображаются на поля таблицы базы данных. Во-вторых, написать XML-шаблон, содержащий XPath-запрос на основе данной схемы.

Для нашего примера создадим XDR-схему, описывающую отображение полей таблицы Employees на элементы XML-документа. Шаблон для нашей схемы выглядит следующим образом:

<?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">
...
</Schema>

Здесь мы задаем пространство имен urn:schemas-microsoft-com:xml-data, используемое для описания данных, и определяем два пространства имен, используемых в нашем документе, - sql (urn:schemas-microsoft-com:xml-sql) и dt (urn:schemas-microsoft-com:datatypes)

Добавим в наш шаблон элемент <Employee>, имеющий атрибуты EmpID, FName и LName.

После того как элемент описан, мы должны указать, как его атрибуты отображаются на атрибуты таблицы базы данных. Для этого используем директиву sql:relation, указывающую таблицу базы данных, и директиву sql:field, указывающую поле, на которое отображается атрибут. Полная XDR-схема для нашего примера показана ниже.

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">

Теперь мы можем сохранить эту схему в файле LIST3.XDR и написать XPath-представление, которое будет применять данную схему для извлечения данных. Сначала создадим простой запрос, использующий фиксированный параметр:

<Northwind xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:xpath-query mapping-schema='list3.xdr'>
/Employee
</sql:xpath-query>
</Northwind>

Отметим, что директива sql:xpath-query необходима для указания используемой нами XDR-схемы. Если мы вызовем это представление через URL - http://www.terra/northwind/templates/list3.xml, то получим XML-документ, содержащий список всех сотрудников (поля EmployeeID, FirstName, LastName таблицы Employees), в котором каждый из них представлен отдельным элементом Employee (рис. 8).

Рис. 8

Приведенный выше пример - это самое простое из того, что можно делать с XPath-представлениями. Отметим, что в отличие от обычных SQL-запросов через URL мы получаем возможность определять структуру XML-документа и названия элементов и атрибутов. Эта функциональность намного проще использования директивы FOR XML EXPLICIT.

Изменим XPath-запрос на следующий:

/Employee[@FName='Nancy']

Выполнив данное представление, мы получим данные об одной сотруднице - Nancy Davolio. Теперь, если мы хотим передавать нашему представлению параметры, нужно изменить его исходный текст на следующий:

<Northwind xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:header>
<sql:param name='FName'>Nancy</sql:param>
</sql:header>
<sql:xpath-query mapping-schema='list3.xdr'>
/Employee[@FName=$FName]
</sql:xpath-query>
</Northwind>

Обратите внимание на то, что мы добавили описание параметра (директива sql:param) и изменили текст XPath-запроса. Для вызова данного представления через URL введите следующий адрес:

http://terra/northwind/templates/list3.xml?FName=Andrew

Результат выполнения нашего запроса - данные о сотруднике с именем Andrew - показан на рис. 9.

Рис. 9

Отметим, что для создания комплексных XDR-схем можно воспользоваться утилитой XML View Mapper, бесплатно распространяемой фирмой Microsoft. Подробное рассмотрение XDR-схем и XDR-представлений - тема отдельной статьи, которая будет опубликована в ближайших номерах.

Вставка, удаление и обновление данных

Все операции с XML-документами, рассмотренные выше, позволяли нам извлекать данные, указывая тот или иной критерий. Ниже мы расскажем, как воспользоваться поддержкой XML в Microsoft SQL Server 2000 для вставки, удаления и обновления данных, другими словами - как можно выполнить операции INSERT, UPDATE и DELETE.

Для выполнения операций вставки, удаления и обновления данных Microsoft SQL Server 2000 поддерживает механизм updategrams - специальный XML-синтаксис, позволяющий описать данные до и после операции.

Данный механизм находится в состоянии бета-тестирования. Для того чтобы им воспользоваться, необходимо загрузить с Web-сайта фирмы Microsoft пакет Microsoft SQL Server XML Tools и установить его на компьютере, где инсталлирован SQL Server 2000.

Идея механизма updategrams очень проста: если описанные данные до операции отличаются от данных после операции, выполняется обновление данных. Например, если блок «до» содержал данные, а блок «после» - нет, данные удаляются, если данные в блоках «до» и «после» различны - выполняется обновление, а если данные присутствуют только в блоке «после», то выполняется вставка данных.

Ниже показан шаблон, используемый механизмом updategrams.

Первое, что бросается в глаза, - это то, что updategrams похожи на рассмотренные нами выше XML-шаблоны. Они также начинаются с корневого элемента и описания пространства имен. Но вместо префикса sql мы указываем префикс updg и связываем его с адресом urn:schames-microsoft-com: xml-updategram. Кроме того, можно заметить новый элемент sync, являющийся контейнером для описания данных до (before) и после (after) операции. Данный блок синхронизации описывает транзакцию: если любая операция внутри блока завершится с ошибкой, будут отменены все операции. Имя таблицы, имена колонок и их значения (до и после операции) используются для определения типа операции - вставка, удаление или обновление. Таким образом, все, что необходимо сделать для выполнения той или иной операции, - это задать соответствующие данные.

После того как мы ознакомились с теорией updategrams, давайте рассмотрим примеры использования этого механизма. Начнем со вставки новой записи в таблицу Employees - добавим нового сотрудника с именем John Smith, занимающего позицию Accounting Manager:

Обратите внимание на то, как описываются данные в случае операции вставки: блок before пуст, а блок after содержит новые данные.

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

Обратите внимание, что в этом случае в блоке before мы указываем идентификатор записи, а в блоке after - обновляемые данные. Может возникнуть вполне резонный вопрос: как в случае вставки данных мы узнаем идентификатор новой записи (значение поля EmployeeID для нашей новой записи)? В механизме updategrams для этой цели предусмотрены директивы returnid и at-identity, пример использования которых показан ниже.

В случае выполнения такого набора XML-инструкций в результирующем документе будет возвращено значение поля EmployeeID для нашей новой записи:

Наконец, используя механизм updategrams, мы можем удалять записи. Например:

В этом случае мы заполняем лишь блок before, оставляя пустым блок after. Это указывает SQL Server на необходимость удаления записей, соответствующих заданному в блоке before критерию.

Завершая обсуждение механизма updategrams, следует сказать несколько слов об использовании параметров. Для задания параметров мы используем префикс updg:param и атрибут name, располагая описание параметров в секции <updg:header>. Например, для обновления данных мы используем следующий код:

В приведенном выше шаблоне мы указали два параметра: EmployeeID и LastName. При вызове шаблона мы задаем параметры так:

http://terra/northwind/templates/emp_update.xml?EmployeeID=1&LastName=Johnson

Помимо рассмотренных здесь способов использования механизма updategrams для вставки, обновления и удаления данных можно выполнять более комплексные операции - изменять более одной записи в рамках одного шаблона, использовать более одной таблицы и задавать XDR-схемы для указания соответствия полей. Эти, а также другие возможности Microsoft SQL Server XML Tools - темы отдельной статьи.

Заключение

В данной статье мы ознакомились с некоторыми новыми возможностями Microsoft SQL Server 2000. Мы рассмотрели основные шаги для настройки Web-сервера, синтаксис URL-запросов, использование хранимых процедур и XML-шаблонов, а также извлечение данных с помощью XDR-схем и XPath-запросов. Далее мы поговорим об основных операциях, которые можно выполнять с помощью механизма updategrams.

Разумеется, в рамках одной статьи невозможно полностью раскрыть такую комплексную тему, как поддержка XML в Microsoft SQL Server 2000. В следующих номерах мы ознакомимся с утилитой для создания XDR-схем, а также рассмотрим вопросы, связанные с использованием поддержки XML в Microsoft SQL Server 2000 из ASP-страниц, и опишем, как эта технология поддерживается в ASP. Net и в рамках всей платформы Microsoft. Net.

Microsoft SQL Server 2000: поддержка XML

Оглавление