XML по максимуму: добейтесь большего от SQL Server

Статья
02/10/2009

Автор:

Уорд Понд

Простой пример: пакетная проверка пользовательского ввода

Работа с XML-данными

Знакомимся с OPENXML

Освобождение памяти

SQL-синтаксис для возврата XML-данных

Как выглядит содержимое раздела EXPLICIT?

Миссия выполнена

XML-программирование в SQL Server 2005

Переработанный пример

Новая версия процедуры

Возврат результатов

SQL Server 2000 - первая версия флагманской СУБД Microsoft, в которой поддерживается создание и манипулирование XML-данными с помощью T-SQL.

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

Я расскажу, как обращаться к данным, которые содержатся в XML-строках, передаваемых хранимым процедурам, и как выводить XML-данные. На рис. 1 показана высокоуровневая архитектура этих операций. Я познакомлю вас со следующими синтаксическими конструкциями T-SQL, связанными с XML:

sp_xml_preparedocument;
OPENXML;
sp_xml_removedocument;
FOR XML EXPLICIT.

Dd451061.sql_server01(ru-ru,TechNet.10).gif

Рис. 1. Применение на практике некоторых новшеств в синтаксисе T-SQL

Простой пример: пакетная проверка пользовательского ввода

В SQL Server 2000 нет встроенного типа для хранения XML-данных. Для хранения XML-строк в переменных или полях таблиц применяют тип (n)(var)char или (n)text. Почти во всех случаях, встречающихся при разработке, такие переменные оказываются входными параметрами хранимых процедур; поэтому так будет и в моем примере. (Следует заметить, что передача кода T-SQL между уровнями всегда рискованна с точки зрения безопасности и, как правило, оказывается не лучшим вариантом с точки зрения производительности; это достаточно веские причины, чтобы никогда так не поступать.)

Рассмотрим хранимую процедуру sptxValidateLookupData, разработанную моей группой несколько лет назад. Она предназначена для проверки на допустимость данных, вводимых пользователем в рабочий процесс определенной системы. Чтобы свести к минимуму количество обменов с базой данных, разработчики системы решили накапливать весь пользовательский ввод и передавать информацию, которую требуется проверять на допустимость, в базу данных в одном XML-документе. Хранимая процедура выполняет проверку на допустимость и возвращает вызывающему процессу результаты, также помещая их в один XML-документ. Процедура sptxValidateLookupData обслуживает несколько разных рабочих процессов, поэтому в одном и том же пакете могут запрашиваться проверки "существует" или "не существует" для любого значения (datum) или области (domain). В следующем фрагменте кода показаны типичные входные XML-данные этой хранимой процедуры:

<ValidateData>
<Validate Type="Countries" Name="CountryCode"
  Value="JA" Test="Not Exists"/>
<Validate Type="Countries" Name="CountryCode"
  Value="BO1" Test="Exists"/>
<Validate Type="Languages" Name="LanguageCode"
  Value="EN" Test="Exists"/>
<Validate Type="ContactPreferences"
  Name="ContactPreferenceCode" Value="EN"
  Test="Exists"/>
</ValidateData>

В корневом узле <ValidateData> содержатся подузлы <Validate>, описывающие, какую проверку нужно выполнить. Type задает область, для которой выполняется проверка, Name - проверяемый атрибут, а Test - тип проверки (существует ли значение Value в поле, указанном атрибутом Name, в области Type). Заметьте: этот фрагмент XML-данных описывает четыре операции проверки на допустимость для трех областей, но версия хранимой процедуры, которая используется в настоящее время, поддерживает 17 областей и любое число проверок. Таким образом, у нас есть компонент проверки на допустимость, обеспечивающий многократное использование кода, отличную расширяемость и высокую производительность, удобный в сопровождении, а главное - простой до неприличия!

Результаты также возвращаются в XML-формате. Если все проверки для данного потока возвратили TRUE (т. е. если истинны утверждения, задаваемые в узлах Test), возвращается пустой тэг <Results/>. Однако, если какие-либо проверки потерпели неудачу, возвращается список ошибок:

<Errors>
<Error ErrorMessage="JA exists in Countries"
  FieldName="CountryCode"/>
