Работа с XML в SQL Server

Как в SQL Server работать с XML: парсинг и генерация

SQL Server поддерживает тип данных XML и мощные методы для преобразования XML в реляционные данные и обратно.

1. Парсинг XML – из XML в таблицу

Имея XML-документ (в переменной, параметре или столбце), можно извлечь данные в виде строк и столбцов.

Способ 1: методы nodes() и value() (современный, предпочтительный)

DECLARE @xml XML = '
<root>
    <person id="1">
        <name>Иван</name>
        <age>30</age>
    </person>
    <person id="2">
        <name>Мария</name>
        <age>25</age>
    </person>
</root>';

SELECT 
    p.value('@id', 'int') AS Id,
    p.value('(name)[1]', 'varchar(50)') AS Name,
    p.value('(age)[1]', 'int') AS Age
FROM @xml.nodes('/root/person') AS t(p);

Результат:

Как это работает:
- .nodes('/root/person') – создаёт виртуальную таблицу из каждого узла, подпадающего под XPath.
- p.value('@id', 'int') – берёт атрибут id текущего узла и преобразует в int.
- p.value('(name)[1]', 'varchar(50)') – берёт первый дочерний элемент <name>.

Если XML содержит пространства имён:

WITH XMLNAMESPACES (DEFAULT 'http://example.com')
SELECT ...
FROM @xml.nodes('/root/person') AS t(p);

Способ 2: OPENXML (устаревший) – не рекомендуется для новой разработки.

2. Генерация XML из SELECT-запроса

Превратить обычный реляционный результат в XML можно с помощью предложения FOR XML.

Базовые режимы

• FOR XML RAW – каждая строка – элемент <row> с атрибутами.
• FOR XML AUTO – вложенность автоматически определяется по псевдонимам таблиц.
• FOR XML PATH – самый гибкий, полный контроль над структурой.
• FOR XML EXPLICIT – сложный, устаревший – не используйте.

Пример с FOR XML PATH (рекомендуется)

SELECT 
    1 AS "@id",
    'Иван' AS "name",
    30 AS "age"
FOR XML PATH('person'), ROOT('root');

Результат:

<root>
  <person id="1">
    <name>Иван</name>
    <age>30</age>
  </person>
</root>

Если нужно много записей из таблицы

CREATE TABLE #Persons (Id int, Name varchar(50), Age int);
INSERT #Persons VALUES (1, 'Иван', 30), (2, 'Мария', 25);

SELECT 
    Id AS "@id",
    Name AS "name",
    Age AS "age"
FROM #Persons
FOR XML PATH('person'), ROOT('root');

Полезные опции

• , TYPE – возвращает результат как тип XML (а не NVARCHAR).
• , ELEMENTS – выводит данные элементами, а не атрибутами.
• , ROOT('Имя') – оборачивает весь XML в корневой элемент.

Создание вложенного XML (заказы и позиции)

SELECT 
    o.OrderId AS "@id",
    o.OrderDate AS "date",
    (SELECT ProductId AS "@id", Quantity
     FROM OrderItems i
     WHERE i.OrderId = o.OrderId
     FOR XML PATH('item'), TYPE) AS "items"
FROM Orders o
FOR XML PATH('order'), ROOT('orders');

Краткое резюме