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

Today we'll learn how to join XML data using XQuery. -- -- assume that we have 2 XML documents, customers and 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. --cross join --When multiple for clauses are specified, the result is similar as a nested loops. --In this case, the return clause is evaluated once for each of the combination of the $c and $o variable's value. --Since there're 3 customers and 2 orders, the return clause is evaluate 3 * 2...

To prepare for next blog post (XML Inner Join using XQuery), today, we'll learn how to import and concatenate XML documents in SQL Server. USE tempdb; GO -- Import XML documents as CLOB = Character Large Object. -- Note: AS of SQL 2012, XML data type can hold 2GB of data max. 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); SELECT @customers AS customers, @orders AS orders; -- Concatenate XML document. -- This query will fails. Operand data type xml is invalid for add operator. DECLARE @customers_orders XML = @customers + @orders; -- Concatenate XML document using XML PATH mode. -- Add the default row tag as root node DECLARE @customers_orders2 XML = (SELECT @customers, @orders FOR XML PATH); SELECT @customers_orders; -- Concatenate XML documen...

In this post we will learn how to filter and sort data in XQuery when querying XML. -- let's assume that we have a table contain owners -- and all the detail about the stores that they own. USE TEMPDB; GO IF OBJECT_ID(N'#business', 'U') IS NOT NULL BEGIN DROP TABLE #business; END GO CREATE TABLE #business ( OwnerId INT NOT NULL, OwnerName VARCHAR(50) NOT NULL, stores XML --note: this is an XML data type ); GO --generate some XML data DECLARE @bookstore1 XML = N' <stores> <bookstore specialty="novel" address_city="Seattle"> <book style="autobiography"> <title>Create a Vision</title> <author> <first-name>Joe</first-name> <last-name>Smith</last-name> </author> <price>12</price> </book> <book style="autobiography"> <title>Face the Sun</title> ...

In previous several posts, I provided some examples querying XML data mainly using XPath Expressions. Today we'll learn about the FLWOR expressions. The FLWOR expression is the core of the XQuery language. With FLWOR expressions, we can create more complex queries. Also we can sort the result set. FLWOR(for, let, where, order by, return). Below is a brief description for these 5 clauses. for : Contains variables to hold each value in the sequence. This set up iterations. Similar like, but not exactly the same, foreach loop in C#. The key different is the iteration in the FLWOR expression is not processed in ordered. let : contains variables to hold a sequence. The let clause perform the assignment of a sequence to a variable. Only evaluate once (no iteration). where : filter result. Similar to WHERE clause in SQL. (optional) order by : sort the sequence. Similar to ORDER BY in SQL. Order by clause requires a singleton or an empty sequence. (optional) return : returns result...

SQL Server 2012 implement XQuery using several methods for XML data type. To simplify the examples below, I insert some XML data to a XML column within a table. Then use the SQL Server query() method to write some simple XQuery queries. From last post we learned that XQuery queries return sequences, so I will use the word "sequence" for the result of the examples below. -- let's assume that we have a table contain owners -- and all the detail about the stores that they own. USE TEMPDB; GO IF OBJECT_ID(N'#business', 'U') IS NOT NULL BEGIN DROP TABLE #business; END GO CREATE TABLE #business ( OwnerId INT NOT NULL, OwnerName VARCHAR(50) NOT NULL, stores XML --note: this is an XML data type ); GO --generate some XML data DECLARE @bookstore1 XML = N' <stores> <bookstore specialty="novel" address_city="Seattle"> <book style="autobiography"> <title>Create a Vision</tit...