DB4Breakfast

On the last post, the examples aggregate data with single level (group by customers). Today we'll explore how to aggregate data with multiple levels in XQuery. For the example below, we'll use the same orders.xml document. <?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...

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

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> &ltcustid>{$c/@custid}</custid> {$c/first-nam...