Outer Join XML Data Using XQuery

Today we'll learn about outer join in XQuery.

--
-- assume that we have 2 XML documents, customers 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\orders.xml', SINGLE_CLOB) AS orders);

-- Concatenate XML documents using XML PATH mode.
DECLARE @customers_orders XML = (SELECT @customers, @orders
                                 FOR XML PATH(''));
SELECT @customers_orders;

Here's the XML data that we'll use for the examples below.

--outer join
-- all customers regardless if they have an order or not
SELECT @customers_orders.query(
       'for $c in //customers/customer
       let $o := //orders/order[custid = $c/@custid]
       return
       <order>
         <custid>{$c/@custid}</custid>
         {$c/first-name}
         {$c/last-name}
         <orderid>{$o/@orderid}</orderid>
         {$o/orderdate}
       </order>');

The result is what we expected. Customer with custid = 3 also is included in the result even she didn't make any order.

-- Notice the result for the query above.
-- The orderdate for custid = 3 is not shown, since she doesn't have any order.
-- What if we want to display a tag <orderdate> with no value?
-- This query provide a tag <orderdate> with no value for custid = 3.
-- However, it also nested the <orderdate> for other customers' order.
SELECT @customers_orders.query(
       'for $c in //customers/customer
       let $o := //orders/order[custid = $c/@custid]
       return
       <order>
         <custid>{$c/@custid}</custid>
         {$c/first-name}
         {$c/last-name}
         <orderid>{$o/@orderid}</orderid>
         <orderdate>{$o/orderdate}</orderdate>
       </order>');

--
-- This query fix the nesting issue by display a tag <orderdate>
--   with no value for customer with custid = 3.
-- Use text() to only return value not tag.
-- Note: data() is not supported in sql server 2012
SELECT @customers_orders.query(
       'for $c in //customers/customer
       let $o := //orders/order[custid = $c/@custid]
       return
       <order>
         <custid>{$c/@custid}</custid>
         {$c/first-name}
         {$c/last-name}
         <orderid>{$o/@orderid}</orderid>
         <orderdate>{$o/orderdate/text()}</orderdate>
       </order>');

--
-- Another option for outer join is include another FLWOR expression inside of the return clause.
SELECT @customers_orders.query(
       'for $c in //customers/customer
       return
       <order>
         <custid>{$c/@custid}</custid>
         {$c/first-name}
         {$c/last-name}
         {
            for $o in //orders/order[custid = $c/@custid]
            return (
               <orderid>{$o/@orderid}</orderid>
                <orderdate>{$o/orderdate/text()}</orderdate>
            )
          }
        </order>');

--
--same as query above but using where clause for join conditions
SELECT @customers_orders.query('for $c in //customers/customer
SELECT @customers_orders.query(
       'for $c in //customers/customer
       return
       <order>
         <custid>{$c/@custid}</custid>
         {$c/first-name}
         {$c/last-name}
         {
            for $o in //orders/order
            where $c/@custid = $o/custid
            return (
               <orderid>{$o/@orderid}</orderid>
                <orderdate>{$o/orderdate/text()}</orderdate>
            )
          }
       </order>');

Note the result for 2 queries above doesn't include <orderid> and <orderdate> tag for customer with custid = 3.