Создание XML данных с помощью конструкции FOR XML в языке T-SQL

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

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

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

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

Конструкция FOR XML в T-SQL

FOR XML – конструкция языка T-SQL, позволяющая получить результаты SQL запроса в формате XML. Данная конструкция определяется после определения всего SQL запроса, в случае необходимости в определение списка выборки вносятся изменения с целью формирования определённой структуры XML документа. Конструкцию FOR XML можно использовать также и во вложенных запросах. У данной конструкции есть несколько режимов работы.

Режимы работы конструкции FOR XML

• RAW – режим, при котором в XML документе создается одиночный элемент <row> для каждой строки результирующего набора данных инструкции SELECT;
• AUTO – в данном режиме структура XML документа создается автоматически, в зависимости от инструкции SELECT (объединений, вложенных запросов и так далее);
• EXPLICIT – самый расширенный режим работы конструкции FOR XML, при котором Вы сами формируете структуру итогового XML документа, за счет чего этот режим самый трудоемкий. Данный режим в основном используется для создания XML документов с очень сложной структурой, которую не получается реализовать с помощью других режимов;
• PATH – это своего рода упрощенный режим EXPLICIT, который хорошо справляется со множеством задач по формированию XML документов, включая формирование атрибутов для элементов. Если Вам нужно самим сформировать структуру XML данных, то рекомендовано использовать именно этот режим.

У конструкции FOR XML есть еще и параметры, с помощью которых мы также можем влиять на структуру итогового XML документа. Давайте рассмотрим несколько полезных параметров конструкции FOR XML.

Параметры конструкции FOR XML

• TYPE – возвращает сформированные XML данные с типом XML, если параметр TYPE не указан, данные возвращаются с типом NVARCHAR(MAX). Параметр необходим в тех случаях, когда над итоговыми XML данными будут проводиться операции, характерные для XML данных, например, выполнение инструкций на языке XQuery;
• ELEMENTS – если указать данный параметр, столбцы возвращаются в виде вложенных элементов;
• ROOT – параметр добавляет к результирующему XML-документу один элемент верхнего уровня (корневой элемент), по умолчанию «root», однако название можно указать произвольное.

Примеры создания XML данных с помощью конструкции FOR XML на T-SQL

Сначала давайте создадим тестовые данные, которые мы будет использовать в примерах. Все примеры будут выполнены в Microsoft SQL Server 2016 Express.

   
   --Инструкция создания таблицы 1
   CREATE TABLE TestTable(
        [ProductId] INT IDENTITY(1,1) NOT NULL,
        [CategoryId] INT NOT NULL,
        [ProductName] VARCHAR(100) NOT NULL,
        [Price] MONEY NULL
   )
   GO
   --Инструкция создания таблицы 2
   CREATE TABLE TestTable2(
        [CategoryId] INT IDENTITY(1,1) NOT NULL,
        [CategoryName] VARCHAR(100) NOT NULL
   ) 
   GO
   --Инструкция добавления данных
   INSERT INTO TestTable
        VALUES (1,'Клавиатура', 100),
                   (1, 'Мышь', 50),
                   (2, 'Телефон', 300)  
   GO
   INSERT INTO TestTable2
        VALUES ('Комплектующие компьютера'),
                   ('Мобильные устройства')
   GO
   --Запросы на выборку
   SELECT * FROM TestTable
   SELECT * FROM TestTable2

Данные созданы, теперь переходим к самим примерам.

1. Пример использования режима RAW в конструкции FOR XML

В этом примере мы используем режим RAW (режим указывается после ключевых слов FOR XML), а также параметр TYPE, для этого мы просто после основного запроса SELECT напишем данную конструкцию с указанием нужного режима и параметра.

   
   SELECT ProductId, ProductName, Price
   FROM TestTable
   ORDER BY ProductId
   FOR XML RAW, TYPE

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

Запрос нам вернул XML данные, где каждая строка таблицы TestTable является элементом row, а все столбцы отображены в виде атрибутов этого элемента.

В следующем запросе мы укажем дополнительные параметры конструкции FOR XML.

   
   SELECT ProductId, ProductName, Price
   FROM TestTable
   ORDER BY ProductId
   FOR XML RAW ('Product'), TYPE, ELEMENTS, ROOT ('Products')

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

2. Пример использования режима AUTO в конструкции FOR XML

Сейчас давайте попробуем режим AUTO, при этом мы еще модифицируем запрос, например, добавим в него объединение для наглядности.

   
   SELECT TestTable.ProductId, 
           TestTable.ProductName, 
           TestTable2.CategoryName, 
           TestTable.Price
   FROM TestTable
   LEFT JOIN TestTable2 ON TestTable.CategoryId = TestTable2.CategoryId
   ORDER BY TestTable.ProductId
   FOR XML AUTO, TYPE, ROOT ('Products')

В данном режиме нам вернулись XML данные, где записи таблицы TestTable представлены в виде элементов, ее столбцы — в виде атрибутов, а соответствующие записи (на основе объединения) таблицы TestTable2 — в виде вложенных элементов с атрибутами. Параметром ROOT мы также задали корневой элемент.

3. Пример использования режима EXPLICIT в конструкции FOR XML

В режиме EXPLICIT мы уже можем сами указывать, что именно будет элементом, а что атрибутом.

   
   SELECT 1 AS Tag,
       NULL AS Parent, 
       ProductId AS [Product!1!Id], 
       ProductName AS [Product!1!ProductName!ELEMENT], 
       Price AS [Product!1!Price!ELEMENT]
   FROM TestTable
   ORDER BY [Product!1!Id]
   FOR XML EXPLICIT, TYPE, ROOT ('Products')

В данном случае мы указали, что у нас будет всего один уровень в иерархии (1 AS Tag), если нужно несколько, то пишутся дополнительные запросы и объединяются с помощью UNUON ALL, в Parent при этом указывается номер родительского элемента, для верхнего уровня в Parent, как в нашем случае, ничего не указывается.

С помощью специального синтаксиса мы определили, что значение ProductId будет атрибутом Id у элемента Product, а ProductName и Price будут вложенными элементами элемента Product, для этого мы в имена этих столбцов добавили директиву ELEMENT. Параметры TYPE и ROOT мы здесь также использовали.

4. Пример использования режима PATH в конструкции FOR XML

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

   
   SELECT ProductId AS "@Id", 
           ProductName, 
           Price
   FROM TestTable
   ORDER BY ProductId
   FOR XML PATH ('Product'), TYPE, ROOT ('Products')

В этом запросе мы с помощью параметра задали название элементов для каждой строки данных, т.е. Product, указали корневой элемент с помощью параметра ROOT. С помощью символа @, указали, что значение столбца ProductId должно быть атрибутом Id у элемента строки (если имя столбца начинается с символа @ и не содержит косую черту /, то SQL сервер создает атрибут элемента). В итоге структура XML данных у нас получилась точно такая же, как и при использовании режима EXPLICIT в примере 3.

Заметка! Для изучения языка T-SQL на профессиональном уровне рекомендую посмотреть мои видеокурсы по T-SQL.

У меня все, пока!