<Error ErrorMessage="BO1 does not exist in Countries"
  FieldName="CountryCode"/>
<Error ErrorMessage="EN does not exist in ContactPreferences"
  FieldName="ContactPreferenceCode"/>
</Errors>

Работа с XML-данными

В SQL Server 2000 XML-данные передаются процессу T-SQL как простые строки. Чтобы с ними можно было работать как с реляционными или иерархическими данными, необходимо дать SQL Server "понять", что это XML-данные, - "подготовить" их. Для этого вызывается системная хранимая процедура sp_xml_preparedocument. Давайте посмотрим начало хранимой процедуры sptxValidateLookupData (листинг 1). Системная хранимая процедура sp_xml_preparedocument считывает XML-текст, передаваемый как входной параметр (параметр @XMLString в вызове в листинге 1), затем передает текст анализатору MSXML и формирует проанализированный документ, готовый к обработке функцией OPENXML, возвращающей набор записей.

Этот документ является иерархическим представлением различных узлов XML-документа (элементов, атрибутов, текста, комментариев и т.д.). Оно хранится в кэше сервера, максимальный размер которого равен одной восьмой от общего объема памяти сервера. Поэтому необходимо аккуратно работать с этим внутренним представлением в интенсивно используемых системах или в средах с ограниченным объемом памяти.

Листинг 1. Хранимая процедура sptxValidateLookupData


create procedure sptxValidateLookupData
@XMLString ntext
as

set nocount on

declare @idoc int, @Name nvarchar(30), @Value nvarchar(300),
        @Test nvarchar(30), @Result int, @Type nvarchar(30),
        @TestResult int

--    Готовим входные XML-данные к выборке с помощью OPENXML
exec sp_xml_preparedocument @idoc OUTPUT, @XMLString

Процедура sp_xml_preparedocument возвращает описатель (значение @idoc в вызове в листинге 1), через который можно обращаться к созданному внутреннему представлению XML-документа. Это значение используется в качестве параметра функции OPENXML, возвращающей набор записей для оператора SELECT. OPENXML - "мост" к подготовленному XML-документу, и ее можно указывать в операторе SELECT аналогично таблице или представлению (листинг 2).

Я создал и заполнил табличную переменную, чтобы не выполнять многократные вызовы OPENXML, которые были бы менее эффективны, чем многократное обращение к табличной переменной. Лучше копировать содержимое XML-документов в табличные переменные, чтобы избавиться от многократного неэффективного доступа к XML-данным (хотя, если вы собираетесь обратиться к XML-данным только один раз, просто выполните запрос с OPENXML без предварительного создания табличной переменной). Второй оператор на рис. 3 копирует входные XML-данные в табличную переменную @tempValidateLookupData.

Листинг 2. Создание табличной переменной


--  Создаем табличную переменную для хранения
--  данных о проверках
declare @tempValidateLookupData table (
    [Type] nvarchar(30),
    [Name] nvarchar(30),
    [Value] nvarchar(300),
    [Test] nvarchar(30),
    [TestResult] int
)

--  Заполняем табличную переменную данными о проверках,
--  которые требуется выполнить
insert @tempValidateLookupData
select [Type], [Name], [Value], [Test], NULL
    from OPENXML (@idoc, '/ValidateData/Validate')
    with ([Type] nvarchar(30), [Name] nvarchar(30),
        [Value] nvarchar(300), [Test] nvarchar(30))

Знакомимся с OPENXML

Теперь повнимательнее рассмотрим синтаксис OPENXML:

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]])
[WITH (SchemaDeclaration | TableName)]

Параметр idoc - описатель документа, созданный для внутреннего представления XML-документа. Другими словами, это значение, ранее возвращенное процедурой sp_xml_preparedocument. Заметьте: в одной хранимой процедуре можно манипулировать несколькими XML-строками. В таком случае необходимо вызвать sp_xml_preparedocument для каждой XML-строки и объявить по отдельной переменной для хранения каждого возвращенного описателя.

Параметр rowpattern идентифицирует узлы XML-документа, связанного с описателем idoc, которые должны обрабатываться как записи. Он указывает XML-анализатору, где в XML-документе находятся интересующие вас данные.

