SQL Server: FOR XML PATH — вложение/группировка

У меня есть данные, которые выглядят так:

OrderID CustomerID  ItemID  ItemName
10000   1234        111111  Product A
10000   1234        222222  Product B
10000   1234        333333  Product C
20000   5678        111111  Product A
20000   5678        222222  Product B
20000   5678        333333  Product C

Я хочу написать запрос T-SQL в SQL Server, чтобы вернуть такие данные:

<Root>
  <Order>
    <OrderID>10000</OrderID>
    <CustomerID>1234</CustomerID>
    <LineItem>
      <ItemID>11111</ItemId>
      <ItemName>Product A</ItemName>
    </LineItem>
    <LineItem>
      <ItemID>22222</ItemId>
      <ItemName>Product B</ItemName>
    </LineItem>
    <LineItem>
      <ItemID>33333</ItemId>
      <ItemName>Product B</ItemName>
    </LineItem>
  </Order>
  <Order>
    <OrderID>20000</OrderID>
    <CustomerID>5678</CustomerID>
    <LineItem>
      <ItemID>11111</ItemId>
      <ItemName>Product A</ItemName>
    </LineItem>
    <LineItem>
      <ItemID>22222</ItemId>
      <ItemName>Product B</ItemName>
    </LineItem>
    <LineItem>
      <ItemID>33333</ItemId>
      <ItemName>Product B</ItemName>
    </LineItem>
  </Order>
</Root>

Я попытался вернуть запрос в XML, используя:

FOR XML PATH ('Order'), root ('Root')

Но это дает мне Order узла для каждой строки (всего 6) по сравнению с просто порядковым узлом для каждого orderId (всего 2).

Любые идеи?


sql-server for-xml-path
person jared    schedule 25.07.2012    source источник

Ответы (2)


arrow_upward
4
arrow_downward

Для завершения: вот решение без подвыборки, которое должно работать быстрее для больших таблиц. Вместо этого таблица группируется столько раз, сколько уровней в XML, и идентифицирует уровень с помощью GROUPING_ID (см. https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx и https://docs.microsoft.com/en-us/sql/relational-databases/xml/use-explicit-mode-with-for-xml):

with rsOrders as (
  select '10000' OrderID, '1234' CustomerID, '111111' ItemID, 'Product A' ItemName union
  select '10000' orderId, '1234' customerID, '222222' itemID, 'Product B' ItemName union
  select '10000' orderId, '1234' customerID, '333333' itemID, 'Product C' ItemName union
  select '20000' orderId, '5678' customerID, '111111' itemID, 'Product A' ItemName union
  select '20000' orderId, '5678' customerID, '222222' itemID, 'Product B' ItemName union
  select '20000' orderId, '5678' customerID, '333333' itemID, 'Product C' ItemName 
)
select case 
         when GROUPING_ID(ItemID) = 0 then 3 
         when GROUPING_ID(OrderID) = 0 then 2 
         else 1 
         end as tag,
       case 
           when GROUPING_ID(ItemID) = 0 then 2 
         when GROUPING_ID(OrderID) = 0 then 1 
         else null
       end as parent,
       null       as 'Root!1',
       OrderID    as 'Order!2!OrderID!element', 
       CustomerID as 'Order!2!CustomerID!element', 
       ItemID     as 'LineItem!3!ItemID!element', 
       ItemName   as 'LineItem!3!ItemName!element'
  from rsOrders
 group by grouping sets ((), (OrderID, CustomerID), (OrderID, CustomerID, ItemID, ItemName))
 order by OrderID, CustomerID, ItemID, ItemName
   for xml explicit, type
person casenonsensitive    schedule 09.08.2017

arrow_upward

arrow_downward

person    schedule
comment
Спасибо, Берт. Что делает «тип» в подзапросе «для пути xml»? - person jared; 26.07.2012
comment
@jared Это означает «вернуть это как тип данных XML». Таким образом, в приведенном выше запросе он просто возвращает подзапрос в виде небольшого фрагмента xml. - person Bert; 26.07.2012