FOR XML: примеры (SQL)

Краткие примеры использования

Пример 1: Режим RAW

Базовое использование:

SELECT CustomerID, CompanyName 
FROM Customers 
WHERE CustomerID IN ('ALFKI', 'ANATR') 
FOR XML RAW;

С заданием имени элемента и корня:

SELECT CustomerID, CompanyName 
FROM Customers 
WHERE CustomerID = 'ALFKI' 
FOR XML RAW('Customer'), ROOT('Customers');

Пример 2: Режим AUTO

SELECT c.CustomerID, o.OrderID 
FROM Customers c 
INNER JOIN Orders o ON c.CustomerID = o.CustomerID 
WHERE c.CustomerID = 'ALFKI' 
FOR XML AUTO;

С директивой ELEMENTS:

SELECT CustomerID, CompanyName 
FROM Customers 
WHERE CustomerID = 'ALFKI' 
FOR XML AUTO, ELEMENTS;

  ALFKI
  Alfreds Futterkiste

Пример 3: Режим PATH

Гибкое формирование структуры:

SELECT CustomerID as '@ID', 
       CompanyName as 'info/Name', 
       City as 'info/City' 
FROM Customers 
WHERE CustomerID = 'ALFKI' 
FOR XML PATH('Customer'), ROOT('Data');

  
    
      Alfreds Futterkiste
      Berlin
    
  

Похожие функции в MS SQL

В MS SQL Server существуют другие функции для работы с XML и JSON.

FOR JSON

Функция FOR JSON преобразует результаты запроса в формат JSON. Доступна с SQL Server 2016. Она аналогична FOR XML по синтаксису, поддерживает режимы AUTO и PATH. Использование предпочтительно при разработке современных веб-API или интеграции с системами, ожидающими JSON.

SELECT CustomerID, CompanyName 
FROM Customers 
WHERE CustomerID = 'ALFKI' 
FOR JSON PATH;

OPENXML

Функция OPENXML предоставляет возможность разбора XML-документа и представления его в виде реляционного набора строк. Это противоположная операция по отношению к FOR XML. Используется, когда требуется загрузить XML-данные в таблицы. Работает в паре с хранимой процедурой sp_xml_preparedocument.

Выбор между функциями зависит от задачи: FOR XML для экспорта данных в XML, OPENXML для импорта XML в таблицы, FOR JSON для работы с JSON-форматом.

Альтернативы в других СУБД

Oracle

В Oracle для генерации XML используется функция XMLELEMENT, XMLAGG и другие. Подход более многословный, но гибкий.

SELECT XMLELEMENT("Customer", 
         XMLATTRIBUTES(CustomerID AS "ID"),
         XMLELEMENT("Name", CompanyName))
FROM Customers 
WHERE CustomerID = 'ALFKI';

  Alfreds Futterkiste

PostgreSQL

PostgreSQL предлагает функции table_to_xml, query_to_xml и расширение xml2. Также можно формировать XML с помощью стандартных SQL/XML функций, таких как XMLELEMENT, аналогичных Oracle.

SELECT xmlconcat(
  xmlelement(name Customer, 
    xmlattributes(CustomerID as "ID"),
    xmlelement(name CompanyName, CompanyName)))
FROM Customers 
WHERE CustomerID = 'ALFKI';

MySQL

MySQL не имеет прямой аналогии FOR XML. Обычно XML формируется на стороне приложения или с помощью конкатенации строк. Доступна функция ExtractValue для парсинга XML.

SQLite

SQLite не имеет встроенных функций для генерации XML. Формирование XML выполняется на клиенте или путем ручной сборки строки в запросе.

Ключевое отличие MS SQL — наличие FOR XML как простого и интегрированного в SELECT способа, тогда как в других СУБД часто требуется использовать набор отдельных функций или формировать XML вне СУБД.

Типичные ошибки

Ошибка 1: Отсутствие однозначного порядка строк

При использовании режима AUTO структура XML зависит от порядка столбцов в SELECT. Изменение порядка может привести к неожиданной вложенности.