Параметр flags задает, какое сопоставление используется при запросе в первую очередь - ориентированное на атрибуты (attribute-centric) или на элементы (element-centric). Если этот параметр опущен, SQL Server 2000 по умолчанию применяет сопоставление, ориентированное на атрибуты, что вполне подходит для нашего случая. Подробнее об этом параметре см. описание функции OPENXML в SQL Server Books Online.

Раздел WITH функции OPENXML указывает SQL Server 2000, какие типы данных SQL сопоставляются содержимому XML-документа. Вы можете либо явно задать поля, либо сослаться на таблицу базы данных с подходящей структурой. Каждое поле XML-документа, используемое в операторе SELECT, должно присутствовать в разделе WITH (описываться явно или в таблице). В рассматриваемом примере:

--  Заполняем табличную переменную данными о проверках,
--  которые требуется выполнить
insert @tempValidateLookupData
select [Type], [Name], [Value], [Test], NULL
    from OPENXML (@idoc, '/ValidateData/Validate')
    with ([Type] nvarchar(30), [Name] nvarchar(30),
    [Value] nvarchar(300), [Test] nvarchar(30))

имена полей в операторе SELECT и в разделе WITH должны соответствовать именам атрибутов во входном XML-документе. Также заметьте, что, поскольку XML-данные рассматриваются как набор записей, программа проверки на допустимость будет правильно работать при любом количестве значений (хоть при одном, хоть при десяти): вызывающий процесс просто передает столько узлов XML-документа, сколько нужно проверить на допустимость.

Освобождение памяти

Внутреннее представление XML-документа остается в памяти, пока соединение процесса с сервером не будет закрыто или сброшено или пока память не освободят явно. Следует вызывать системную процедуру sp_xml_removedocument как можно раньше, поскольку лучше поскорее освободить эту память, чтобы на сервере было доступно больше ресурсов (для освобождения памяти требуется указать описатель):

--    Освобождаем память сервера, которая используется
--    образом входных XML-данных, созданным OPENXML
exec sp_xml_removedocument @idoc

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

После разбора XML-документа процедура sptxValidateLookupData выполняет большой оператор IF, чтобы выбрать требуемую проверку и занести в переменную @TestResult значение, соответствующее результатам проверки. Затем результаты проверки используются при обновлении табличной переменной (листинг 3).

Листинг 3. Выбор требуемой проверки


--  Заносим результаты проверок в табличную переменную,
--  перебирая записи, у которых поле TestResult содержит NULL
while exists (select TestResult from @tempValidateLookupData
    where TestResult is null) begin

--  Извлекаем данные из записи, проверяемой на допустимость
    Select top 1 @Type=[Type], @Name=[Name], @Value=Value,
        @Test=Test
        from @tempValidateLookupData
        where TestResult is null

--  И выполняем соответствующую проверку...

--  Проверка для области Countries (взята в качестве примера)
    if    @Type = 'Countries' begin
        if exists (select CountryCode from dbo.Country where
            CountryCode = convert(nvarchar(4), @Value))
            select    @TestResult =
            CASE    when @Test = 'Exists' then 1
                else 0
            end
        else
            select    @TestResult =
            CASE    when @Test = 'Not Exists' then 1
                else 0
            end
    end

--  (16 других проверок на допустимость опущены для краткости)

--  Обновляем соответствующую запись: заносим в нее результат
--  проверки
    update   @tempValidateLookupData
    set      TestResult = @TestResult
    where    Name = @Name
    and      Value = @Value
    and      Test = @Test
end

SQL-синтаксис для возврата XML-данных

Итак, проверки на допустимость выполнены, теперь нужно возвратить результаты вызывающему процессу. SQL Server 2000 поддерживает несколько механизмов вывода XML-данных с помощью директивы FOR XML оператора SELECT. Прежде чем продолжить рассказ о sptxValidateLookupData, я кратко рассмотрю еще кое-какие новшества в синтаксисе языка SQL.

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

Как выглядит содержимое раздела EXPLICIT?

