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

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

Более всего для хранения XML-документа в БД подходит тип clob (character large object). Этот тип в SQL Server называется text (ntext – для юникодных символов). Однако XML – это не просто текст, а формат разметки и хранения данных, и логично было бы работать с ним как со структурированными данными, а не как с простым текстом. К сожалению, до сих пор SQL Server не позволял этого. Например, чтобы выбрать определенный узел XML-документа с помощью XPath, этот документ придется перенести на middle tier, потому что SQL Server не содержит XML-процессоров, способных выполнять подобные операции. А что, если необходимо использовать результаты обработки XML-документа в SQL-запросе? XML должен быть отправлен обратно и преобразован в скалярные величины или в реляционные данные (функция OPENXML в SQL Server-е).

Иерархическую информацию можно представить с помощью реляционной модели [18]. Однако о простоте при этом приходится забыть. Почему? Дело в том, что в SQL просто нет механизмов выборки иерархической информации. Различные серверы для осуществления подобной выборки предоставляют разнообразные расширения SQL. Например, в Oracle есть расширение connect by. В SQL Server 2005 появятся общие табличные выражения (common table expressions), которые могут выполняться рекурсивно.

Для решения описанных выше проблем в SQL Server будет добавлен новый тип данных – XML. В Oracle аналогичный тип появился сравнительно давно, в SQL Server-е – только-только. Что дает его появление? Это позволяет перенести обработку XML-документов в сервер БД. Теперь с помощью встроенного XML-процессора можно выполнять запросы XPath и XQuery. Кроме выполнения запросов предоставляется поддержка механизма изменения XML-документов, а также проверки их на допустимость. Теперь при работе с XML можно использовать все преимущества SQL Server, например, транзакции.

Теперь у пользователей есть возможность извлечения XML-документов прямо из SQL Server непосредственно с помощью браузера. Отпадает необходимость в использовании отдельного Web-сервера и дополнительного ПО (типа SQLXML), служащего для связи между SQL Server и Web-сервером.

Что ж, давайте поближе ознакомимся с этими возможностями.

Тип данных XML

Основы

XML-документ или фрагмент представлен в виде нового типа данных – xml. Вы можете создавать атрибуты таблиц или переменные данного типа. Инициализировать их можно просто строкой, например:

XML-тип похож на CLR-тип в том смысле, что у него, как и у CLR-типа, имеются методы. Экземпляр XML-типа создается не в момент объявления, а только тогда, когда ему присваивается некое строковое значение. Например,

        declare @xml xml--здесь переменная @xml равна NULL, --поэтому следующий запрос возвращает NULLselect @xml::query('/*')
--создаем экземплярset @xml = ''--здесь возвращается пустая строкаselect @xml::query('/*')

XML-тип похож на строковый: единственное ограничение заключается в том, что строка должна представлять сбалансированный XML-фрагмент. Под сбалансированным XML-фрагментом (это не официальный термин, однако он встречается в литературе) понимается XML-фрагмент, который полностью удовлетворяет спецификации XML 1.0 за исключением того, что не имеет корневого элемента. Такой документ нельзя назвать правильно оформленным (well formed), однако в некоторых языках (XPath, XQuery) он допустим.

Переменная строкового типа неявно преобразуется к XML-типу. Обратное не верно: для того, чтобы получить экземпляр XML-типа вам необходимо выполнить явное преобразование с помощью операторов cast или convert.

Так как строки неявно преобразуются к типу xml, вы можете задавать умолчания (defaults) для колонок XML-типа в виде строки или менять тип поля таблицы со строкового на xml. Например,

В принципе, такое поведение вполне логично. Xml – это прежде всего текст! Давайте рассмотрим, чем же он отличается от обычной строки.

С XML-типом можно связать схему (xml schema), которая предназначена для проверки структуры документа. Документ, соответствующий определенной схеме, называется допустимым (well formed). Схемы данных описываются с помощью специального языка XML Schema definition language (XSD), который в свою очередь также выражается с помощью xml. Спецификация XML Schema принята уже относительно давно, более детальную информацию о ней и официальные документы можно найти в [5].

Создадим простенькую схему для документа, который может содержать только один XML-элемент с названием root, не может содержать атрибутов и дочерних элементов. Значение элемента root ограничено строковым типом. Забегая вперед скажу, что в SQL Server-е название схемы явно не задается, оно получается из целевого имени (targetNamespace) пространства имен схемы данных.

Создается схема с помощью новой инструкции create xmlschema. Вот как это делается:

О работе со схемами в SQL Server я поговорю чуть попозже, а теперь давайте попытаемся создать экземпляр документа этой схемы:

Так как наличие каких-либо атрибутов запрещено схемой, в момент присваивания строкового литерала переменной @xml, мы получаем ошибку:

Переменная XML-типа, которая связана с какой-либо схемой данных, называется типизированной. Вы можете изменять тип (т.е. схему) полей XML-типа с помощью команды alter table, как, например, в следующем примере (таблица T была создана в примере выше):

Теперь атрибут x таблицы T является типизированным. При попытке вставить в него фрагмент, не соответствующий схеме, будет сгенерированна ошибка. Например команда,

попытается вставить в него документ <root/> (заданный по умолчанию), который не принадлежит пространству имен my-first-schema, поэтому сервер выдаст ошибку:

Так как xml – это полноценный тип, у него есть методы:

Метод query

Метод query используется, когда необходимо выполнить запрос XQuery к XML-документу или фрагменту. Метод возвращает экземпляр типа xml, содержащий результат выполнения запроса. Поддержка развивающегося стандарта запросов для XML-документов соответствует черновикам стандарта, выпущенным консорциумом w3c на протяжении 2002 года [2]. Синтаксис вызова метода следующий:

Ссылку на узел можно получить с помощью табличной функции xmlnoderefs, возвращающей таблицу с одним полем noderef, записи которой содержат ссылки на выбранные узлы документа. Синтаксис функции следующий:

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

Результат (ссылки на три дочерних узла элемента fam):

Использование этой функции может привести к существенному увеличению производительности в тех случаях, когда в запросах используются «почти повторяющиеся» пути. Другое применение заключается в возможности работать с набором узлов в запросе, а не с одним документом или фрагментом xml. Выберем, например, суммарный доход всех членов семьи:

Это единственный способ выполнить подобный запрос, так как запрос "в лоб"

не работает, потому что метод query возвращает XML-документ или фрагмент, но никак не атрибуты (сервер выдает ошибку – XQuery: Attribute may not appear outside of an element). Использование метода value:

также не возможно, поскольку он должен возвращать скалярное значение (сервер выдает ошибку – XQuery: Expression used by 'value()' must return a singleton or empty sequence).

Также можно использовать табличную функцию xmlnoderefs вместе с новым оператором APPLY, который позволяет вызывать произвольную табличную функцию для каждой строки внешней таблицы [3]. Допустим, таблица fams содержит семьи.

Необходимо выбрать доход каждого из членов семей:

Здесь функция xmlnoderefs вызывается для каждой семьи (для каждой строчки таблицы) и возвращает три ссылки на дочерние элементы (fam). Для каждой ссылки, которая обозначает узел XML-элемента (husband, wife или son) вызывается метод value.

