ADO и XML

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

С выходом версии SQL Server 2000 корпорация Microsoft стала претворять в жизнь свой новый девиз: обеспечивать доступ к данным и приложениям «в любом месте, в любое время и на любом устройстве». В центре этой стратегии находится интегрированная в SQL Server 2000 поддержка XML. Способность XML к «самоописанию» и совместимость со многими платформами предоставляют SQL Server возможность легко обмениваться данными с приложениями Web и другими системами. В данной статье я расскажу о том, как OLE DB и ADO 2.6 поддерживают три ключевые возможности XML: извлечение XML из хранимых процедур, исполнение шаблонов и выполнение запросов XPath к представлениям XML.

Оборот FOR XML

Прежде чем приступить к иследованию механизмов использования ADO для извлечения XML, напомню, как SQL Server 2000 генерирует XML. В SQL Server 2000 для предложения SELECT языка T-SQL введено расширение в виде оборота FOR XML. При добавлении оборота FOR XML в предложение SELECT, возвращаемый SQL Server результатирующий набор данных будет отформатирован как XML с учетом параметров, указанных в предложении. Наиболее важным аргументом для оборота FOR XML является параметр режима XML, XML mode, который может иметь значения RAW (без обработки), AUTO (автоматический), EXPLICIT (с явным указанием параметров). Синтаксис предложения SELECT с оборотом FOR XML выглядит следующим образом:

SELECT ...

FROM ...

[WHERE]

FOR XML RAW | AUTO | EXPLICIT [,XMLDATA] [,ELEMENTS] [,BINARY Base64]

Чтобы увидеть, как работают три упомянутых режима XML, и каковы результаты их применения, пройдем последовательно по каждому режиму.

Режим XML RAW. Режим RAW генерирует неиерархический текст XML, в котором каждая строка возвращаемого набора данных преобразуется в один элемент, строку XML, причем столбцы трансформируются в атрибуты XML. Режим RAW лучше всего использовать в тех случаях, когда клиентский код ожидает встретить плоские документы XML и ищет элементы-строки. В отличие от режима AUTO режим RAW позволяет применять оборот GROUP BY и проводить агрегирование. Все режимы XML позволяют возвращать схему XML Data Reduced (XDR), для чего после оборота FOR XML следует поставить аргумент XMLDATA. Схема XDR представляет собой разновидность XML, в которой описываются структура и содержание документа XML. Схема XDR позволяет клиентскому коду считывать информацию о типе данных, так что код может должным образом форматировать данные. Схема XDR предоставляет больше информации, чем файлы Document Type Definition (DTD), и с ней легче работать программам синтаксического разбора. Заметим, что схема XDR была предложена корпорацией Microsoft в качестве определения схемы XML. Однако консорциум World Wide Web Consortium (W3C) не принял XDR в качестве стандартного определения. Представители Microsoft заявили, что корпорация будет поддерживать то определение схемы XML, которое примет консорциум W3C, когда оно станет доступным. Более подробную информацию об определениях схемы XML можно найти в статье "XML Schema Part 2: Datatypes" на сайте http://www.w3.org/tr/xmlschema-2.

Режим XML AUTO. В режиме AUTO генерируется иерархический документ путем преобразования в элемент каждой таблицы, на которую ссылается предложение SELECT. По умолчанию в режиме AUTO каждый столбец преобразуется в атрибут, если только вы не примените аргумент ELEMENTS для создания подэлементов. Не забывайте, что режим AUTO не поддерживает оборота GROUP BY, а порядок следования столбцов в предложении SELECT задает порядок вложения атрибутов. Режим XML AUTO позволяет применять псевдонимы таблиц и столбцов в качестве имен элементов или атрибутов. Однако по умолчанию именами элементов и атрибутов являются названия таблиц и столбцов, а не их псевдонимы. Использование аргумента BINARY Base64 обеспечивает возврат образа или двоичных данных в двоичном формате с кодировкой base64. Если аргумент BINARY Base64 не используется, то режим XML AUTO возвращает URL, к которому можно обратиться с запросом для получения двоичных данных.
Режим XML EXPLICIT. Режим EXPLICIT является наиболее сложным и мощным режимом XML. Как следует из названия, при работе в режиме EXPLICIT необходимо явным образом определить схему возвращаемого XML. Это производится путем создания виртуальной таблицы, которую SQL Server переводит в XML. В силу чрезвычайной гибкости данного режима он особенно удобен для формирования документов с иерархической структурой. Режим EXPLICIT позволяет опеределить каждый столбец как атрибут или элемент, и даже создать элементы, не представленные в базе данных. При работе в этом режиме необходимо предварять результат двумя столбцами, Tag и Parent, которые и образуют иерархическую структуру результирующего документа XML. Как и при использовании двух других режимов, обязательно следует определить названия элементов и атрибутов в предложении SELECT.