-- Разный порядок столбцов дает разную структуру
SELECT o.OrderID, c.CustomerID 
FROM Orders o 
INNER JOIN Customers c ON o.CustomerID = c.CustomerID 
WHERE c.CustomerID = 'ALFKI' 
FOR XML AUTO;

SELECT c.CustomerID, o.OrderID 
FROM Customers c 
INNER JOIN Orders o ON c.CustomerID = o.CustomerID 
WHERE c.CustomerID = 'ALFKI' 
FOR XML AUTO;

Ошибка 2: Попытка обработки более 256 вложенных вызовов FOR XML

При сложной вложенности может возникнуть ошибка переполнения стека. Требуется упрощение запроса.

Ошибка 3: Смешивание атрибутов и элементов без указания PATH

В режимах RAW и AUTO сложно смешивать атрибуты и элементы. Режим PATH решает эту проблему.

-- Некорректно для RAW/AUTO, но работает в PATH
SELECT CustomerID as '@ID', CompanyName 
FROM Customers 
FOR XML PATH('');

Ошибка 4: Игнорирование специальных XML-символов

Данные, содержащие символы <, >, &, могут привести к формированию некорректного XML. FOR XML автоматически экранирует их.

Изменения в последних версиях

Основные изменения функции FOR XML связаны с интеграцией с типом данных xml и производительностью.

• В SQL Server 2005 была добавлена директива TYPE, позволяющая возвращать результат как тип xml.
• В SQL Server 2008 улучшена производительность и поддержка пространств имен.
• Начиная с SQL Server 2012 функция работает стабильно, значительных синтаксических изменений не вносилось.
• В современных версиях (2016+) акцент смещен на развитие функции FOR JSON, но FOR XML остается полностью поддерживаемой.
• В SQL Server 2017 и выше улучшена обработка больших XML-документов.

Новых параметров в последних основных версиях не добавлялось. Рекомендуется следить за обновлениями, касающимися производительности и безопасности XML.

Расширенные примеры

Пример 1: Генерация XML с пространствами имен

WITH XMLNAMESPACES('http://example.com' as ns)
SELECT CustomerID as '@ns:ID', 
       CompanyName as 'ns:Name'
FROM Customers 
WHERE CustomerID = 'ALFKI' 
FOR XML PATH('ns:Customer'), ROOT('ns:Data');

  
    Alfreds Futterkiste
  

Пример 2: Использование TYPE и XQuery

Получение результата как типа xml для дальнейшего разбора:

DECLARE @x xml;
SET @x = (SELECT CustomerID, CompanyName 
          FROM Customers 
          WHERE CustomerID = 'ALFKI' 
          FOR XML RAW, TYPE);
-- Теперь можно использовать XQuery
SELECT @x.query('/row[@CustomerID="ALFKI"]');

Пример 3: Агрегация данных с FOR XML и STUFF

Формирование списка значений в одном элементе:

SELECT CustomerID,
       STUFF((SELECT ', ' + CAST(OrderID as varchar(10)) 
              FROM Orders o 
              WHERE o.CustomerID = c.CustomerID 
              FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, '') as OrderList
FROM Customers c 
WHERE CustomerID IN ('ALFKI', 'ANATR');

CustomerID | OrderList
ALFKI      | 10643, 10692, 10702, ...
ANATR      | 10308, 10625

Пример 4: Вложенные подзапросы с FOR XML

SELECT c.CustomerID,
       (SELECT OrderID, OrderDate 
        FROM Orders o 
        WHERE o.CustomerID = c.CustomerID 
        FOR XML RAW('Order'), TYPE, ELEMENTS) as OrdersXML
FROM Customers c 
WHERE CustomerID = 'ALFKI' 
FOR XML PATH('Customer'), ROOT('Customers');

  
    ALFKI
    
      
        10643
        1997-08-25T00:00:00
      
      ...
    
  

Пример 5: Режим EXPLICIT для полного контроля

Создание сложной иерархии:

SELECT 1 as Tag, NULL as Parent,
       CustomerID as [Customer!1!ID],
       CompanyName as [Customer!1!Name!ELEMENT]
FROM Customers 
WHERE CustomerID = 'ALFKI' 
FOR XML EXPLICIT;

  Alfreds Futterkiste