Tuesday, July 15, 2014

XQuery: Aggregation with Join

Today we'll learn about aggregation using XQuery FLWOR expressions.
Assume that we have a customers and an orders XML document. The examples below demonstrate grouping, aggregation and join in XQuery.

-- load xml files to SQL Server as CLOB = Character Large Object 
DECLARE @customers XML = 
(SELECT * FROM OPENROWSET(BULK 'C:\Self_Dev\XML\customers.xml', SINGLE_CLOB) AS customers);

DECLARE @orders XML =
(SELECT * FROM OPENROWSET(BULK 'C:\Self_Dev\XML\orders2.xml', SINGLE_CLOB) AS orders);

-- Concatenate XML documents using XML PATH mode.
DECLARE @customers_orders XML = (SELECT @customers, @orders
                                 FOR XML PATH(''));
--Aggregation without join.
--count # of order for each customer (group by customer).
SELECT @customers_orders.query(
  'for $custid in distinct-values(//orders/order/custid)
   let $o := //orders/order[custid = $custid]
   return 
   <customer_order>
     <custid>{$custid}</custid>
     <ordercount>{count($o)}</ordercount>
   </customer_order>
   ');

<customer_order>
  <custid>1</custid>
  <ordercount>1</ordercount>
</customer_order>
<customer_order>
  <custid>2</custid>
  <ordercount>3</ordercount>
</customer_order>

--aggregation with inner join.
--count # of order for each customers and list the customer name.
SELECT @customers_orders.query(
    'for $custid in distinct-values(//orders/order/custid)
     let $o := //orders/order[custid = $custid]
     let $c := //customers/customer[@custid = $custid]
     return
     <customer_order>
       <custid>{$custid}</custid>
       {$c/first-name}
       {$c/last-name}
       <ordercount>{count($o)}</ordercount>
     </customer_order>
    ');
<customer_order>
  <custid>1</custid>
  <first-name>Bobby</first-name>
  <last-name>Nguyen</last-name>
  <ordercount>1</ordercount>
</customer_order>
<customer_order>
  <custid>2</custid>
  <first-name>SonHa</first-name>
  <last-name>Pham</last-name>
  <ordercount>3</ordercount>
</customer_order>

--Aggregation with outer join.
--count # of order for each customers and list the customer name,
-- and include customer didn't have any order.
SELECT @customers_orders.query(
    'for $c in //customers/customer
     let $o := //orders/order[custid = $c/@custid]
     return
     <customer_order>
       {$c/first-name}
       {$c/last-name}
       <ordercount>{count($o)}</ordercount>
     </customer_order>
    ');
<customer_order>
  <first-name>Bobby</first-name>
  <last-name>Nguyen</last-name>
  <ordercount>1</ordercount>
</customer_order>
<customer_order>
  <first-name>SonHa</first-name>
  <last-name>Pham</last-name>
  <ordercount>3</ordercount>
</customer_order>
<customer_order>
  <first-name>Ngan</first-name>
  <last-name>Le</last-name>
  <ordercount>0</ordercount>
</customer_order>

--aggregation with outer join.
--count # of order, and sum the orders amount
-- for each customers and list the customer name,
--  and include customer didn't have any order.
SELECT @customers_orders.query(
    'for $c in //customers/customer
     let $o := //orders/order[custid = $c/@custid]
     return
     <customer_order>
       {$c/first-name}
       {$c/last-name}
       <ordercount>{count($o)}</ordercount>
       <totalordersamount>{sum($o/subtotal)}</totalordersamount>
     </customer_order>
    ');

<customer_order>
  <first-name>Bobby</first-name>
  <last-name>Nguyen</last-name>
  <ordercount>1</ordercount>
  <totalordersamount>10</totalordersamount>
</customer_order>
<customer_order>
  <first-name>SonHa</first-name>
  <last-name>Pham</last-name>
  <ordercount>3</ordercount>
  <totalordersamount>100</totalordersamount>
</customer_order>
<customer_order>
  <first-name>Ngan</first-name>
  <last-name>Le</last-name>
  <ordercount>0</ordercount>
  <totalordersamount>0.0E0</totalordersamount>
</customer_order>

No comments:

Post a Comment