Преобразуем XML в таблицу

В Transact-SQL распространённой практикой представления данных XML в виде таблицы является использование OPENXML. Рассмотрим альтернативу с применением XQuery.

При использование OPENXML также приходится задействовать процедуры sp_xml_preparedocument и sp_xml_removedocument.

DECLARE @orders xml ='
<Orders xmlns="uri:shop">
 <Order orderID="1">
  <Customer>Василий</Customer>
 </Order>
 <Order orderID="2">
  <Customer>Петров</Customer>
 </Order>
 <Order orderID="3">
  <Customer>Иванов</Customer>
 </Order>
</Orders>
';

DECLARE @h int;
EXECUTE sp_xml_preparedocument @h OUTPUT, @orders, '';

SELECT * FROM OPENXML(@h, '/s:Orders/s:Order', 2)
  WITH
  (
    OrderID int './@orderID',
    CustomerName nvarchar(128) './s:Customer'
  );

EXECUTE sp_xml_removedocument @h;

* This source code was highlighted with Source Code Highlighter.

Результат:

OrderID     CustomerName
----------- ------------
1           Василий
2           Петров
3           Иванов

(3 row(s) affected)

У данной практики есть несколько недостатков. Во первых процедура sp_xml_preparedocument не работает с XML данными, которым назначена схема. Во вторых об ограничениях процедуры можно прочитать во множественных примечаниях в спецификации.
Альтернативным вариантом является применение XQuery к данным типа xml. XQuery начался поддерживаются с MS SQL Server 2005.
Если возникла потребность преобразовать xml в таблицу, то это значит, что xml содержит данные с одинаковой структурой, образующие массив. Так в нашем примере присутствуют элементы Order, представляющие одну и ту же структуру заказа в массиве заказов. Используя функции XQuery можно осуществить перебор данных так, как это делается во множество языках программирования:

DECLARE @orders xml ='
<Orders xmlns="uri:shop">
 <Order orderID="1">
  <Customer>Василий</Customer>
 </Order>
 <Order orderID="2">
  <Customer>Петров</Customer>
 </Order>
 <Order orderID="3">
  <Customer>Иванов</Customer>
 </Order>
</Orders>
';

DECLARE @orderTable table(OrderID int, Name nvarchar(12));
DECLARE @count int = @orders.value('declare namespace s="uri:shop";
                  count(/s:Orders/s:Order)'
                 , 'int');
DECLARE @i int = 1;

WHILE @i <= @count BEGIN
  INSERT @orderTable(OrderID, Name)
    SELECT @orders.value('declare namespace s="uri:shop";
               (/s:Orders/s:Order[sql:variable("@i")]/@orderID)[1]'
              , 'int')
       , @orders.value('declare namespace s="uri:shop";
               (/s:Orders/s:Order[sql:variable("@i")]/s:Customer)[1]'
              , 'nvarchar(12)')

  SET @i += 1;
END

SELECT * FROM @orderTable

* This source code was highlighted with Source Code Highlighter.

Результат:

OrderID     CustomerName
----------- ------------
1           Василий
2           Петров
3           Иванов

(3 row(s) affected)

В данном примере следует обратить внимание на применение функций XQuery count и sql:variable. Функция count возвращает количество элементов результирующего набора, а функция sql:variable вставляет переменную T-SQL выражения в выражение XQuery.
Также следует обратить внимание на то, что переменная @i инициализируется 1, так как в XML индексирование элементов узла начинается с 1.
Чтобы не вызывать функцию count (не делать двойного перебора) можно перебирать данные пока результирующая выборка не будет пустой:

DECLARE @orders xml ='
<Orders xmlns="uri:shop">
 <Order orderID="1">
  <Customer>Василий</Customer>
 </Order>
 <Order orderID="2">
  <Customer>Петров</Customer>
 </Order>
 <Order orderID="3">
  <Customer>Иванов</Customer>
 </Order>
</Orders>
';

DECLARE @orderTable table(OrderID int, Name nvarchar(12));
DECLARE @order xml;
DECLARE @i int = 1;

WHILE 1=1 BEGIN
  SET @order = @orders.query('declare namespace s="uri:shop";
               /s:Orders/s:Order[sql:variable("@i")]');

  IF @order.exist('declare namespace s="uri:shop"; /s:Order') = 0
    BREAK;

  INSERT @orderTable(OrderID, Name)
    SELECT @order.value('declare namespace s="uri:shop";
               (/s:Order/@orderID)[1]'
              , 'int')
       , @order.value('declare namespace s="uri:shop";
               (/s:Order/s:Customer)[1]'
              , 'nvarchar(12)');

  SET @i += 1;
END

SELECT * FROM @orderTable

* This source code was highlighted with Source Code Highlighter.

Результат:

OrderID     CustomerName
----------- ------------
1           Василий
2           Петров
3           Иванов

(3 row(s) affected)