Используя оборот FOR XML, можно легко преобразовать результирующий набор в XML. Однако этот оборот можно применять лишь в предложениях, которые возвращают данные непосредственно клиенту. Нельзя использовать оборот FOR XML в определениях представлений или задаваемых пользователем функций (UDF); во вложенных предложениях SELECT; в хранимых процедурах, которые манипулируют результирующими наборами данных; в предложениях INSERT, а также в предложениях с оборотом COMPUTE BY.

ЛИСТИНГ 1: Хранимая процедура usp_GetCatTotals.

CREATE PROCEDURE usp_GetCatTotals
@CatID int,
@StartDate smalldatetime,
@EndDate smalldatetime

AS

SELECT 1 AS Tag, NULL AS Parent,
  @CatID AS [OrderTot!1!CatID],
  @StartDate AS [OrderTot!1!StartDate],
  @EndDate AS [OrderTot!1!EndDate],
  NULL AS [Product!2!ProductID], 
  NULL  AS [Product!2!ProductName], 
  NULL AS [Product!2!TotalUnits!element]

UNION

SELECT 2, 1,
  NULL,
  NULL,
  NULL,
  d.ProductID,
  d.ProductName,
  SUM(b.UnitPrice)
FROM Orders a JOIN [Order Details] b ON a.OrderID = b.OrderID
JOIN Products d ON d.ProductID = b.ProductID
WHERE d.CategoryID = @CatID
AND a.OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY d.ProductID, d.ProductName
ORDER BY [Product!2!ProductName]
FOR XML EXPLICIT

GO

Возврат XML при помощи ADO

Теперь рассмотрим хранимую процедуру, которая применяет ADO для возврата XML в клиентское приложение. В листинге 1 показана хранимая процедура usp_GetCatTotals, которая возвращает документ XML. В этом документе проводится агрегирование подробной информации о заказах из базы данных NorthWind для указанной категории товара и заданного периода времени. Процедура использует режим EXPLICIT для построения заказной схемы, в вершине которой располагается элемент OrderTot. Этот элемент содержит атрибуты, которые отражают параметры категории товара (categoryID), даты начала и конца временного интервала. Эти параметры клиентское приложение передает хранимой процедуре. Такой подход позволяет каждому клиентскому приложению, которое вызывает эту процедуру, определять, каким образом SQL Server должен формировать результирующий набор. На рисунке 1 показан документ XML, сформированный процедурой usp_GetCatTotals.

Рис.1

На рисунке 2 показано приложение, которое применяет все три подхода, описанные в данной статье, для получения XML из SQL Server 2000 при помощи ADO. Приложение, которое я написал на Visual Basic (VB) 6.0, использует ADO 2.6 (именно эта версия поставляется с SQL Server 2000) и SQLOLEDB в качестве поставщика OLE DB для SQL Server. Это приложение нетрудно переложить для Active Server Pages (ASP) без потери функциональных возможностей.

Рис.2

В листинге 2 продемонстрирован первый поддход, который состоит в том, чтобы просто вызвать хранимую процедуру usp_GetCatTotals для получения документа XML, который можно было бы просматривать с помощью обычного Web-браузера. Как и при вызове любой хранимой процедуры, сначала необходимо создать объект ADO Connection, чтобы установить соединение с базой данных. Затем следует создать объект Command для формирования вызова хранимой процедуры, как показано в метке А листинга 2. В рассматриваемом примере вызывающая процедура OpenProc имеет три входных параметра: pCatID, pStart и pEnd. Эти параметры передаются процедуре через объекты ADO Parameter, которые создаются с помощью метода CreateParameter объекта Command.