Существует ряд жестких синтаксических требований к формированию запросов, использующих режим EXPLICIT. Каждый запрос с режимом EXPLICIT должен содержать два поля метаданных. У первого поля, указываемого в операторе SELECT, должно быть имя Tag и тип int. Это номер тэга текущего элемента, т. е. фактически номер типа поддерева. У второго поля должно быть имя Parent и тоже тип int. Оно содержит номер тэга элемента, который является родителем текущего элемента. Эти поля описывают иерархию XML-дерева. Если поле Parent записи имеет значение 0 или NULL, его данные располагаются на вершине XML-иерархии. Кроме этого единственного исключения, все значения поля Parent должны соответствовать ранее объявленным значениям тэгов. Заметьте: набор результатов должен содержать ровно одну запись, у которой поле Parent содержит 0 или NULL, причем она должна быть первой в наборе результатов (если имеется более одной записи, у которой номер тэга родителя равен 0 или NULL, генерируется XML-фрагмент).

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

[ElementName!TagNumber!AttributeName!Directive]

где ElementName - имя элемента (если в качестве ElementName указано "Countries", результатом будет <Countries>), а TagNumber - номер тэга элемента. TagNumber вместе с полями метаданных Tag и Parent описывает иерархию XML-дерева. Каждый TagNumber соответствует одному ElementName, а AttributeName является именем XML-атрибута (если оно задано).

Рассмотрение использования Directive и работы в нескольких ситуациях, в которых AttributeName может быть NULL, выходит за рамки данной статьи. Раздел "Using Explicit Mode" в SQL Server 2000 Books Online - превосходный источник дополнительной информации.

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

Вернемся к sptxValidateLookupData. Как вы помните, в описании процедуры говорилось о двух форматах возвращаемого набора результатов. Если все проверки данного пакета возвратили TRUE, я возвращаю пустой тэг <Results/>. Но если какая-то проверка потерпела неудачу, я возвращаю XML-документ со списком ошибок.

В листинге 4 приведен код процедуры sptxValidateLookupData, который генерирует наборы результатов с помощью FOR XML EXPLICIT. Синтаксис режима EXPLICIT довольно многословен, поэтому рассмотрим его по частям. Первая ветвь оператора IF обрабатывает простую ситуацию, когда все мои проверки возвратили TRUE:

-- Если все проверки на допустимость пройдены,
-- возвращаем пустой набор XML-данных...
if not exists (select [TestResult] from
    @tempValidateLookupData where TestResult = 0)
    select 1 as TAG, 0 as parent,
    NULL as [Results!1!]
    for xml explicit

Листинг 4. Применение FOR XML EXPLICIT


-- Если все проверки на допустимость пройдены,
-- возвращаем пустой набор XML-данных...
if not exists (select [TestResult] from
    @tempValidateLookupData where TestResult = 0)
    select 1 as TAG, 0 as parent,
    NULL as [Results!1!]
    for xml explicit

-- ...иначе возвращаем XML-данные о проверках,
-- потерпевших неудачу
else
    select 1 as TAG, 0 as parent,
    NULL as [Errors!1!],
    NULL as [Error!2!],
    NULL as [Error!2!ErrorMessage],
    NULL as [Error!2!FieldName]

    union all

    select 2 as TAG, 1 as parent,
    NULL,
    NULL,
    ltrim(rtrim(value)) + ' does not exist in ' + type,
    [name]

    from @tempValidateLookupData
    where [TestResult] = 0
    and test = 'Exists'

    union all

    select 2 as TAG, 1 as parent,
    NULL,
    NULL,
    ltrim(rtrim(value)) + ' already exists in ' + type,
    [name]

    from @tempValidateLookupData
    where [TestResult] = 0
    and test = 'Not Exists'

    for xml explicit

Поскольку AttributeName не задано, этот оператор создаст единственный XML-элемент Results без атрибутов и потомков: <Results/>

Ветвь ELSE гораздо интереснее: в ней я формирую XML-данные о проверках, потерпевших неудачу, объединяя операторы SELECT с помощью UNION. Как показано в листинге 5, каждый запрос формирует узлы выходных XML-данных определенного типа (который описывается в комментариях после каждого запроса).

Листинг 5. Формирование узлов

