XQuery MSSQL server

Tsql теория > XQuery MSSQL server
01.02.2017 10:36:03


Наиболее часто встречающиеся слова в статье:

[DECLARE ] [Customer] [''declare] [namespace] [/Customer] [              ] [variable] [Василий] [Orders/s] [orderTable]


Статья:

Альтернативным вариантом OPENXML является применение 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)