Метод value

Из предыдущих примеров уже должно быть понятно назначение этого метода. Его синтаксис:

Использование этого метода удобно для выбора определенных скалярных значений документа. Не стоит его использовать только для фильтрации результирующего набора, как показано ниже:

В результате выбираются все семьи, мужья в которых имеют доход больше 180. Это менее эффективно, чем использование метода exist (особенно при наличии индекса), который я описываю далее:

Метод exist

Метод exist возвращает значение типа bit, равное:

Метод modify

Этот метод позволяет выполнять изменяющие XML-документ команды, речь о которых пойдет в разделе XML DML.

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

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

Например, можно создать функцию, которая возвращает суммарный доход всех членов семьи:

На поля XML-типа можно накладывать ограничения (check constraints), например следующее ограничение не позволит вставить в таблицу fams семью, у элемента husband которой отсутствует атрибут @income (хотя лучше для таких целей использовать XSD):

Если вы еще не привыкли к XQuery (который я старался не использовать в предыдущих примерах, обходясь синтаксисом старого XPath 1.0), вы можете создать удобное представление (view) на основе XML-поля. Следующее view отображает некоторые данные таблицы fams в реляционном виде:

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

XML-индекс

Xml-индекс может существенно увеличить скорость запросов, особенно на больших документах. XML-индекс не строится для отдельного набора узлов – индексируется весь документ, поэтому размер индекса во много раз превышает размер самого XML-документа. Сейчас пока невозможно точно сказать, насколько он замедляет операции DML, и насколько увеличивает производительность запросов, поэтому к построению индекса нужно подходить со здоровой долей скептицизма.

При создании XML-индекса должны учитываться следующие ограничения:

Удаление индекса выполняется обычной командой drop index, правда, синтаксис ее в Yukon немного изменен по сравнению с SQL Server 2000:

Хранение XML-типа

Мне было очень интересно узнать, каким образом SQL Server хранит XML-тип. К сожалению, в документации об этом нет ни слова, и вряд ли положение изменится к публичной бете. Вот что я выяснил из собственных экспериментов и общения с разработчиками.

XML-тип сохраняется как большой объект (lob - large object) в бинарном формате, структура которого неизвестна, но точно можно сказать, что она линейна. Т.е. сохраняется относительный порядок узлов в документе – атрибуты следуют после элемента, к которому они принадлежат, дочерние элементы следуют после атрибутов, и последующие элементы (элементы того же уровня) – после дочерних элементов.

Для осуществления запроса XQuery SQL Server каждый раз выполняет преобразование этой линейной структуры в реляционную – в таблицу узлов (node table). Таблица узлов более формализована и понятна, однако занимает в несколько раз (даже на порядки) больше места. Любой запрос XQuery трансформируется в SQL запрос к этой таблице.

Эта таблица может быть сохранена для последующих запросов, что и делается при построении XML-индекса.

Структура этой таблицы следующая (назначение некоторых полей я придумал сам, потому что разработчики пока не признаются, что эти поля означают):

Вот собственно и все, что мне удалось раскопать по внутреннему устройству XML-типа.

XML DML

На данный момент не существует стандарта и даже рабочей группы по вопросам создания языка для изменения содержимого XML-документов.

Существует самостоятельная инициатива разработчиков – XUpdate, которая представлена сообществом XMLDB (xmldb.org) и доступна по адресу [4]. Есть несколько продуктов, в том числе и коммерческих, поддерживающих этот язык, однако большого распространения он не получил.

Разработчики SQL Server 2005 решили пойти своим путем: они разработали язык xml dml – XML Data Manipulation Language, который является расширением XQuery. Он не выражается в терминах самого XML (как XUpdate), и в нем можно применять выражения XQuery. Расширение заключается во введении трех ключевых слов – update, delete и insert, которые можно использовать в методе modify XML-типа.

Ниже будем использовать несколько более сложный XML-документ, удовлетворяющий схеме my-first-schema:

        drop
        xmlschema
        namespace
        'my-first-schema'
        go
create
        xmlschema
        '
<xs:schema 
  targetNamespace="my-first-schema"
  xmlns:tns="my-first-schema"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
>
  <xs:element name="root">
    <xs:complexType>  
      <xs:sequence>
        <xs:element name="a" 
          maxOccurs="unbounded" minOccurs="0" 
          form="qualified"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

Здесь мы расширили содержимое XML-элемента root: теперь он может содержать бесчисленное множество элементов a любого типа (так как тип не задан, используется по умолчанию xs:Any).

Delete

Это самый простой оператор. Вот его синтаксис

Где Expression – выражение XQuery, выбирающее последовательность узлов, подлежащих удалению. Оператор delete не может удалить узлы, находящиеся на оси namespace – это единственная ось, которая есть в XPath 2.0, но отсутствует в XQuery.

Любое выражение в XQuery возвращает последовательность элементов (sequence items). Элементами могут быть либо скалярные величины, либо узлы. Последовательность – совершенно новое понятие, которое заменяет понятие набора узлов в XPath 1.0, более подробно о нем мы поговорим в разделе "Поддержка XQuery".

Рассмотрим пример применения оператора delete:

Здесь удаляется узел a, значение которого равно 10. Единственное, что отличает это выражение от старого XPath – пролог, в котором декларируется пространство имен. Попробую продемонстрировать некоторые возможности XQuery:

В этом примере, если сумма всех числовых значений элементов a больше 10, удаляется XML-элемент а с максимальным значением. Иначе не удаляется ничего. Вместо конструкции "/.." по идее можно было бы использовать пустую последовательность "()". Но в имеющейся у меня бета-версии это приводило к ошибке. Поэтому пришлось применить эту конструкцию, обозначающую пустой элемент (точнее, несуществующего родителя узла документа).

Замечу, что при использовании функции max узлы а трактовались как строковые, так что если бы документ был следующего содержания:

то максимальным элементом оказался бы узел со значением 2. Чтобы выполнять операции над значениями элемента а как над числовыми, необходимо в схеме данных (my-first-schema) задать тип XML-элемента а. Есть другой способ, который заключается в непосредственном указании типа прямо в самом XML-документе с помощью атрибута type пространства имен http://www.w3.org/2001/XMLSchema-instance:

Однако ни тот, ни другой способ текущей версией процессора в SQL Server не поддерживаются.

Следующий пример демонстрирует объединение результатов двух XPath-запросов в единую последовательность. В нем происходит удаление третьего элемента (в порядке их следования в документе) и элементов, чье числовое представление равно 1:

Изменение XML-фрагментов в таблице выглядит довольно причудливо. Я сейчас продемонстрирую, как это делается, но все остальные примеры будут выполняться на локальных переменных, так как это проще и нагляднее.

Удалим из таблицы fams мужей из всех семей, чей суммарный доход превышает 440:

Данная команда выполняется в контексте транзакции, как и любой другой обычный update.

Update

Этот оператор предназначен для изменения существующего значения узла. Синтаксис оператора:

Вот примеры использования данного оператора:

          declare @xml xmlset @xml = '<?xml version="1.0" ?>
<root>some text
  <a>1</a>
  <a>2</a>
  <a>30</a>
</root>'--Изменим значение элемента а, чье предыдущее значение --было равно минимальному, на сумму всех числовых--значений элементов аset @xml::modify('
  update //a[. = min(//a)]/text() to sum(//a)
')

select @xml

--Изменим текстовый узел элемента rootset @xml::modify('
  update /root/text() to "another text"
')
    
select @xml

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

          declare @xml xmlset @xml = '<?xml version="1.0" ?>
<root>first text node
  <a b="9">1</a>
  <a>2</a>
  <a>30</a>
  second text node
</root>'--Изменим значение атрибута b на среднее арифметическое--числовых значений элементов аset @xml::modify('
  update //a/@b to avg(//a)
')

select @xml

--Изменим первый текстовый узел элемента rootset @xml::modify('
  update /root/text()[1] to "another text node"
')
    
select @xml

------------------------------------------------------
<root>first text node
  <a b="11">1</a><a>2</a><a>30</a>
  second text node
</root>

(1 row(s) affected)


------------------------------------------------------
<root>another text node<a b="11">1</a><a>2</a><a>30</a>
  second text node
</root>

(1 row(s) affected)

Insert

Последний оператор DML предназначен для вставки узлов в существующий XML-документ. Это самый мощный и сложный оператор. Рассмотрим его синтаксис:

Ниже приводится пример работы данного оператора:

          declare @xml xmlset @xml = '<?xml version="1.0" ?>
<root>
  <a>1</a>
  <a>2</a>
  <a>30</a>
</root>'-- Добавляем инструкцию обработки как первый узел среди дочерних-- узлов элемента rootset @xml::modify('
  insert <?mypi d="a" ?> as first into /root
')

select @xml

-- добавляем комментарий как дочерний узел элемента root-- после второго элемента аset @xml::modify('
  insert <!--comment--> after /root/a[2]
')

select @xml

-- Невозможно добавить узел пространства имен-- в элемент а. Не поддерживается в текущей версии-- set @xml::modify('--  insert namespace some {"rosa"} into /root/a[1]
--'</str></str>)
-- Добавляем атрибут attr со значением b во-- второй элемент аset @xml::modify('
  insert attribute attr {"b"} into /root/a[2]
')

select @xml

-- Добавляем два элемента: sum и avеrage с соответствующими значениями-- как последние узлы элемента rootset @xml::modify('
  insert (<sum>{sum(//a)}</sum>, element average {avg(//a)}) into /root
')

select @xml

-- Добавляем текстовый узел как первый дочерний узел элемента rootset @xml::modify('
  insert <![CDATA[text node]]> as first into /root
')

select @xml

------------------------------------------------------
<root><?mypi d="a" ?><a>1</a><a>2</a><a>30</a></root>

(1 row(s) affected)


------------------------------------------------------
<root><?mypi d="a" ?><a>1</a><a>2</a><!--comment--><a>30</a></root>

(1 row(s) affected)


------------------------------------------------------
<root><?mypi d="a" ?><a>1</a><a attr="b">2</a><!--comment--><a>30</a></root>

(1 row(s) affected)


------------------------------------------------------
<root>
  <?mypi d="a" ?>
  <a>1</a>
  <a attr="b">2</a>
  <!--comment-->
  <a>30</a>
  <sum>33</sum>
  <average>11</average>
</root>

(1 row(s) affected)

------------------------------------------------------
<root>text node<?mypi d="a" ?>
  <a>1</a>
  <a attr="b">2</a>
  <!--comment-->
  <a>30</a>
  <sum>33</sum>
  <average>11</average>
</root>

(1 row(s) affected)

В последних трех инструкциях данного примера я попытался применить конструкторы узлов – специальные выражения XQuery для создания узлов любого типа. К сожалению, конструкторы узлов пространств имен, документа, текста, комментария и инструкции обработки не поддерживаются в текущей версии. Однако часть из них – комментарии и инструкции обработки – можно задавать непосредственно как литералы, а для конструирования текстовых узлов можно использовать секцию CDATA, что и было продемонстрировано в примере. Более подробно о конструкторах узлов см. раздел Поддержка XQuery.

Поддержка XML Schema

XML-документ – вещь чрезвычайно гибкая. Он может принимать какую угодно структуру, главное, чтобы он удовлетворял тем синтаксическим требованиям, которые излагаются в спецификации XML 1.0. С одной стороны это очень хорошо, однако конкретное приложение, как правило, хочет работать не с абстрактной структурой, а со вполне определенной. Для описания структуры или схемы документа и проверки документа на соответствие этой схеме консорциумом w3c был разработан стандарт XML Schema Definiton Language. Подробное описание его выходит за рамки статьи [5].

В SQL Server 2005 входит поддержка данного стандарта, правда в весьма ограниченном виде. Основными целями этой поддержки разработчики считают автоматическую проверку документа на допустимость и типизацию документа. Фактически первичной целью является типизация документа, так как автоматическая проверка на допустимость проводится именно на основе типов. Другой эффект от типизации документа заключается в оптимизации хранения документа и оптимизации запросов XQuery. Оптимизация заключается в том, что для типизированных документов система хранит значения соответствующих типов для значений узлов, тогда как для не типизированного документа все текстовые узлы и значения атрибутов хранятся в виде строк. Такой подход уменьшает размер сериализованного представления XML-документа и увеличивает скорость выполнения запросов.

Схема XML-документа хранится в метаданных базы, ее имя (т.е. целевое пространство имен) должно быть уникально не в пределах схемы как почти все объекты теперь, а в пределах всей базы.

Сервер хранит схемы данных не в том первоначальном текстовом виде, в котором они создавались, а в разобранном и приведенном к реляционной структуре. Разобранная схема доступна с помощью нескольких системных представлений. Вот основные из них:

Как вы сами понимаете, собирать из всего этого схему вручную довольно сложно, поэтому существует встроенная функция xml_schema_namespace, которая по имени схемы воссоздает ее текстовое представление. Надо сказать, что полученная таким образом схема данных не совсем точно будет соответствовать первоначальной, так как в метаданных не хранятся комментарии, аннотации, префиксы пространств имен и пр. Если вам необходимо работать с оригинальной версией схемы, рекомендуется сохранять ее в отдельной таблице.

Если сейчас выбрать текстовое представление схемы my-first-schema с помощью функции xml_schema_namespace, вы увидите некоторые различия с исходной схемой:

<xsd:schema 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  targetNamespace="my-first-schema" 
  xmlns:t="my-first-schema"
  elementFormDefault="qualified">
  <xsd:import namespace="http://www.w3.org/2001/XMLSchema" 
  schemaLocation="urn:schemas-microsoft-com:sql:database" />
  <xsd:element name="root">
    <xsd:complexType>
      <xsd:complexContent>
        <xsd:restriction base="xsd:anyType">
          <xsd:sequence>
            <xsd:element name="a" 
              type="xsd:integer" 
              minOccurs="0" 
              maxOccurs="unbounded" />
          </xsd:sequence>
        </xsd:restriction>
      </xsd:complexContent>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Добавлено пространство имен urn:schemas-microsoft-com:sql:database, добавлена секция complexContext.

Рассмотрим команды создания и удаления схемы.

Создание схемы

Expression – строка, представляющая собой схему документа. Это может быть строковый литерал или переменная строкового типа.

Команды изменения схемы нет: повторное создание схемы с тем же именем приводит к замещению схемы, что, как мне кажется, не очень правильно.

Выше уже приводились примеры использования этой команды.

Удаление схемы

Удалить можно только ту схему, с которой еще не связан ни один столбец в таблицах данной базы. К сожалению, если такая связь есть, команда не удаляет схему и не выдает никаких сообщений об ошибках. Кроме этого, на данный момент нет механизмов определения, с какими полями таблиц связана данная схема, так что удалить схему практически невозможно (по крайней мере, мне не удалось удалить ни одну из ранее созданных схем!). :(

Вот такая команда должна была бы удалить схему my-first-schema:

Ограничения текущей реализации

К сожалению, на данный момент аннотации, примечания и ограничения (unique, key и keyref) не сохраняются в метаданных базы. Кроме того, атрибут id в схеме не поддерживается.

Полностью список ограничений можно найти в MSDN.

Безопасность

По умолчанию права на создание схемы имеет только пользователь в роли db_owner. Если вы хотите наделить обычного пользователя правами на создание схемы в данной базе, необходимо воспользоваться следующей командой:

Здесь User or Group – имя пользователя или группы в базе.

Например, следующий код создает логин, пользователя в базе и дает ему право на создание схем XML-документов:

SQL-инструкции и XML

SQL Server позволяет представлять результаты реляционной выборки в виде XML и наоборот, представлять XML-фрагмент или документ в реляционной форме. Делается это с помощью расширения команды select – for xml и функции OPENXML, соответственно. Довольно подробно их использование описано в [6].

Рассмотрим основные нововведения в конструкции for xml, которые появятся в SQL Server 2005. Общий синтаксис запроса:

For xml, type

Результаты запроса for xml теперь доступны на сервере и могут быть автоматически представлены как встроенный тип xml. Фактически, вы можете не указывать команду type, так как преобразование из строки в XML-тип делается неявно, т.е. две следующие команды эквивалентны:

Однако в первом случае возвращается строка, которая затем преобразуется в XML-тип, а во втором – сразу XML-тип. Различие становится более очевидным в следующем примере:

Подобная запись возможна потому, что результатом выборки с ключевым словом type является XML-тип. Без использования ключевого слова type сервер вернет ошибку.

Вложенные запросы for xml

Раньше нельзя было выполнять вложенные запросы, возвращающие xml. Теперь это стало возможным. Например:

Вложенный запрос возвращает тип xml, и серверу абсолютно безразлично, как он был получен. Т.е. с тем же успехом можно написать так:

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

XSINIL

Теперь пустое значение можно представлять с помощью стандартного механизма, принятого в XML для обозначения значения NULL. Суть проблемы заключается в том, что в режиме for xml auto, elements поля со значением NULL просто не попадают в результирующий XML-фрагмент. Например:

Как видите, для первых трех строк элемент obj отсутствует, потому что равен NULL. Для того чтобы передать информацию о том, что он равен NULL, в XML-документ, необходимо добавить ключевое слово xsinil следующим образом:

<sys.indexes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <object_id>133575514</object_id>
  <obj xsi:nil="1" />
</sys.indexes>
<sys.indexes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <object_id>677577452</object_id>
  <obj xsi:nil="1" />
</sys.indexes>
<sys.indexes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <object_id>677577452</object_id>
  <obj xsi:nil="1" />
</sys.indexes>
<sys.indexes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <object_id>693577509</object_id>
  <obj>693577509</obj>
</sys.indexes>

Генерация XSD на лету

Раньше создать схему на лету можно было только в формате XDR с помощью конструкции for xml , xmldata. Так как спецификация XRD, которая никогда не была официальным стандартом, уже давно устарела, разработчики, наконец, ввели поддержку генерации схемы в формате XSD по результатам выборки. Например, нехитрый запрос:

возвращает следующий, довольно хитрый, результат:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  targetNamespace="http://schemas.microsoft.com/sql/2002/types">
  <xsd:simpleType name="nvarchar">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>
  <xsd:simpleType name="int">
    <xsd:restriction base="xsd:int"/>
  </xsd:simpleType>
</xsd:schema>
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet8" 
  xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet8" 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  xmlns:sqltypes="http://schemas.microsoft.com/sql/2002/types" 
  elementFormDefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sql/2002/types"/>
  <xsd:element name="sys.indexes">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="name" nillable="1">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:nvarchar" 
              sqltypes:localeId="1049" 
              sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
              <xsd:maxLength value="128"/>
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
        <xsd:element name="object_id" 
          type="sqltypes:int" 
          nillable="1"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<sys.indexes xmlns="urn:schemas-microsoft-com:sql:SqlRowSet8" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>pk_dtproperties</name>
  <object_id>133575514</object_id>
</sys.indexes>
<sys.indexes xmlns="urn:schemas-microsoft-com:sql:SqlRowSet8" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name xsi:nil="1"/>
  <object_id>677577452</object_id>
</sys.indexes>
<sys.indexes xmlns="urn:schemas-microsoft-com:sql:SqlRowSet8" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>IX_composite</name>
  <object_id>677577452</object_id>
</sys.indexes>
<sys.indexes xmlns="urn:schemas-microsoft-com:sql:SqlRowSet8" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name xsi:nil="1"/>
  <object_id>693577509</object_id>
</sys.indexes>

Вложенные запросы не могут генерировать схему. Это может делать только запрос верхнего уровня. Все вложенные XML-фрагменты такого многоуровневого запроса представляются типом xsd:anyType. Кроме этого, нельзя генерировать XSD-схему на лету в режиме explicit.

sql:variable и sql:column

Может сложиться ситуация, когда в запросе XQuery нужно получить значение поля sql-запроса или внешней переменной. Для таких целей в SQL Server-е введены две функции расширения: variable и column, которые должны находиться в неком пространстве имен с префиксом sql. Если в запросе объявляется какое-либо пространство имен с тем же префиксом sql, выше обозначенные функции становятся недоступными. Надеюсь, эту ошибку, как и бесчисленное множество других, разработчики поправят к релизу.

В функцию sql:variable передается один параметр – строковый литерал, значение которого трактуется как имя переменной простого типа во внешней области видимости. Функция возвращает скалярное значение этой переменной. Например:

Здесь выбираются все дочерние элементы fam, у которых атрибут income меньше значения внешней переменной i.

Похожим образом используется функция sql:column. Она позволяет получить значение поля из внешнего запроса.

Например, следующий запрос содержит в себе XQuery-подзапрос, обращающийся к колонке id внешнего SQL-запроса.

С помощью этой функции нельзя обращаться к колонкам XML-типа.

OPENXML

Как уже говорилось в первой части этой статьи [6], при использовании функции openxml можно было не указывать структуру и типы полей результирующей выборки, опуская ключевое слово with. В этом случае результат возвращался в так называемом edge table формате. Например,

Теперь можно получать доступ к внутренней информации, использующейся в процессе создания edge table, при формировании структуры результирующей выборки. Это делается с помощью атрибутов метасвойств (metaproperty attributes), которые находятся в пространстве имен urn:schemas-microsoft-com:xml-metaprop, которому назначен префикс mp. Список всех метаатрибутов я приводить не буду: достаточно взглянуть на шапку предыдущей таблицы. Вот пример их использования:

          declare @h intexec sp_xml_preparedocument @h out, 
  '<a attr="it is"><b>text1</b><b>text2</b></a>'select * fromopenxml(@h, '/a/b|/a')
with(
  nodeid int'@mp:id', 
  parentid int'@mp:parentid', 
  nodename varchar(10) '@mp:localname', 
  text1 varchar(100) '../@attr', 
  text2 varchar(100) 'text()'
)
exec sp_xml_removedocument @h

Здесь выбирается три строки - два элемента b и элемент а. Вот результат:

Поддержка XQuery

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

Последовательность – это упорядоченная коллекция элементов (item collection). Она может содержать повторяющие элементы или не содержать их вообще. В последнем случае говорят, что последовательность пуста. Элементом последовательности может быть атомарное значение или узел. Элементами последовательности не могут быть другие последовательности. Для конструирования последовательности применяется оператор запятая. Последовательности могут заключаться в скобки для улучшения наглядности.

Каждый элемент в последовательности имеет тип. Если процессор не может определить тип элемента в момент помещения его в коллекцию, ему назначается тип xs:anyType. Если процессор не может определить тип атомарного элемента, ему назначается тип xs:anySimpleType.

XQuery – типизированный, функциональный язык. В нем можно создавать пользовательские функции, однако перегрузка функций не допускается. Вы можете использовать конструкторы встроенных типов для создания атомарных элементов или узлов. В XQuery нет возможности создавать пользовательские типы.

На момент написания статьи XQuery был все еще рабочим проектом (working draft).

В имеющейся у меня версии SQL Server 2005 (кодовое название Yukon, версия 9.00.608) реализация XQuery базируется на рабочих проектах, которые выходили в течение 2002 года. Примерно эту же версию описывает Дон Чемберлин (один из редакторов спецификации) в статье [10]. Я буду описывать поддержку XQuery в SQL Server, попутно делая замечания о несоответствии реализации текущей (на момент написания статьи) спецификации.

Основы

Запрос XQuery состоит из пролога и тела запроса. В SQL Server-е пролог может содержать декларацию пространства имен и импорт схемы.

Пролог

Импорт схемы заявлен в документации, но, похоже, что все-таки не реализован. Синтаксис объявления пространства имен такой:

где NCName – неквалифицированное имя (не может содержать двоеточия).

Это отличается от синтаксиса, описанного в последних спецификациях:

Изменения состоят в добавлении ключевого слова declare и точки с запятой после объявления пространства имен.

Тело запроса

Тело запроса представляет собой выражение XQuery. Любое выражение XQuery возвращает последовательность. Если запрос возвращает атомарный элемент или узел, это считается последовательностью из одного атомарного элемента или узла.

Комментарии начинаются с символов {-- и заканчиваются символами --}. В текущей спецификации конструкция комментария имеет другой вид: (: :). Не знаю как вам, а мне смайлики нравятся больше.

Атомарные элементы могут конструироваться с помощью литералов или конструкторов атомарных элементов. О конструкторах будет сказано ниже.

XQuery во многом основан на XPath 2.0. Выражения XPath используются для выбора узлов и элементов последовательностей. В SQL Server поддерживается только 6 осей XPath из 13: child, descendant, parent, attribute, self и descendant-or-self.

Сам XQuery поддерживает 12 осей из 13 спецификации XPath 2.0 – не поддерживается только ось namespace. Кроме этого, процессор XQuery может поддерживать только выше перечисленные обязательные 6 осей, если он не поддерживает full axis feature (см. пункт 2.6.3 спецификации [14]).

Конструкторы узлов

Язык запросов для XML не был бы самим собой, если бы в нем нельзя было создавать узлы XML-документа. К сожалению, здесь довольно сложно провести аналогию с обычным SQL, потому что в нем вы не имеете возможности получать результирующую выборку в каком-либо виде, отличном от табличного.

Изменение результирующей выборки в XQuery производится при помощи конструкторов узлов. Эти конструкторы предназначены для создания узлов элементов, атрибутов, документов, инструкций обработки, комментариев, пространств имен и текстовых узлов. Кроме этого, есть возможность создавать секции CDATA. В SQL Server-е пока поддерживается создание только узлов элементов, атрибутов, комментариев и текстовых узлов, при чем последние создаются как секции CDATA, что, конечно, является очередным отклонением от стандарта.

Простой конструктор (direct constructor) – это литерал, который создает узел в соответствии с синтаксисом XML. Например, выражение

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

        (: создание секции CDATA :)
<![CDATA[Hi from CDATA!]]>

(: создание комментария :)
<!--Hi from comment!-->

(: Создание текстового узла :)
"Hi from text!"(: Создание узла инструкции обработки:)
<?target Hi from PI!?>

Что же делать в случае, когда нужно создать атрибут? Ведь нельзя же просто написать:

На помощь приходят вычисляемые конструкторы.

Вычисляемый конструктор узла – это ключевое слово, обозначающее тип узла, за которым следует название узла (если у данного типа узла может быть название) и значение. Значение узла всегда берется в фигурные скобки, внутри которых можно использовать любое выражение XQuery. Если имя узла должно задаваться динамически, его также можно сделать вычисляемым во время исполнения, путем заключения выражения, вычисляющего имя, в фигурные скобки. Например,

        (: Создание атрибута а со значением "b" :)

        attribute a {"b"}

(: Создание элемента а со значением "b" :)
element a {"b"}

(: Создание фрагмента семья :)
element fam
{
element husband {attribute income {"180"}, "alex"}, 
element wife {attribute income {"161"}, "rosa"}, 
element son {attribute income {"90"}, "dima"}
}

Вот список всех вычисляемых конструкторов. Их точный синтаксис и использование можно найти в [14].

Какие ограничения имеются в реализации SQL Server? Их много. Например, следующий код:

будет выдавать ошибку XQuery: Attribute may not appear outside of an element. По-моему, это сообщение не имеет смысла.

Секция CDATA бесследно исчезает в следующем запросе:

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

Кроме этого, мне не удалось использовать ни один конструктор узла с вычисляемым именем: SQL Server упорно выдавал ошибку XQuery: Cannot implicitly convert from 'xs:string' to 'xs:QName'.

Атомарные элементы конструируются с помощью литералов и конструкторов. В XQuery определено четыре типа литерала: строковый литерал (все, что заключено в двойные или одинарные кавычки), целочисленный литерал (целое число), вещественный литерал и литерал с плавающей точкой. Например,

В выражениях с различными типами операндов выполняется следующее неявное преобразование типов: integer -> decimal -> float -> double.

Если тип операнда после преобразования все же не совместим с типом другого операнда, генерируется ошибка времени компиляции. Тип выражения определяется типом операндов. Например,

Для конструирования или преобразования типов можно использовать конструкторы атомарных элементов. Конструкторы атомарных элементов – это, по существу, все типы XML Schema. Например,

вполне законное выражение, тип которого xs:integer. Здесь выполняется конструирование типа xs:integer из строкового литерала "1".

Если операнд не может быть преобразован к желаемому типу конструктором, генерируется динамическая ошибка (ошибка времени исполнения).

Перегруженные конструкторы атомарных элементов фактически являются выражениями преобразования типов и идентичны использованию оператора cast. Следующие строчки эквивалентны:

В XQuery поддерживается также оператор castable, который эквивалентен оператору is в языке VB.NET или в C#. Он возвращает булево значение, равное true, если операнд может быть преобразован к данному типу, и false в противном случае. Например,

Оператор castable не поддерживается в текущей версии SQL Server. Возможно, он будет реализован в дальнейшем.

Операторы сравнения

В XQuery (а точнее, в XPath 2.0) существует три типа операторов сравнения: общие операторы сравнения, операторы сравнения атомарных значений и операторы сравнения узлов.

Операторы сравнения атомарных значений

Операторы сравнения атомарных значений (value comparision operators) – новый тип операторов, тип операндов которых ограничен атомарными значениями, т.е. в качестве операндов не могут выступать последовательности.

К ним относятся eq – равно, ne – не равно, lt – меньше, gt – больше, le – меньше или равно, ge – больше или равно.

Общие операторы сравнения

Общие операторы сравнения (general comparision operators) больше всего напоминают операторы сравнения XPath 1.0. Различие заключается в том, что новые операторы работают с последовательностями элементов.

К общим операторам сравнения относятся <, >, <=, >=, = и !=.

Когда общий оператор применяется к двум последовательностям элементов, результатом является истина в том случае, если хотя бы один элемент в первой последовательности удовлетворяет соответствующему условию, примененному к любому элементу второй последовательности. Фактически, это то же самое правило, которое действовало в XPath 1.0, когда операндами оператора сравнения были наборы узлов. Что означает данное правило на практике? Данный оператор сравнения берет поочередно каждый элемент первой последовательности и применяет соответствующий атомарный оператор сравнения к каждому элементу из второй последовательности. Как видно, это довольно медленная операция. Ее трудоемкость достаточно велика – O(n*m), где n – размер первой последовательности и m – размер второй. Правда, процессор вовсе не обязан просматривать все элементы обоих последовательностей – он может завершить работу сразу же, как только для какой-либо пары оператор сравнения вернет истинное значение.

          declare @xml xmlset @xml = '<a>1</a><a>2</a>'select @xml::query('
{--
false – нет ни одного элемента из первой последовательности, меньшего, 
чем любой элемент из второй последовательноси
--}
(10, 20, 30) < (1, 2)
, 
{--
true - первый элемент первой последовательности меньше последнего
элемента второй последовательности
--}
(10, 20, 30) < (1, 20)
, 
{--
true - последний элемент первой последовательности равен 
единственному элементу второй последовательности
--}
(1, 2, 3) = (3)
, 
{--
true - последний элемент первой последовательности равен 
единственному элементу второй последовательности
/a - возвращает последовательность из двух элементов
--}
/a = 2
')

Операторы сравнения узлов

Операторы сравнения узлов (node comparison operators), как следует из названия, принимают в качестве типов операндов только узлы.

К операторам сравнения узлов относятся: is – проверка идентичности узлов, << и >> - сравнение позиции элементов относительно порядка документа.

Результатом применения операторов сравнения не всегда является булева переменная. Если в качестве одного или обоих операндов выступает пустая последовательность, результатом будет пустая последовательность.

          declare @xml xmlset @xml = '<a>1</a><a>2</a>'select @xml::query('
{-- true - первый узел а предшествует второму --}
/a[1] << /a[2]
, 
{-- false - первый узел а не предшествует второму --}
/a[1] >> /a[2]
, 
{-- false - первый узел а не является вторым узлом а --}
/a[1] is /a[2]
')

В SQL Server реализован еще один оператор сравнения узлов – isnot, который был в рабочих спецификациях в 2002 году, но отсутствует в нынешней рабочей спецификации. Результат его обратен результату оператора is.

Логические операторы

Логические операторы and и or остались на своем месте. Не поменялась ни семантика, ни синтаксис. В качестве типов обоих операндов всегда выступает логический тип – xs:boolean. Если операнд имеет отличный от xs:boolean тип, для него находится действительное булево значение (effective boolean value) путем применения функции fn:boolean.

Функция fn:boolean возвращает false в случае, когда операнд:

Во всех остальных случаях функция возвращает true.

        declare @xml xmlset @xml = '<a>1</a><a>2</a>'select @xml::query('
{-- true - действительное булево значение 2 является true --}
true() and 2
, 
{-- false - второй операнд является пустой последовательностью --}
true() and ()
, 
{-- true - действительное булево значение обоих операндов - true --}
"rosa" and "dima"
, 
{-- выберет оба узла а --}
/a[. = 1 or . = 2]
')

К сожалению, SQL Server не захотел принимать этот пример из-за последнего выражения, где выбираются XML-элементы a, выдавая такую ошибку: XQuery: Heterogeneous sequences are not allowed: found 'xs:boolean +' and 'element a *'. Очередной баг.

Условные выражения

Условное выражение присутствует в XQuery почти в паскалевском синтаксисе – if then else. Порядок обработки этой конструкции следующий: для выражения после if вычисляется эффективное булево значение. Если результат равен истине, вычисляется выражение после then, если ложь, вычисляется выражение после else. Конструкция else обязательна. Выражение после if обязано заключаться в скобки.

В качестве выражений then и else в этом примере используются конструкторы текстовых узлов.

FLWOR-выражения

FLWOR расшифровывается как for let where order by return. К сожалению, SQL Server не поддерживает ключевых слов LET и ORDER BY.

Бессмысленно говорить об отличиях в реализации SQL Server от текущего черновика стандарта, потому что фактически у них нет ничего общего, кроме трех ключевых слов – for, where и return.

FLWOR-выражения, являются, пожалуй самым мощным средством обработки последовательностей в XQuery. Синтаксис выражения в SQL Server примерно следующий (примерно, потому что в документации по SQL Server нигде не указан точный синтаксис FLWOR-выражения):

Смысл выражения FLWOR: в цикле каждый элемент последовательности, возвращаемой Expression, связывается с переменной, чье имя задается в VariableName. Далее, в каждой итерации выполняется выражение WhereExpression, и если его результат равен true – выполняется выражение ReturnExpression. Важно понимать, что ReturnExpression выполняется для каждого элемента последовательности, т.е. n раз, где n – размер последовательности. Если выражение WhereExpression вернуло false, выполняется следующая итерация. Общее количество итераций равно размеру последовательности Expression.

Названия переменных в XQuery всегда начинаются со знака доллара. Объявлять переменные можно только в FLWOR-выражениях, кванторных выражениях (см. ниже), в прологе запроса и при объявлении параметров функций. SQL Server не поддерживает переменные в прологе запроса и функции, так что объявить переменную можно только в FLWOR-выражениях и кванторных выражениях.

В самом FLWOR-выражении переменную можно объявлять в разделе for и в разделе let. Так как раздел let не поддерживается в SQL Server, переменные можно объявлять только в разделе for. Примеры объявления переменных в настоящем FLWOR-выражении находятся в конце этого раздела.

FLWOR-выражение в SQL Server нельзя применять для связывания нескольких XML-документов, так как отсутствует функция doc, которая возвращает узел документа по заданному URI. Возможно, в финальной версии появится подобная функция или функция расширения, которая будет принимать значение первичного ключа для данной таблицы и имя XML-поля. Фактически же, текущая версия вообще не поддерживает узел документа.

Пожалуй, FLWOR-выражение в SQL Server можно применять только для фильтрации элементов последовательности и трансформации структуры XML-фрагмента. В следующем примере демонстрируется и то, и другое: сначала выполняется фильтрация, а затем конструирование последовательности узлов элемента b.

Выражения FLWOR могут быть вложенными. Например:

        declare @xml xmlset @xml = '<a>10</a>, <a>2</a>, <b>4</b>, <a>3</a>, <b>6</b>'select @xml::query('
{-- для каждого элемента а --}
for $a in /a

  {-- для каждого элемента b --}
  for $b in /b

    {-- этот раздел return относится к внутреннему циклу for
    Поэтому исполняется 3 * 2 раз --}
    return <r>
    <expression>{$a/text()} * {$b/text()}</expression>
    <equals>{$a*$b}</equals>
    </r>
')

Здесь во внешнем цикле производится перебор элементов а, а во внутреннем – элементов b, и все элементы a умножаются на все элементы b. Результат:

<r><expression>10 * 4</expression><equals>40</equals></r>
<r><expression>10 * 6</expression><equals>60</equals></r>
<r><expression>2 * 4</expression><equals>8</equals></r>
<r><expression>2 * 6</expression><equals>12</equals></r>
<r><expression>3 * 4</expression><equals>12</equals></r>
<r><expression>3 * 6</expression><equals>18</equals></r>

Вследствие того, что SQL Server очень неважно соответствует стандарту, у вас может сложиться обманчивое впечатление, что выражения FLWOR не так круты, как их рекламируют. Ниже я попробую продемонстрировать возможности FLWOR из последнего черновика стандарта.

В качестве исходных данных будем использовать небольшую базу данных по форумам RSDN из статьи [6], которая, допустим, находится в файле forums.xml:

<?xmlversion="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>

И базу данных пользователей, которая находится в файле users.xml:

<?xmlversion="1.0"encoding="windows-1251"?>
<rsdn date="20/04/2004">
  <users>  
    <user nickname="Alexey Shirshov"name="Ширшов Алексей Николаевич" 
      posts="4327" articles="16" rate="5779" total-rating="27">
      <location from="Уфа" workin="Москва"/>
    </user>
    <user nickname="Alex Fedotov"name="Alex Fedotov" 
      posts="2617" articles="11" rate="6885" total-rating="31">
      <location from="" workin="San Francisco"/>
    </user>
    <user nickname="Vi2"name="Шарахов Виктор" 
      posts="3094" articles="1" rate="5922" total-rating="26">
      <location from="Ижевск" workin="Ижевск"/>
    </user>
    <user nickname="Sinclair"name="Антон Злыгостев" 
      posts="4325" articles="5" rate="11136" total-rating="39">
      <location from="Новосибирск" workin="Новосибирск"/>
    </user>
    <user nickname="Hacker_Delphi"name="Michael Polyudov" 
      posts="2740" articles="2" rate="1880" total-rating="12">
      <location from="Новосибирск" workin="Новосибирск"/>
    </user>
    <user nickname="_MarlboroMan_"name="Полюдов Дмитрий Петрович" 
      posts="2104" articles="1" rate="2798" total-rating="13">
      <location from="Новосибирск" workin="Новосибирск"/>
    </user>
    <user nickname="Merle"name="Ivan D. Bodiaguine" 
      posts="2049" articles="5" rate="4974" total-rating="23">
      <location from="Москва" workin="Москва"/>
    </user>
  </users>
</rsdn>

Выберем всех пользователей, которые являются модераторами форумов:

        (: для каждого модератора :)

        for $moders in doc("forums.xml")/rsdn/forums/forum/moderators/moderator/@name

(: загружаем его профиль по имени :)
let $user := doc("users.xml")/rsdn/users/user[@nickname = $moders]

(: и копируем его в результирующую последовательность :)
return $user

Теперь выберем всех пользователей, которые являются модераторами с количеством баллов больше среднего:

        (: выбираем всех пользователей :)

        let $user := doc("users.xml")/rsdn/users/user

(: считаем средний бал для всех пользователей :)
let $avg-rate := avg($user/@rate)

(: в цикле для каждого модератора :)
for $moders in doc("forums.xml")/rsdn/forums/forum/moderators/moderator/@name

(: находим соответствующий ему профиль :)
let $moder-user := $user[@nickname = $moders]

(: и фильтруем по баллам :)
where $moder-user/@rate > $avg-rate

(: копируем модератора в результирующую последовательность :)
return $moder-user

Следующий запрос возвращает модифицированный элемент top-poster из файла forums.xml:

        (: для каждого топ-постера :)

        for $top-poster in doc("forums.xml")/rsdn/forums/forum/top-poster

(: получаем его профиль :)
let $user := doc("users.xml")/rsdn/users/user[@nickname = $top-poster]
return(: атрибут nick - имя пользователя :)
<top-poster nick="{$top-poster}">
  <name>
  {
    (: копируем атрибут name с использованием
    конструктора xs:string (имя пользователя в миру) :)
    xs:string($user/@name)
  }</name>

  {
    (: копируем элемент location из профиля пользователя :)
    $user/location
  }

  <rating>
  {
    (: копируем значение атрибута total-rating :)
    $user/@total-rating
  }</rating>
</top-poster>

<?xml version="1.0" encoding="UTF-8"?>
<top-poster nick="Alex Fedotov">
   <name>Alex Fedotov</name>
   <location from="" workin="San Francisco"/>
   <rating total-rating="31"/>
</top-poster>
<top-poster nick="Vi2">
   <name>Шарахов Виктор</name>
   <location from="Ижевск" workin="Ижевск"/>
   <rating total-rating="26"/>
</top-poster>
<top-poster nick="Sinclair">
   <name>Антон Злыгостев</name>
   <location from="Новосибирск" workin="Новосибирск"/>
   <rating total-rating="39"/>
</top-poster>
<top-poster nick="Merle">
   <name>Ivan D. Bodiaguine</name>
   <location from="Москва" workin="Москва"/>
   <rating total-rating="23"/>
</top-poster>

И последний запрос: выберем сумму балов пользователей, проживающих в одном городе:

        (: для каждого города :)

        for $cities in distinct-values(doc("users.xml")/rsdn/users/user/location/@workin)

(: найдем пользователей, в нем проживающих :)
let $users-in-the-same-city := 
doc("users.xml")/rsdn/users/user[location/@workin = $cities]

(: отсортируем выходную последовательность в порядке, 
обратном сумме балов пользователей данного города :)
order by sum($users-in-the-same-city/@rate) descending(: сконструируем результат :)
return <city name="{$cities}" total-rate="{sum($users-in-the-same-city/@rate)}" />

Как видите, XQuery мало в чем уступает SQL, и даже, я уверен, значительно превосходит его по гибкости. Единственной проблемой остается производительность подобных запросов, но об этом поговорим в конце статьи.

Кванторные выражения

Кванторные выражения делятся на два типа: квантор существования, при котором определяется истинность условия для любого элемента последовательности, и квантор общности, при котором определяется истинность условия для каждого элемента последовательности.

Кванторное выражение служит примерно тем же целям, что и ключевые слова SOME и ANY в SQL. Например, следующий запрос возвращает все элементы а, которые больше всех элементов b:

Для большей наглядности перепишем предыдущий пример на SQL:

        --создание таблицы
        create
        table test(a int, b int)

--наполнение ее даннымиinsertinto test values(1, 2)
insertinto test values(1, 3)
insertinto test values(4, 1)

--запрос, возвращающий все значения поля а, которое больше--любого значения поля bselect a from test4 where a > any (select b from test4)

Сортировка

С сортировкой всегда была проблема. Редакторы спецификации все никак не могли решить – является ли она частью FLWR-выражения или нет. Если нет, то это должна быть универсальная конструкция, позволяющая сортировать любую последовательность. Подобное описание сортировки с использованием ключевого слова sortby можно найти в [10]. В SQL Server-е реализован именно такой метод, правда, с серьезным ограничением (или багом).

Здесь оператор sortby применяется к последовательности узлов а и сортирует их по их содержимому в порядке убывания. Этот оператор может сортировать любую последовательность, однако в следующем примере SQL Server видит какой-то подвох, и исполнять его отказывается, выдавая сообщение об ошибке: XQuery: Operand of 'sortby' has invalid type.

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

Надо сказать, что в текущей версии рабочего черновика стандарта оператор sortby отсутствует. Его заменил оператор order by в конструкции FLWOR. Функциональность от этого совершенно не пострадала. Например, предыдущие запросы переписываются так:

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

Выполнять сортировку можно по нескольким критериям (ordering specifications). Например:

Здесь сначала выполняется сортировка по значению узла элемента а, а затем – по значению узла атрибута b.

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

Ни один из этих параметров сортировки не поддерживается в SQL Server.

Заключение

Поддержка XML и сопутствующих технологий в SQL Server 2005 значительно расширена, но все еще не достаточно соответствует стандартам. Какие выгоды получает разработчик от такого расширения поддержки XML? Можно ли полностью полагаться на SQL Server как на высокопроизводительный и надежный XML Server? Это сложные вопросы и в данный момент на них нет однозначного ответа.

SQL Server хранит XML-документ в бинарном, но последовательном (линейном) виде. XML-документ не представлен в виде дерева – в нем открывающие и закрывающие теги просто заменены определенными кодами. Возможно, для запросов такой формат удобен, но мы же знаем, что перед исполнением любого запроса XQuery, SQL Server сначала строит реляционную модель XML-документа и транслирует запрос XQuery в запрос SQL. Данная модель хранения абсолютно не оптимизирована для изменения документа, так как при вставках данных приходится передвигать все дочерние и последующие узлы. Можно возразить, что хранить XML-документ как дерево очень не эффективно, так как дерево занимает в несколько раз больший объем. Совершенно верно, но есть модели, где не нужно хранить указатели на все дочерние узлы элемента.

Например, достаточно присвоить каждому узлу пару чисел (назовем их N и R) такую, что для всех прямых и косвенных дочерних узлов параметр N будет удовлетворять условию: Nparent > Ndescendant > R; а для всех последующих узлов параметр R будет удовлетворять условию: R > Nfollowing. В этом случае, узлы можно хранить обычным способом, как хранятся строки в РСУБД. Существуют и другие, более хитрые схемы [16, 17], но та, которая используется в SQL Server наиболее проста в реализации [12] и не оптимальна.

Скорость выполнения запросов XQuery пока также невелика по сравнению, например, с парсером XQuery из .NET Framework 2.0. Кроме того, что SQL Server-у необходимо преобразовать XML-данные в реляционную структуру, ему необходимо выбрать правильный план запроса. А как это возможно без статистики, я не представляю. Возможно, после построения индекса SQL Server начинает собирать определенную статистическую информацию, но это только догадки (вернее, пожелания). Почему разработчики не использовали тот же подход (или даже код), который используется в .NET Framework 2.0, где все запросы компилируются в MSIL, для меня также большая загадка.

Является ли SQL Server действительно подлинным XML database server? В [13] выдвигаются определенные требования к подобным серверам, среди которых:

Часть из них SQL Server поддерживает, часть нет. Лично мое мнение такое: если к официальной версии разработчики исправят очевидные ляпы, повысят уровень соответствия последним стандартам и оптимизируют хранение XML-фрагментов и исполнение запросов – можно смело переносить все свои XML-приложения на SQL Server. В противном случае, SQL Server в качестве XML database server можно использовать только в очень узком диапазоне задач.

10	целочисленный литерал типа xs:interger
10.1	вещественный литерал типа xs:decimal
101E-1	литерал с плавающей точкой типа xs:double
"string"	строковый литерал типа xs:string

1 + 2	выражение типа xs:integer
1 + 2.0	выполняется преобразование литерала 1 до типа xs:double. Тип результата – xs:double
"1" + 1	продвижение выполнить нельзя. Ошибка.

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

Часть 3. Новые возможности Microsoft SQL Server 2005 (Yukon)

Автор: Алексей ШиршовИсточник: RSDN Magazine #2-2004

Опубликовано: 16.10.2004Исправлено: 10.12.2016Версия текста: 1.0

Введение

Тип данных XML

Основы

Метод query

Метод value

Метод exist

Метод modify

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

XML-индекс

Хранение XML-типа

XML DML

Delete

Update

Insert

Поддержка XML Schema

Создание схемы

Удаление схемы

Ограничения текущей реализации

Безопасность

SQL-инструкции и XML

For xml, type

Вложенные запросы for xml

XSINIL

Генерация XSD на лету

sql:variable и sql:column

OPENXML

Поддержка XQuery

Основы

Пролог

Тело запроса

Конструкторы узлов

Операторы сравнения

Операторы сравнения атомарных значений

Общие операторы сравнения

Операторы сравнения узлов

Логические операторы

Условные выражения

FLWOR-выражения

Кванторные выражения

Сортировка

Заключение

Литература

Автор: Алексей Ширшов
Источник: RSDN Magazine #2-2004

Опубликовано: 16.10.2004
Исправлено: 10.12.2016
Версия текста: 1.0