ЛИСТИНГ 2: Процедура VB, которая для получения XML вызывает usp_GetCatTotals.

Private Sub OpenProc(ByVal pCatID As Long, ByVal pStart As Date, ByVal pEnd As Date)

Dim cnConn As Connection
Dim strConn As String
Dim cmCmd As Command
Dim strQuery As String
Dim sResponseStream As Stream

BEGIN CALLOUT A
Set cnConn = New Connection

strConn = "Provider=SQLOLEDB;Data Source=ssosa\sql2000;Initial 
Catalog=Northwind;trusted_connection=yes"
cnConn.ConnectionString = strConn
cnConn.Open

Set cmCmd = New Command
Set cmCmd.ActiveConnection = cnConn

With cmCmd
   CommandType = adCmdStoredProc
   CommandText = "usp_GetCatTotals"
   Parameters.Append .CreateParameter("@CatID", adInteger, adParamInput, 4, pCatID)
   Parameters.Append .CreateParameter("@Start", adDBDate, adParamInput, 8, pStart)
   Parameters.Append .CreateParameter("@End", adDBDate, adParamInput, 8, pEnd)
End With
END CALLOUT A

BEGIN CALLOUT B
` Создать поток для обработки отклика 
Set sResponseStream = New Stream
sResponseStream.Open

` Ответный объект может находиться на странице ASP 
cmCmd.Properties("Output Stream") = sResponseStream
END CALLOUT B

cmCmd.Execute , , adExecuteStream
   
BEGIN CALLOUT C
` Загрузить XML в синтаксический анализатор
mobjXML.Load sResponseStream

Call ShowXML
END CALLOUT C

Set cnConn = Nothing
Set cmCmd = Nothing
Set sResponseStream = Nothing

End Sub

После того как будет создан объект Command, необходимо создать объект, который будет принимать результирующий документ XML. Поскольку выходной набор данных хранимой процедуры usp_GetCatTotals не является объектом ADO Recordset, а представляет собой документ XML, ADO возвращает данные через потоковый объект Stream. Объект Stream, который появился в версии ADO 2.5 вместе с объектом Record, позволяет ADO манипулировать данными, используя интерфейс IStream COM. Эта возможность позволяет ADO взаимодействовать с источниками данных, которые возвращают полуструктурированную информацию, такую как данные XML или бинарные данные. Чтобы проинструктировать ADO о том, что возвращать данные следует через объект Stream, сначала необходимо создать и открыть экземпляр объекта Stream. Затем следует указать объекту Command на объект Stream, используя для этого динамическое свойство Output Stream объекта Command. Как это сделать, показывает метка В листинга 2. Если используется объект, поддерживающий интерфейс Istream, то можно также применять и любой компонент СОМ, который тоже поддерживает интерфейс Istream. В рассматриваемом примере для отправки результатов работы объекта Command непосредственно в приложение с браузером использован объект ASP Response, который поддерживает интерфейс IStream. После этого процедура вызывает объект Command, передавая ему константу adExecuteStream, которая сообщает ADO о том, что результаты следует пересылать через выходной поток.

Чтобы извлечь документ XML из выходного потока и поместить его в строку, можно было бы воспользоваться принадлежащим объекту Stream методом ReadText. Однако в рассматриваемом примере процедура VB применяет программу синтаксического разбора XML под названием Microsoft XML Parser (MSXML) 2.6 для загрузки документа XML. После этого его можно просматривать при помощи Web-браузера, как показывает метка C листинга 2. Процедура VB воспользовалась объектом DOMDocument26, расположенным на уровне форм в объектной модели документа (Document Object Model, DOM). Экземпляр этого объекта порождается событием Load формы. Затем процедура использует метод Load, чтобы извлечь XML из объекта Stream и поместить в программу синтаксического разбора. Такой метод действует, поскольку можно передать методу Load объекта DOMDocument26 указатель, который поддерживает интерфейс IStream. Затем используется процедура ShowXML, приведенная в листинге 5. Она сохраняет XML во временном файле и загружает его в Web-браузер для просмотра, используя элемент управления OLE custom control (OCX). Обратите внимание на то, что загрузить OCX можно было бы напрямую из объекта, который поддерживает интерфейс IStream, однако VB не позволяет это сделать.

ЛИСТИНГ 5 Процедура ShowXML.

Private Sub ShowXML

` Сохранить XML во временном файле
mobjXML.Save "temp.xml"

