Xreferat.com » Рефераты по информатике и программированию » Использование XML совместно с SQL

Использование XML совместно с SQL

NULL

Таблица 1.

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

tag parent pub_name city fname lname
2 1 Binnet & Hardley Washington Paolo Accorti
2 1 Binnet & Hardley Washington Victoria Ashworth
2 1 Binnet & Hardley Washington Helen Bennett
2 1 Binnet & Hardley Washington Lesley Brown
... ... ... ... ... ...

Таблица 2.

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

ПРИМЕЧАНИЕ

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

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

OPENXML

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

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

[WITH (SchemaDeclaration | TableName)]

Аргументы:

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

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

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

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

ColName ColType [ColPattern | MetaProperty]

Где

ColName – имя поля.

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

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

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

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

ПРИМЕЧАНИЕ

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

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

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

<rsdn>

 <forums date="09.01.03">

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

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

   <moderators/>

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

  </forum>

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

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

   <moderators/>

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

  </forum>

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

      description="Delphi и Builder">

   <moderators>

    <moderator name="Sinclair"/>

    <moderator name="Hacker_Delphi"/>

   </moderators>

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

  </forum>

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

   <moderators>

    <moderator name="_MarlboroMan_"/>

   </moderators>

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

  </forum>

 </forums>

</rsdn>

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

exec sp_xml_preparedocument @hdoc out, @_xmlbody

select [text] as totalposts

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

  join (select [id],localname

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

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

exec sp_xml_removedocument @hdoc

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

totalposts
16688
10116
5001
6606

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

exec sp_xml_preparedocument @hdoc out, @_xmlbody

select *

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

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

exec sp_xml_removedocument @hdoc

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

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

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

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

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

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

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

exec sp_xml_preparedocument @hdoc out, @_xmlbody

select

 forum as 'Форум',

 case when moders is null

  then 'нет'

  else moders

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

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

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

 (

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

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

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

 )

exec sp_xml_removedocument @hdoc

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

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

(

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

 forum varchar(50) '@name',

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

)

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

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

DECLARE @idoc int

DECLARE @doc varchar(1000)

SET @doc ='

<root>

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

  <name>Janine</name>

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

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

   Customer was very satisfied

  </Order>

 </Customer>

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

  <name>Ursula</name>

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

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

          note="Wrap it blue white red">

   Sad Customer.

   <Urgency>Important</Urgency>

  </Order>

 </Customer>

</root>'

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

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *

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

 (

  cid char(5) '@cid',

  [name] varchar(20),

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

  amount float 'Order/@amount',

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

 )

-- Очистка

EXEC sp_xml_removedocument @idoc

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

cid name oid amount comment
C1 Janine O1 3.5 Customer was very satisfied
C2 Ursula O4 10000.0 Happy Customer.

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

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

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

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

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

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

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

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

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

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

ПРИМЕЧАНИЕ

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

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

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

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

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

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

URL-запросы

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

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

Здесь:

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

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

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

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

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

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

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

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

XML document must have a top level element.

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

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

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

Incorrect syntax near 'M'.

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

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

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

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

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

<?xml version="1.0" ?>

<xsl:stylesheet xmlns:xsl="w3/1999/XSL/Transform"

        version="1.0">

 

 <xsl:template match = "*">

  <xsl:apply-templates />

 </xsl:template>

 <xsl:template match = "row">

 <li>

  <table><tr>

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

Если Вам нужна помощь с академической работой (курсовая, контрольная, диплом, реферат и т.д.), обратитесь к нашим специалистам. Более 90000 специалистов готовы Вам помочь.
Бесплатные корректировки и доработки. Бесплатная оценка стоимости работы.

Поможем написать работу на аналогичную тему

Получить выполненную работу или консультацию специалиста по вашему учебному проекту
Нужна помощь в написании работы?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Пишем статьи РИНЦ, ВАК, Scopus. Помогаем в публикации. Правки вносим бесплатно.

Похожие рефераты: