Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 28 October 2020 08:22:27


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Мастерская 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')
/* Результат: <order> <orderInfo> <orderNumber>00001</orderNumber> <item> <itemNumber>A001</itemNumber> <Quantity>10</Quantity> </item> </orderInfo> <orderInfo> <orderNumber>00001</orderNumber> <item> <itemNumber>A002</itemNumber> <Quantity>20</Quantity> </item> </orderInfo> </order> */

Пример 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 <customersByRegion> 2 <country name="USA" currency="US Dollars"> 3 <city name="NY"> 4 <customer id="MK" name="John Mark" phone="111-111-1111"/> 5 <customer id="WS" name="Will Smith" phone="222-222-2222"/> 6 </city> 7 <city name="NJ"> 8 <customer id="EN" name="Elizabeth Lincoln" phone="333-333-3333"/> 9 </city> 10 </country> 11 <country name="England" currency="Pound Sterling"> 12 <city name="London"> 13 <customer id="TH" name="Thomas Hardy" phone="444-444-4444"/> 14 </city> 15 </country> 16 <country name="India" currency="Rupees"> 17 <city name="New Delhi"> 18 <customer id="JS" name="Jacob Sebastian" phone="555-555-5555"/> 19 </city> 20 </country> 21 </customersByRegion>

Запустите скрипт для создания таблиц и наполнения их нужными нам данными:

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

/* Результат: <row> <country> <name>England</name> <currency>Pound Sterling</currency> <city> <name>London</name> <customer> <id>TH</id> <name>Thomas Hardy</name> <phone>444-444-4444</phone> </customer> </city> </country> </row> <row> <country> <name>India</name> <currency>Rupee</currency> <city> <name>New Delhi</name> <customer> <id>JS</id> <name>Jacob Sebastian</name> <phone>555-555-5555</phone> </customer> </city> </country> </row> <row> ... </row> ... */

Хорошо, но не очень. Вы, должно быть, заметили, что формат не совсем тот, который нам нужен. Нам нужны значения как Атрибуты, а наш запрос возвращает значения как 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')

/* <CustomersByRegion> <country name="England" currency="Pound Sterling"> <city name="London"> <customer id="TH" name="Thomas Hardy" phone="444-444-4444" /> </city> </country> <country name="India" currency="Rupee"> <city name="New Delhi"> <customer id="JS" name="Jacob Sebastian" phone="555-555-5555" /> </city> </country> <country name="USA" currency="US Dollars"> <city name="NJ"> <customer id="EN" name="Elizabeth Lincoln" phone="333-333-3333" /> </city> </country> <country name="USA" currency="US Dollars"> <city name="NY"> <customer id="MK" name="John Mark" phone="111-111-1111" /> </city> </country> <country name="USA" currency="US Dollars"> <city name="NY"> <customer id="WS" name="Will Smith" phone="222-222-2222" /> </city> </country> </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

/* Результат: <Country name="England" Currency="Pound Sterling"> <City name="London"> <Customer id="TH" name="Thomas Hardy" Phone="444-444-4444" /> </City> </Country> <Country name="India" Currency="Rupee"> <City name="New Delhi"> <Customer id="JS" name="Jacob Sebastian" Phone="555-555-5555" /> </City> </Country> <Country name="USA" Currency="US Dollars"> <City name="NJ"> <Customer id="EN" name="Elizabeth Lincoln" Phone="333-333-3333" /> </City> <City name="NY"> <Customer id="MK" name="John Mark" Phone="111-111-1111" /> <Customer id="WS" name="Will Smith" Phone="222-222-2222" /> </City> </Country> */

Здесь опять же мы можем получить прекрасный 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)

/* <CustomersByRegion> <Country name="England" Currency="Pound Sterling"> <City name1="London"> <Customer id="TH" name2="Thomas Hardy" Phone="444-444-4444" /> </City> </Country> <Country name="India" Currency="Rupee"> <City name1="New Delhi"> <Customer id="JS" name2="Jacob Sebastian" Phone="555-555-5555" /> </City> </Country> <Country name="USA" Currency="US Dollars"> <City name1="NJ"> <Customer id="EN" name2="Elizabeth Lincoln" Phone="333-333-3333" /> </City> <City name1="NY"> <Customer id="MK" name2="John Mark" Phone="111-111-1111" /> <Customer id="WS" name2="Will Smith" Phone="222-222-2222" /> </City> </Country> </CustomersByRegion> */

Теперь давайте посмотрим, сможем ли мы использовать режим 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')

/* Результат: <CustomersByRegion> <Country name="England" Currency="Pound Sterling"> <City name="London"> <Customer id="TH" name="Thomas Hardy" Phone="444-444-4444" /> </City> </Country> <Country name="India" Currency="Rupee"> <City name="New Delhi"> <Customer id="JS" name="Jacob Sebastian" Phone="555-555-5555" /> </City> </Country> <Country name="USA" Currency="US Dollars"> <City name="NJ"> <Customer id="EN" name="Elizabeth Lincoln" Phone="333-333-3333" /> </City> <City name="NY"> <Customer id="MK" name="John Mark" Phone="111-111-1111" /> <Customer id="WS" name="Will Smith" Phone="222-222-2222" /> </City> </Country> </CustomersByRegion> */

Заключение

Мы рассмотрели несколько примеров применения режимов AUTO, RAW и PATH, и я надеюсь, что они достаточно ясно продемонстрировали использование вышеупомянутых режимов. Есть еще один режим, EXPLICIT, который я еще не показал. Данный способ предоставляет значительно больше возможностей по структурированию сгенерированного XML. Однако этот режим намного более сложен в использовании. В следующей статье я представлю пошаговое руководство для облегчения понимания использования режима EXPLICIT.

20-06-2007

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.