` Открыть временный файл Web-браузером OCX 
WebBrowser1.Navigate2 "file://" & App.Path & "/temp.xml"

End Sub

Версия 2.5 и более поздние версии ADO позволяют применять метод Save объекта Recordset для генерации XML. Однако использование базы данных для выполнения этой задачи упрощает код и позволяет избежать двойной обработки данных. Двойная обработка выполняется в том случае, если сначала возвращается стандартный набор записей ADO, а затем промежуточный слой программного обеспечения генерирует XML.

Исполнение шаблонов

Генерировать XML из SQL Server 2000 средствами ADO можно также с применением шаблонов. Шаблон представляет собой документ XML, который определяет один или несколько запросов, подлежащих обработке в SQL Server. Запросы могут быть написаны как на SQL, так и на языке XML Path Language (XPath). Хотя обычно выполняются шаблоны, посылаемые клиентами Web (такая технология называется доступом через URL), точно так же можно выполнять шаблоны, посылаемые клиентстким экземпляром ADO.

В листинге 3 показан шаблон, который вызывает хранимую процедуру usp_GetCatTotals и передает ей ряд параметров. Для вызова хранимой процедуры из шаблона применяется такой же синтаксис, как и для вызова хранимой процедуры из T-SQL. Сначала определяются параметры и их значения, затем для передачи параметров хранимой процедуре применяется предложение EXEC. Как показывает метка А листинга 3, шаблон должен обязательно иметь корневой элемент ROOT, хотя назвать его можно не ROOT, а иначе. После того как будет установлено соединение с SQL Server при помощи объекта Connection и после настройки объекта Command, содержимое шаблона записывается в объект ADO Stream. В дальнейшем этот объект будет исполняться объектом Command. Чтобы записать содержимое шаблона в объект Stream, сначала генерируется экземпляр этого объекта, а затем принадлежащий объекту Stream метод WriteText переносит содержимое шаблона в объект Stream. Этому соответствует метка В листинга 3. Последняя строчка кода данного раздела листинга использует свойство Position, чтобы вернуть указатель на начало объекта Stream. Это необходимо для того, чтобы последующие операции чтения информации из потока возвращали данные.

ЛИСТИНГ 3: Template That Calls usp_GetCatTotals to Return XML

Private Sub OpenStream()

Dim cnConn As Connection
Dim strConn As String
Dim sStreamQuery As Stream
Dim cmCmd As Command
Dim strQuery As String
Dim sResponseStream As Stream

Set cnConn = New Connection

strConn = "Provider=SQLOLEDB;Data Source=ssosa\sql2000;Initial
 Catalog=Northwind;trusted_connection=yes"
cnConn.ConnectionString = strConn
cnConn.Open

Set cmCmd = New Command
Set cmCmd.ActiveConnection = cnConn

BEGIN CALLOUT A
` Создать шаблон запроса
strQuery = "<ROOT xmlns:sql=`urn:schemas-microsoft-com:xml-sql`>"
strQuery = strQuery & "<sql:header>"
strQuery = strQuery & "<sql:param name=`CatID`>1</sql:param>"
strQuery = strQuery & "<sql:param name=`Start`>7/5/96</sql:param>"
strQuery = strQuery & "<sql:param name=`End`>7/5/97</sql:param>"
strQuery = strQuery & "</sql:header>"
strQuery = strQuery & "<sql:query >"
strQuery = strQuery & "exec usp_GetCatTotals @CatID, @Start, @End"
strQuery = strQuery & "</sql:query>"
strQuery = strQuery & "</ROOT>"
END CALLOUT A