-- ...иначе возвращаем XML-данные о проверках,
-- потерпевших неудачу
else
    select 1 as TAG, 0 as parent,
    NULL as [Errors!1!],
    NULL as [Error!2!],
    NULL as [Error!2!ErrorMessage],
    NULL as [Error!2!FieldName]

--    (приведенный выше запрос формирует элемент <Errors>)

    union all

    select 2 as TAG, 1 as parent,
    NULL,
    NULL,
    ltrim(rtrim(value)) + ' does not exist in ' + type,
    [name]

    from @tempValidateLookupData
    where [TestResult] = 0
    and test = 'Exists'

--    (приведенный выше запрос формирует узлы вида
--    <Error ErrorMessage="BO1 does not exist in Countries"
--        FieldName="CountryCode"/>)

    union all

    select 2 as TAG, 1 as parent,
    NULL,
    NULL,
    ltrim(rtrim(value)) + ' exists in ' + type,
    [name]

    from @tempValidateLookupData
    where [TestResult] = 0
    and test = 'Not Exists'

--    (приведенный выше запрос формирует узлы вида
--    <Error ErrorMessage="JA exists in Countries"
--        FieldName="CountryCode"/>)

    for xml explicit

Заметьте: запросы, объединенные UNION, упорядочены так, чтобы каждый потомок шел сразу за своим родителем, хотя узлы могут быть потомками нескольких подзапросов (у запросов "exists" и "not exists" поля tag и parent имеют одинаковые значения). Когда запросы, объединенные с помощью UNION, обрабатываются в режиме FOR XML EXPLICIT, сериализатор FOR XML вычисляет имена полей [ElementName!TagNumber!AttributeName!Directive] и значения полей метаданных Tag и Parent, а затем выводит XML-иерархию, заданную программистом:

<Errors>
  <Error ErrorMessage="JA exists in Countries"
    FieldName="CountryCode"/>
  <Error ErrorMessage="BO1 does not exist in Countries"
    FieldName="CountryCode"/>
  <Error ErrorMessage="EN does not exist in ContactPreferences"
    FieldName="ContactPreferenceCode"/>
</Errors>

Миссия выполнена

Имея в распоряжении описанные выше средства, вы можете запрашивать и генерировать XML-данные в среде SQL Server 2000. Но какими бы мощными ни были эти средства, SQL Server 2005 предоставит еще больше программных функций всем специалистам, обрабатывающим XML-данные с помощью T-SQL, и позволит добиться еще большей производительности.

XML-программирование в SQL Server 2005

При разработке SQL Server 2005 Microsoft значительно усовершенствовала поддержку XML. Последние два года я создавал новую систему на основе внутренних версий этого продукта (рай для SQL-разработчика!) и рад сообщить, что программистов, использующих XML, ждет масса хороших новостей.

Весь синтаксис поддержки XML, введенный в SQL Server 2000, не изменился, но в SQL Server 2005 в него внесена уйма дополнений. Я рассмотрю некоторые из них и покажу, как изменится хранимая процедура sptxValidateLookupData, рассмотренная в статье. Я также расскажу о следующих новшествах SQL Server 2005: о типах данных XML, FOR XML PATH, TYPE, синтаксисе nodes().

Все методики работы с XML, применяемые в SQL Server 2000 (передача XML-строк хранимым процедурам в параметрах типа ntext, манипулирование ими с помощью sp_xml_preparedocument, sp_xml_removedocument, FOR XML EXPLICIT и OPENXML), доступны и в SQL Server 2005, но в новой версии появились средства, позволяющие использовать другие подходы. Как только вы поработаете с новым синтаксисом, вам уже не захочется возвращаться к старому.

В SQL Server 2005 внесены революционные изменения в сам механизм хранения данных, в частности введены три новых типа данных: nvarchar(max), varbinary(max) и xml. В T-SQL возникали сложности с обработкой значений типа ntext, поэтому, если вы собираетесь по-прежнему использовать те же конструкции работы с XML, что и в SQL Server 2000, имеет смысл отказаться от ntext и перейти на nvarchar(max) и varbinary(max). Однако наиболее интересен тип данных XML, также добавленный в ядро сервера. Он предоставляет программистам ряд совершенно новых возможностей.

