На главную страницу
Мастерская XML - FOR XML PATH
Jacob Sebastian (оригинал: XML Workshop - FOR XML PATH)
Перевод Моисеенко С.И.
Введение
В последних 2 выпусках по обработке XML, мы видели различные примеры чтения и генерации
XML-данных. Если Вы пропустили два последних выпуска, то можете найти их здесь:
1. Расширенная
обработка XML
Здесь приводятся несколько примеров, которые генерируют данные XML, используя ключевые
слова FOR XML. Статья демонстрирует использование режимов AUTO и RAW.
2. Дополнительные
примеры расширенныой обработки XML
В этой статье приводится несколько примеров, которые демонстрируют извлечение значений
из переменной XML или столбца типа XML.
Сейчас мы рассмотрим примеры обработки XML, используя режим PATH. Я предпочел бы,
чтобы Вы сначала прочитали предыдущую
статью, в которой приводится несколько примеров, использующих режимы RAWи
AUTO.
FOR XML PATH
Режимы RAW и AUTO достаточно хороши для большинства требований к форматированию
XML. Режим PATH обеспечивает несколько дополнительных возможностей форматирования,
которые мы исследуем с помощью приведенных ниже примеров.
Пример 1: Генерация иерархии узлов XML
/*
Используя режим PATH, вы можете генерировать иерархию узлов XML. Генерация XML управляется
АЛИАСОМ столбца. В приведенном ниже примере создается новый узел <item>, который
содержит "ItemNumber" и "Quantity". Обратите внимание на слэш
("/"), используемый в имени столбца, который фактически управляет генерацией
XML.
*/
SELECT
OrderNumber AS 'orderNumber',
ItemNumber AS 'item/itemNumber',
Qty AS 'item/Quantity'
FROM OrderDetails FOR XML PATH('orderInfo'), TYPE, ELEMENTS, ROOT('order')
/*
Результат:
00001
-
A001
10
00001
-
A002
20
*/
Пример 2: Генерация списка значений
/*
Мы только что видели, как ключевое слово PATH может быть использовано для реструктуризации
XML, сгенерированного с помощью FOR XML. Давайте теперь рассмотрим несколько других
сценариев, когда ключевое слово PATH оказывается полезным.
Одной из ситуаций, когда ключевое слово PATH может оказаться полезным, является
создание списка значений. Следующий запрос возвращает список номеров продукции (Item
Numbers) из таблицы OrderDetails.
*/
SELECT ItemNumber AS 'data()' FROM OrderDetails FOR XML PATH('')
/*
Результат:
A001 A002 A003
*/
/*
Заметьте, что данный запрос возвращает результаты в виде значения с разделителями-пробелами.
Большую часть времени мы работаем со значениями, разделенными ЗАПЯТЫМИM, а не пробелами.
Поэтому давайте посмотрим, как сгенерировать список с запятыми в качестве разделителей.
Нет прямого способа сделать это. Однако мы можем выполнить маленький трюк. Взгляните
на следующий пример
*/
SELECT ',' + ItemNumber AS 'data()' FROM OrderDetails FOR XML PATH('')
/*
Результат:
,A001 ,A002 ,A003
*/
/*
Хорошо, мы почти добились успеха. Одно плохо, - наша строка начинается с ЗАПЯТОЙ.
Давайте воспользуемся функцией STUFF для ее удаления. Вот окончательный запрос.
*/
SELECT STUFF( (SELECT ',' + ItemNumber AS 'data()' FROM OrderDetails FOR XML PATH('')),1,1,'')
/*
Результат:
A001 ,A002 ,A003
*/
Я только что обнаружил блог Jamie, который считает, что использование вышеприведенного
подхода лучше других вариантов создания списка значений. Вы можете найти этот блог
по
ссылке.
Сравнение PATH с AUTO и RAW
PATH обеспечивает больше возможностей управления иерархией посредством манипулирования
алиасом столбца. Это представляется очень удобным в большинстве случаев. EXPLICIT
обеспечивает больше средств контроля над генерацией XML, но его использование довольно
сложно. Я представлю пошаговое освоение использования EXPLICIT в следующей статье.
Давайте попробуем разобраться на примере как каждый режим XML (AUTO, RAW и PATH)
может быть использован для выполнения одной и той же задачи. Пусть у нас имеется
3 таблицы: Клиент (Customer), Город (City) и Страна (Country). Нам требуется извлечь
данные из этих таблиц и представить их в структуре XML, показанной ниже.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Запустите скрипт для создания таблиц и наполнения их нужными нам данными:
CREATE TABLE Countries (CountryID INT, CountryName VARCHAR(20), Currency VARCHAR(20))
CREATE TABLE Cities (CityID INT, CityName VARCHAR(20), CountryID INT)
CREATE TABLE Customers (CustomerNumber VARCHAR(2), CustomerName VARCHAR(40), Phone
VARCHAR(20), CityID INT)
INSERT INTO Countries(CountryID, CountryName, Currency)
SELECT 1 AS CountryID, 'USA' AS CountryName, 'US Dollars'
as Currency UNION
SELECT 2, 'England', 'Pound Sterling' UNION
SELECT 3, 'India', 'Rupee'
INSERT INTO Cities(CityID, CityName, CountryID)
SELECT 1 AS CityID, 'NY' AS CityName, 1 AS CountryID
UNION
SELECT 2, 'NJ', 1 UNION
SELECT 3, 'London', 2 UNION
SELECT 4, 'New Delhi', 3
INSERT INTO Customers(CustomerNumber, CustomerName, Phone, CityID)
SELECT 'MK' AS CustomerNumber, 'John Mark' AS CustomerName,
'111-111-1111' AS Phone, 1 AS CityID UNION
SELECT 'WS', 'Will Smith', '222-222-2222', 1 UNION
SELECT 'EN', 'Elizabeth Lincoln', '333-333-3333', 2
UNION
SELECT 'TH', 'Thomas Hardy', '444-444-4444', 3 UNION
SELECT 'JS', 'Jacob Sebastian', '555-555-5555', 4
Позвольте мне начать с PATH и посмотреть, сможем ли мы создать структуру XML с его
помощью. Напишем первую версию нашего кода TSQL следующим образом
SELECT
Country.CountryName AS 'country/name',
Country.Currency AS 'country/currency',
City.CityName AS 'country/city/name',
Customer.CustomerNumber AS 'country/city/customer/id',
Customer.CustomerName AS 'country/city/customer/name',
Customer.Phone AS 'country/city/customer/phone'
FROM
Customers Customer
INNER JOIN Cities City ON (City.CityID = Customer.CityID)
INNER JOIN Countries Country ON (Country.CountryID
= City.CountryID)
ORDER BY CountryName, CityName
FOR XML PATH
/*
Результат:
England
Pound Sterling
London
TH
Thomas Hardy
444-444-4444
India
Rupee
New Delhi
JS
Jacob Sebastian
555-555-5555
...
...
*/
Хорошо, но не очень. Вы, должно быть, заметили, что формат не совсем тот, который
нам нужен. Нам нужны значения как Атрибуты, а наш запрос возвращает значения как
XML-узлы. Давайте теперь рассмотрим следующий запрос, который дает результаты, где
значения представлены как Атрибуты.
SELECT
Country.CountryName AS 'country/@name',
Country.Currency AS 'country/@currency',
City.CityName AS 'country/city/@name',
Customer.CustomerNumber AS 'country/city/customer/@id',
Customer.CustomerName AS 'country/city/customer/@name',
Customer.Phone AS 'country/city/customer/@phone'
FROM
Customers Customer
INNER JOIN Cities City ON (City.CityID = Customer.CityID)
INNER JOIN Countries Country ON (Country.CountryID
= City.CountryID)
ORDER BY CountryName, CityName
FOR XML PATH(''), ROOT('CustomersByRegion')
/*
*/
Мы получили результаты очень близкие к тому, что нам требуется. Но мы кое-что пропускаем.
Обратите внимание, что результаты не сгруппированы. USA имеет 3 узла в данных XML,
полученных при выполнении запроса. Их следует сгруппировать в один узел. Однако
PATH не предоставляет способа сделать это. Давайте обратимся к другим режимам, чтобы
проверить, могут ли они нам помочь в этом сценарии.
Перейдем к режиму AUTO. Следующим кодом мы пытаемся создать необходимую структуру
XML, используя метод AUTO.
SELECT
Country.CountryName AS [name],
Country.Currency, City.CityName AS [name],
Customer.CustomerNumber AS [id],
Customer.CustomerName AS [name],
Customer.Phone
FROM
Customers Customer
INNER JOIN Cities City ON (City.CityID = Customer.CityID)
INNER JOIN Countries Country ON (Country.CountryID
= City.CountryID)
ORDER BY CountryName, CityName
FOR XML AUTO
/*
Результат:
*/
Здесь опять же мы можем получить прекрасный XML, за исключением корневого элемента
ROOT. XML не всегда полезен без ОБЯЗАТЕЛЬНОГО корневого элемента. Режим AUTO не
дает возможности определить элемент ROOT для результирующего XML. Попробуем все
же добиться этого. Следующий код демонстрирует попытку разрешить вышеупомянутое
ограничение.
SELECT
CAST ('<CustomersByRegion>' + (SELECT
Country.CountryName AS [name],
Country.Currency,
City.CityName AS [name1],
Customer.CustomerNumber AS [id],
Customer.CustomerName AS [name2],
Customer.Phone
FROM
Customers Customer
INNER JOIN Cities City ON (City.CityID = Customer.CityID)
INNER JOIN Countries Country ON (Country.CountryID
= City.CountryID)
ORDER BY CountryName, CityName
FOR XML AUTO) + '</CustomersByRegion>' AS XML)
/*
*/
Теперь давайте посмотрим, сможем ли мы использовать режим RAW, чтобы получить нужный
нам результат. Режим RAW не обеспечивает способа генерации иерархии узлов XML. Однако
может быть использована комбинация RAW и AUTO для получения нужных результатов.
Следующий код демонстрирует это.
SELECT
CAST( (SELECT Country.CountryName AS [name],
Country.Currency, City.CityName AS [name],
Customer.CustomerNumber AS [id],
Customer.CustomerName AS [name],
Customer.Phone
FROM
Customers Customer
INNER JOIN Cities City ON (City.CityID = Customer.CityID)
INNER JOIN Countries Country ON (Country.CountryID
= City.CountryID)
ORDER BY CountryName, CityName FOR XML AUTO) AS XML)
FOR XML RAW('CustomersByRegion')
/*
Результат:
*/
Заключение
Мы рассмотрели несколько примеров применения режимов AUTO, RAW и PATH, и я надеюсь,
что они достаточно ясно продемонстрировали использование вышеупомянутых режимов.
Есть еще один режим, EXPLICIT, который я еще не показал. Данный способ предоставляет
значительно больше возможностей по структурированию сгенерированного XML. Однако
этот режим намного более сложен в использовании. В следующей статье я представлю
пошаговое руководство для облегчения понимания использования режима EXPLICIT.
20-06-2007
На главную страницу