BEGIN CALLOUT B
` Считать шаблон запроса в объект  Stream
Set sStreamQuery = New Stream
sStreamQuery.Open
sStreamQuery.WriteText strQuery, adWriteChar
sStreamQuery.Position = 0
END CALLOUT B

BEGIN CALLOUT C
` Установить связь потока и команды и 
` выбрать диалект XML для интерпретации
Set cmCmd.CommandStream = sStreamQuery
cmCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
END CALLOUT C

` Создать поток для обработки отклика
Set sResponseStream = New Stream
sResponseStream.Open

` Ответный объект может находиьтся на странице ASP 
cmCmd.Properties("Output Stream") = sResponseStream

cmCmd.Execute , , adExecuteStream

Call ShowXML(sResponseStream.ReadText)

End Sub

Перед началом исполнения объекта Command шаблон при помощи свойства CommandStream устанавливает ассоциацию между объектом Stream, содержащим шаблон, и объектом Command. В листинге 3 это место помечено символом С. Свойство CommandStream позволяет объекту Command использовать содержимое объекта Stream в качестве исходного текста команды. Шаблон также присваивает значение свойству Dialect, чтобы сообщить ADO, что содержимое объекта Stream следует интерпретировать как XML. Как и рассмотренная ранее процедура VB OpenProc, шаблон возвращает результаты в выходном потоке.

Применение представлений XML Views и запросов XPath

Третий способ генерации XML при помощи ADO базируется на использовании представлений XML и запросов на языке XPath. Представление XML является схемой XML, содержащей дополнительную информацию, которая позволяет процессору запросов SQL Server определить, какие таблицы надо будет использовать при обработке запроса. Для задания схемы применяется XDR со специальными аннотациями, указывающими, какие элементы схемы соответствуют каким таблицам. Аннотации можно также использовать для определения отношений между элементами, например, отношений внешнего ключа. Они помогают поставщику SQLOLEDB строить обороты JOIN для корректного возврата данных. В листинге 4 приведена схема XDR с аннотациями, которая создает представление XML. В этом представлении у элемента вершины Order имеются подчиненные элементы, содержащие информацию о заказах (Order Details) и заказчиках (Customer). На рисунке 3 показан документ XML, который поставщик SQLOLEBDB генерирует на основании схемы XDR.

ЛИСТИНГ 4: Схема XDR Schema с аннотациями, используемая для создания представления XML.

<?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="OrderDetail" sql:relation="[Order Details]" >

    <AttributeType name="ProductID" />
    <AttributeType name="Quantity" />
    <AttributeType name="UnitPrice" />
    <attribute type="ProductID" />
    <attribute type="Quantity" />
    <attribute type="UnitPrice" />

  </ElementType>


  <ElementType name="Customer" sql:relation="Customers">
    <AttributeType name="CustomerID" />
    <AttributeType name="ContactName" />
    <attribute type="CustomerID" />

    <attribute type="ContactName" />
  </ElementType>

  <ElementType name="Order" sql:relation="Orders" 
                            sql:key-fields="OrderID">
    <AttributeType name="OrderID" />
    <AttributeType name="OrderDate" /> 
    <attribute type="OrderID" />

    <attribute type="OrderDate" />

    <element type="OrderDetail">
      <sql:relationship key-relation="Orders" key="OrderID"
         foreign-key="OrderID" foreign-relation="[Order Details]" />
    </element>

    <element type="Customer" >

        <sql:relationship
            key-relation="Orders"
            key="CustomerID"
            foreign-relation="Customers"
            foreign-key="CustomerID" />
    </element>

   </ElementType>

</Schema>

Обсуждение синтаксиса аннотаций выходит за рамки данной статьи, но комбинирование схем XDR и аннотаций позволяет создавать весьма сложные схемы, не прибегая к синтаксису оборота FOR XML EXPLICIT. SQL Profiler показывает, что когда используются схемы XDR с аннотациями, поставщик SQLOLEDB самостоятельно формирует синтаксис FOR XML EXPLICIT. В дополнение к этому схемы с аннотациями удобно применять в следующих ситуациях. Предположим, что торговый партнер присылает вам для использования свою схему. Можно создать аннотированную версию схемы, которую приложения будут выполнять в SQL Server, создавая документ XML, необходимый вашему партнеру. Этот процесс намного проще применения оборота FOR XML EXPLICIT для генерации корректно отформатированного документа XML.

Как только схема определена, к ней можно обращаться с запросами XPath. Язык XPath предназначен для выборки узлов в документе XML. Более подробная информация о XPath содержится в спецификации "XML Path Language (XPath) Version 1.0: W3C Recommendation 16 November 1999", размещенной по адресу http://www.w3.org/tr/xpath.

ЛИСТИНГ 6: Процедура VB, которая исполняет запрос XPath Query к представлению XML,
 созданному в листинге 4.

Private Sub OpenView(ByVal pOrderID)

Dim cnConn As Connection
Dim strConn As String
Dim cmCmd As Command
Dim sResponseStream As Stream

Set cnConn = New Connection

strConn = "Provider=SQLOLEDB;Data Source=ssosa\sql2000;Initial
 Catalog=Northwind;trusted_connection=yes"
cnConn.ConnectionString = strConn
cnConn.Open


Set cmCmd = New Command
Set cmCmd.ActiveConnection = cnConn

BEGIN CALLOUT A
cmCmd.CommandText = "Order[@OrderID=" & pOrderID & "]"
cmCmd.Dialect = "{EC2A4293-E898-11D2-B1B7-00C04F680C56}"
END CALLOUT A

BEGIN CALLOUT B
cmCmd.Properties("Mapping Schema") = "CustOrders.xml"
cmCmd.Properties("Base Path") = App.Path & "\"
END CALLOUT B

` Create a stream to handle the response
Set sResponseStream = New Stream
sResponseStream.Open