Переработанный пример

Как вы помните, хранимая процедура sptxValidateLookupData, рассмотренная в статье, поддерживает единый процесс пакетной проверки пользовательского ввода в Web-страницы, позволяющий выполнить несколько разных проверок на допустимость для значений, получаемых из гетерогенных источников. Директивы, описывающие проверки, передаются хранимой процедуре в одном XML-документе. Хранимая процедура выполняет проверки и возвращает результаты вызывающему процессу, тоже в одном XML-документе.

Новая версия процедуры

Как и в процедуре для SQL Server 2000, я помещаю содержимое входного XML-документа в табличную переменную. Но я больше не вызываю sp_xml_preparedocument и не применяю OPENXML. Как показано в листинге 6, я изменил тип данных параметра на xml (с ntext) и воспользовался новым синтаксисом nodes().

Листинг 6. Использование типа данных XML

    
create procedure sptxValidateLookupData
@XMLString xml
as

set nocount on

declare @idoc int, @Name nvarchar(30), @Value nvarchar(300),
        @Test nvarchar(30), @Result int, @Type nvarchar(30),
        @TestResult int

--  Создаем табличную переменную для хранения
--  данных о проверках
declare @tempValidateLookupData table (
    [Type] nvarchar(30),
    [Name] nvarchar(30),
    [Value] nvarchar(300),
    [Test] nvarchar(30),
    [TestResult] int
)

--  Заполняем табличную переменную данными о проверках,
--  которые требуется выполнить
insert  @tempValidateLookupData
select  ref.value ('@Type', '[nvarchar](30)'),
        ref.value ('@Name', '[nvarchar](30)'),
        ref.value ('@Value', '[nvarchar](300)'),
        ref.value ('@Test', '[nvarchar](30)'),
        NULL
        from @XMLString.nodes('/ValidateData/Validate')
        as node(ref)

Давайте повнимательнее рассмотрим метод nodes. Он позволяет получить ссылку для каждой записи, которая соответствует элементу Validate, находящемуся внутри элемента ValidateData переменной @XMLString. Эта переменная описывается инструкцией AS NODE(ref) как набор записей, представляющий узлы (node rowset). В каждом определении поля в операторе SELECT вызывается метод value, извлекающий значение заданного атрибута. Он выполняется для каждой записи, поэтому для каждого элемента Validate с параметрами проверки генерируется по одной записи.

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

Логика выполнения проверок на допустимость в новой версии sptxValidateLookupData осталась прежней.

Возврат результатов

Как вы помните, в исходной спецификации процедуры описывались два формата возвращаемого набора записей. Если все проверки данного пакета возвратили TRUE, набор должен содержать пустой тэг <Results/>. Однако, если какие-то проверки потерпели неудачу, нужно вернуть список XML-данных об ошибках.

В листинге 7 приведен новый код процедуры sptxValidateLookupData, генерирующий наборы результатов с помощью FOR XML PATH, TYPE. Обратите внимание, насколько этот синтаксис компактнее старого синтаксиса FOR XML EXPLICIT.

Листинг 7. Применение FOR XML PATH

    
-- Если все проверки на допустимость пройдены,
-- возвращаем пустой набор XML-данных...
if not exists (select [TestResult]
    from @tempValidateLookupData
    where TestResult = 0)
    select null
    for xml path ('Results'), type

-- ...иначе возвращаем XML-данные о проверках,
-- потерпевших неудачу
else
    select null,
    (
        select
        ltrim(rtrim(value)) + ' does not exist in ' +
        type AS '@ErrorMessage',
        [name] AS '@FieldName'

        from @tempValidateLookupData
        where [TestResult] = 0
        and test = 'Exists'

        for xml path ('Error'), type
    ),
    (
        select
        ltrim(rtrim(value)) + ' already exists in ' +
        type AS '@ErrorMessage',
        [name] AS '@FieldName'

        from @tempValidateLookupData
        where [TestResult] = 0
        and test = 'Not Exists'

        for xml path ('Error'), type
    )
    for xml path ('Errors'), type

Как и прежде, первая ветвь оператора IF обрабатывает простой случай, когда все проверки возвратили TRUE:

