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

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

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

It's important to have a solid understanding of XPath Expression. This will help us convert XML document to tabular format  using both OPENXML function and XQuery. If you want to practice querying XML, use this online XPath Tester tool. Or download this XPath Visualizer and install on your computer. Note press Ctrl + N in the XPath Visualizer to create a new source widows then copy and paste the XML source. For our example, I'll use this  XML document . You can view a set of XPath  exercises  from MSDN. <bookstore specialty="novel">   <book style="autobiography">     <author>       <first-name>Joe</first-name>       <last-name>Bob</last-name>       <award>Trenton Literary Review Honorable Mention</award>     </author>     <price>12</price>   </book>   <book style="textbook">   ...

Yesterday, one of my co-worker wanted to learn combining rows into a single comma delimited string, so she can use the result in an IN operator part of a WHERE clause. In this post, I'll provide an example where XML PATH mode can be used to convert a rowset to a single string. The query below is very handy when you work with pivoting data or need a string to use with the IN clause. USE AdventureWorks2012; GO -- all addresses SELECT * FROM Person.Address; -- Number of addresses in each city in Washington state. SELECT sp.Name AS StateName, a.City, COUNT(a.AddressID) AS cnt FROM Person.Address AS a JOIN [Person].[StateProvince] AS sp ON a.StateProvinceID = sp.StateProvinceID WHERE sp.StateProvinceCode = 'WA' GROUP BY sp.Name, a.City ORDER BY a.City; -- -- How about list top 10 cities in Washington state with the most addresses SELECT TOP(10) sp.Name AS StateName, a.City, COUNT(a.AddressID) AS cnt FROM Person.Address AS a JOIN [Person].[Sta...

Last post, we explored some useful cases where STUFF function can be used. This post we'll take a step further by combining STUFF with XML PATH to combine rows to a single string and separate the values with commas. Let's remind ourselves the STUFF function syntax: STUFF ( original_string, start_position , length , replaceWith_string )  /**************************************************************/  /* Delimit a string of text. Insert a comma between strings.*/ /**************************************************************/  DECLARE @LogicalQueryProccessingOrder TABLE ( phase VARCHAR(10) ); INSERT INTO @LogicalQueryProccessingOrder  VALUES ('FROM'), ('WHERE'), ('GROUP BY'), ('HAVING'), ('SELECT'), ('ORDER BY'); /* this doesn't work because result has an extra comma at the end. Note this return in xml format */ SELECT phase + ', ' FROM @LogicalQueryProccessingOrder FOR XML PATH(...