` Can also be a response object in an ASP page
cmCmd.Properties("Output Stream") = sResponseStream

cmCmd.Execute , , adExecuteStream

Call ShowXML(sResponseStream.ReadText)
      

End Sub

В листинге 6 приведена хранимая процедура, исполняющая следующий запрос XPath к схеме, созданной в листинге 4, для конкретного значения идентификатора заказа OrderID:

Order[@OrderID=?]

Первая часть запроса, Order, определяет запрашиваемый элемент. Вторая часть, @OrderID=?, задает запрашиваемый атрибут предиката. Знак вопроса показывает, что будет указан параметр, которому должно соответствовать значение OrderID. При использовании ADO для отправки запроса XPath в SQL Server, следует просто воспользоваться свойством CommandText объекта Command и присвоить такое значение свойству Dialect, чтобы ADO «знал», что будет исполняться запрос XPath:

cmCmd.CommandText = "Order[@OrderID=" & pOrderID & "]"
cmCmd.Dialect = "{EC2A4293-E898-11D2-B1B7-00C04F680C56}"

Приведенные операторы передают требуемое значение OrderID в запрос XPath через параметр pOrderID.

Хранимая процедура должна также сообщить ADO о местоположении схемы XDR, которая называется еще схемой отображения. Имя схемы задается в динамическом свойстве Mapping Schema объекта Command, а путь к файлу указывается в свойстве Base Path:

cmCmd.Properties("Mapping Schema") = "CustOrders.xml"
cmCmd.Properties("Base Path") = App.Path & "\"

Эта процедура также применяет объект VB App для идентификации базового пути, поскольку схема отображения размещается в том же каталоге, что и исполняемый файл VB. Как и в приведенных выше примерах, хранимая процедура использует объект Stream для получения возвращаемого документа XML.

Все три рассмотренные методики основаны на использовании возможностей ADO для получения документов XML из баз данных SQL Server. Описанные подходы являются альтернативой как жестко запрограммированной генерации XML в хранимых процедурах и страницах ASP, так и применению менее эффективного метода Save. Использование ADO может значительно повысить производительность труда программистов и облегчить взаимодействие всего окружения баз данных SQL Server с остальным миром, связь с которым осуществляется через сеть.