-- Если все проверки на допустимость пройдены,
-- возвращаем пустой набор XML-данных...
if not exists (select [TestResult] from
  @tempValidateLookupData where TestResult = 0)
    select null
    for xml path ('Results'), type

Ветвь ELSE, как и раньше, формирует XML-данные для проверок, потерпевших неудачу, но на сей раз с помощью нескольких вложенных операторов SELECT FOR XML PATH, TYPE. Явно заданные имена полей указывают SQL Server, что их нужно использовать в качестве имен XML-атрибутов (перед ними должен идти символ @, их надо заключить в одинарные кавычки). Директивы FOR XML PATH ('Error') указывают серверу, что требуется обернуть XML-данные, создаваемые внутренними операторами SELECT, элементом Error, а директива FOR XML PATH ('Errors') - что внешний SELECT формирует корневой элемент 'Errors'. Таким образом, этот SQL-код генерирует тот же набор результатов, что и прежде, но с помощью гораздо более лаконичного запроса, чем запрос с FOR XML EXPLICIT.

Может, на первый взгляд это и не очевидно, но отказ от запросов SELECT, объединенных операторами UNION, выполняемых при использовании FOR XML EXPLICIT, делает код гетерогенных запросов гораздо компактнее и удобнее в сопровождении. Например, недавно моя группа переписала одну пользовательскую функцию SQL Server 2000 длиной в 5000 строк (большую их часть составлял 43-уровневый запрос с FOR XML EXPLICIT). Новый синтаксис позволил уложиться в 497 строк.

Если вы хотите по-прежнему придерживаться модели с UNION (что сомнительно), то можете написать второй запрос SELECT так, как показано в листинге 8. Этот подход не сработал бы, если бы ваши внутренние XML-узлы содержали разные количества атрибутов. Одно из основных преимуществ нового вложенного синтаксиса в том, что в отличие от операторов SELECT с UNION количество полей в подзапросах не обязательно должно быть одинаковым. Тем не менее, пример в листинге 8 демонстрирует еще одну новую конструкцию - FOR XML ROOT. Она позволяет объявить корневой элемент возвращаемых XML-данных, не создавая явный запрос SELECT. Дополнительные сведения по этому вопросу см. в статье "XML Options in Microsoft SQL Server 2005".

Листинг 8. Применение FOX XML ROOT

select
    ltrim(rtrim(value)) + ' does not exist in    ' +
    type AS '@ErrorMessage',
    [name] AS '@FieldName'

    from @tempValidateLookupData
    where [TestResult] = 0
    and test = 'Exists'

        union all

        select
        ltrim(rtrim(value)) + ' already exists in ' +
        type AS '@ErrorMessage',
        [name] AS '@FieldName'

        from @tempValidateLookupData
        where [TestResult] = 0
        and test = 'Not Exists'

        for xml path ('Error'), root
            ('Errors'), type

С помощью этих новых средств SQL Server 2005 вы можете запрашивать и генерировать XML-данные еще эффективнее, чем раньше. Я рассмотрел лишь небольшую часть возможностей нового синтаксиса. SQL Server 2005 позволяет делать с XML-данными почти все, что угодно. Если вас интересуют ресурсы с самой свежей информацией о SQL Server 2005, посетите сайт "Introducing SQL Server 2005".

XML по максимуму: добейтесь большего от SQL Server

Простой пример: пакетная проверка пользовательского ввода

Работа с XML-данными

Листинг 1. Хранимая процедура sptxValidateLookupData

Листинг 2. Создание табличной переменной

Знакомимся с OPENXML

Освобождение памяти

Листинг 3. Выбор требуемой проверки

SQL-синтаксис для возврата XML-данных

Как выглядит содержимое раздела EXPLICIT?

Листинг 4. Применение FOR XML EXPLICIT

Листинг 5. Формирование узлов

Миссия выполнена

XML-программирование в SQL Server 2005

Переработанный пример

Новая версия процедуры

Листинг 6. Использование типа данных XML

Возврат результатов

Листинг 7. Применение FOR XML PATH

Листинг 8. Применение FOX XML ROOT

Дополнительные ресурсы