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.

<?xml version="1.0"?>
<customers>
    <customer custid="1">
      <first-name>Bobby</first-name>
      <last-name>Nguyen</last-name>
      <phone>206-111-2222</phone>
    </customer>
    <customer custid="2">
      <first-name>SonHa</first-name>
      <last-name>Pham</last-name>
      <phone>503-222-3333</phone>
    </customer>
    <customer custid="3">
      <first-name>Ngan</first-name>
      <last-name>Le</last-name>
      <phone>928-464-732</phone>
    </customer>
</customers>

<?xml version="1.0"?>
<orders>
  <order orderid="1">
    <custid>1</custid>
    <orderdate>2014-05-15</orderdate>
    <shipaddress>
       <address>343 2nd Ave S.</address>
       <city>Seattle</city>
       <region>WA</region>
       <postalcode>98142</postalcode>
    </shipaddress>
    <items>
       <item itemid="a1">
          <quantity>1</quantity>
          <unitprice>10.00</unitprice>
       </item>
    </items>
    <subtotal>10.00</subtotal>
  </order>
  <order orderid="2">
    <custid>2</custid>
    <orderdate>2014-06-01</orderdate>
    <shipaddress>
       <address>1000 Johnson St.</address>
       <city>Portland</city>
       <region>OR</region>
       <postalcode>97209</postalcode>
    </shipaddress>
    <items>
       <item itemid="a1">
          <quantity>1</quantity>
          <unitprice>10.00</unitprice>
       </item>
       <item itemid="b1">
          <quantity>2</quantity>
          <unitprice>20.00</unitprice>
       </item>
    </items>
    <subtotal>50.00</subtotal>
  </order>
  <order orderid="3">
    <custid>2</custid>
    <orderdate>2014-07-01</orderdate>
    <shipaddress>
       <address>1000 Johnson St.</address>
       <city>Portland</city>
       <region>OR</region>
       <postalcode>97209</postalcode>
    </shipaddress>
    <items>
       <item itemid="a1">
          <quantity>2</quantity>
          <unitprice>10.00</unitprice>
       </item>
       <item itemid="b1">
          <quantity>1</quantity>
          <unitprice>20.00</unitprice>
       </item>
    </items>
    <subtotal>40.00</subtotal>
  </order>
  <order orderid="4">
    <custid>2</custid>
    <orderdate>2014-07-10</orderdate>
    <shipaddress>
       <address>1000 Johnson St.</address>
       <city>Portland</city>
       <region>OR</region>
       <postalcode>97209</postalcode>
    </shipaddress>
    <items>
       <item itemid="a1">
          <quantity>1</quantity>
          <unitprice>10.00</unitprice>
       </item>
    </items>
    <subtotal>10.00</subtotal>
  </order>
</orders>

-- 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>