DB4Breakfast

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