DB4Breakfast

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

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