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 показаны настройки на компьютере автора.
 |
|
URL-запросы
Используя созданный выше виртуальный каталог, мы можем выполнять запросы к базе данных Northwind, помещая их непосредственно в URL. Чтобы проверить эту возможность в действии, запустим браузер Microsoft Internet Explorer и введем в строке Address следующий URL:
http://terra/northwind?sql= _ SELECT+*+FROM+Customers+FOR+XML+AUTO&root=ROOT
Результат (в виде XML) отображается в браузере так, как показано на рис. 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-документ, где значения полей представлены в виде атрибутов элемента, имя которого соответствует имени запрашиваемой таблицы:
<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545" />
Чтобы получить XML-документ, в котором значения полей представлены дочерними элементами, следует использовать ключевое слово Elements:
http://terra/northwind?sql=SELECT+*+FROM+Customers+FOR+XML+AUTO,ELEMENTS&root=Northwind
Результат выполнения этого запроса показан на рис. 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.
Выполнив этот же запрос с использованием директивы FOR XML AUTO, мы получим структурированный XML-документ, показанный на рис. 3.
<Customers CustomerID="ALFKI" ContactName="Maria Anders"> <Orders OrderID="10643" CustomerID="ALFKI"/> <Orders OrderID="10692" CustomerID="ALFKI"/> <Orders OrderID="10702" CustomerID="ALFKI"/> <Orders OrderID="10835" CustomerID="ALFKI"/> <Orders OrderID="10952" CustomerID="ALFKI"/> <Orders OrderID="11011" CustomerID="ALFKI"/> </Customers> <Customers CustomerID="ANATR" ContactName="Ana Trujillo"> <Orders OrderID="10308" CustomerID="ANATR"/> <Orders OrderID="10625" CustomerID="ANATR"/> <Orders OrderID="10759" CustomerID="ANATR"/> <Orders OrderID="10926" CustomerID="ANATR"/> </Customers>
Поддержка 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
Если же нам необходим более структурированный XML-документ, значения полей в котором хранятся как атрибуты, в директиве FOR XML AUTO необходимо добавить ключевое слово Elements. В результате мы получим документ, показанный на рис. 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-шаблона; изменения показаны выделенным шрифтом.
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl='list2.xsl'>
Вызвав данный XML-шаблон в браузере, мы получим результат трансформации нашего XML-документа в HTML-документ, представленный в виде... XML-документа, показанного на рис. 6.
Чтобы превратить этот документ в реальный HTML-документ, в конце URL необходимо добавить параметр contenttype=text/html. Теперь результат преобразования полностью соответствует нашим ожиданиям (рис. 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.
<ElementType name="Employee" > <AttributeType name="EmpID" /> <AttributeType name="FName" /> <AttributeType name="LName" /> <attribute type="EmpID" /> <attribute type="FName" /> <attribute type="LName" /> </ElementType>
После того как элемент описан, мы должны указать, как его атрибуты отображаются на атрибуты таблицы базы данных. Для этого используем директиву 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"> <ElementType name="Employee" sql:relation="Employees" > <AttributeType name="EmpID" /> <AttributeType name="FName" /> <AttributeType name="LName" /> <attribute type="EmpID" sql:field="EmployeeID" /> <attribute type="FName" sql:field="FirstName" /> <attribute type="LName" sql:field="LastName" /> </ElementType> </Schema>
Теперь мы можем сохранить эту схему в файле 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).
Приведенный выше пример — это самое простое из того, что можно делать с 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.
Отметим, что для создания комплексных 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.
<ROOT xmlns:updg='urn:schames-microsoft-com: xml-updategram'> <updg:sync> <updg:before> <TABLENAME [updg:id='value'] col='value' col='value' .... /> </updg:before> <updg:after> <TABLENAME [updg:id='value'] [updg:at-identity='value'] col='value' col='value' .... /> </updg:after> <updg:sync> </ROOT>
Первое, что бросается в глаза, — это то, что updategrams похожи на рассмотренные нами выше XML-шаблоны. Они также начинаются с корневого элемента и описания пространства имен. Но вместо префикса sql мы указываем префикс updg и связываем его с адресом urn:schames-microsoft-com: xml-updategram. Кроме того, можно заметить новый элемент sync, являющийся контейнером для описания данных до (before) и после (after) операции. Данный блок синхронизации описывает транзакцию: если любая операция внутри блока завершится с ошибкой, будут отменены все операции. Имя таблицы, имена колонок и их значения (до и после операции) используются для определения типа операции — вставка, удаление или обновление. Таким образом, все, что необходимо сделать для выполнения той или иной операции, — это задать соответствующие данные.
После того как мы ознакомились с теорией updategrams, давайте рассмотрим примеры использования этого механизма. Начнем со вставки новой записи в таблицу Employees — добавим нового сотрудника с именем John Smith, занимающего позицию Accounting Manager:
<ROOT xmlns:updg="urn:schemas-microsoft-com: xml-updategram"> <updg:sync> <updg:before> </updg:before> <updg:after> <Employees FirstName='John' LastName='Smith' Title='Accounting Manager' /> </updg:after> </updg:sync> </ROOT>
Обратите внимание на то, как описываются данные в случае операции вставки: блок before пуст, а блок after содержит новые данные.
После того как мы вставили новую запись, мы можем обновить некоторые из ее полей. Например, добавить дату рождения сотрудника:
<ROOT xmlns:updg="urn:schemas-microsoft-com: xml-updategram"> <updg:sync> <updg:before> <Employees EmployeeID='10' /> </updg:before> <updg:after> <Employees BirthDate='1960-10-22' /> </updg:after> </updg:sync> </ROOT>
Обратите внимание, что в этом случае в блоке before мы указываем идентификатор записи, а в блоке after — обновляемые данные. Может возникнуть вполне резонный вопрос: как в случае вставки данных мы узнаем идентификатор новой записи (значение поля EmployeeID для нашей новой записи)? В механизме updategrams для этой цели предусмотрены директивы returnid и at-identity, пример использования которых показан ниже.
<ROOT xmlns:updg="urn:schemas-microsoft-com: xml-updategram"> <updg:sync> <updg:before> </updg:before> <updg:after updg:returnid="EmpID"> <Employees updg:at-identity="EmpID" FirstName='John' LastName='Smith' Title='Accounting Manager' /> </updg:after> </updg:sync> </ROOT>
В случае выполнения такого набора XML-инструкций в результирующем документе будет возвращено значение поля EmployeeID для нашей новой записи:
<ROOT xmlns:updg="urn:schemas-microsoft-com: xml-updategram"> <returnid><EmpID>10</EmpID></returnid> </ROOT>
Наконец, используя механизм updategrams, мы можем удалять записи. Например:
<ROOT xmlns:updg="urn:schemas-microsoft-com: xml-updategram"> <updg:sync> <updg:before> <Employees EmployeeID='10' /> </updg:before> <updg:after> </updg:after> </updg:sync> </ROOT>
В этом случае мы заполняем лишь блок before, оставляя пустым блок after. Это указывает SQL Server на необходимость удаления записей, соответствующих заданному в блоке before критерию.
Завершая обсуждение механизма updategrams, следует сказать несколько слов об использовании параметров. Для задания параметров мы используем префикс updg:param и атрибут name, располагая описание параметров в секции <updg:header>. Например, для обновления данных мы используем следующий код:
<ROOT xmlns:updg="urn:schemas-microsoft-com: xml-updategram"> <updg:header> <updg:param name='EmployeeID'> <updg:param name='LastName'> </updg:header> <updg:sync> <updg:before> <Employees EmployeeID='$EmployeeID' /> </updg:before> <updg:after> <Employees LastName='$LastName' /> </updg:after> </updg:sync> </ROOT>
В приведенном выше шаблоне мы указали два параметра: 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.
КомпьютерПресс 6'2001
