DB4Breakfast

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

Last several blog posts, we learned the basic of XPath expression , and how to convert XML data to tabular format using OPENXML function. Now, we'll learn XQuery(XML Query Language). As current version of XPath, XPath expressions only allow retrieve data from XML document or fragment. XQuery however, allow retrieval, sort, update, delete, loop, conditional branch, create customized functions, etc... Basically SQL is for query relational database, XQuery is for querying XML document or XML database. Before writing our first XQuery query, let's go over XQuery Data Model. It's important to learn the XQuery data model, because it helps us learning XQuery. For example, when learning SQL it's beneficial to know the basic concepts of relational database such as table, column, row, constraint, data type, index, relationship between tables, etc. You can read more about the W3C XQuery and XPath Data Model 3.0 here. Here's the summary straight out W3C XQuery and XPat...

In this previous blog post, we learned how to use the OPENXML function to covert XML nodes to a single column in tabular format. Today, we'll learn how to convert all the nodes in a XML document to tabular format. We'll use the same XML document on technet with some modifications for the examples below. -- prepare XML document DECLARE @XmlDoc XML = N'<bookstore specialty="novel"> <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="textbook"> <title>XML Reference</title> <author first-name="Mary" last-name="Jane" /> <price>55</price> </book> <book style="novel" id="myfave"> <title>Paths to Success...

Today, at work, our team were designing a screen for collecting some data. I don't think I explained very well how would we implement a many-to-many relationship between 2 entities. I believe I don't understand a subject well enough until I can help other understand how it works. The motivation for this post is helping me solidify my knowledge and help other people. Example: We have a database that holds information about books. A book can have one or many authors, and an author can write one or many books. Let's take a look at the design. Notice that the middle table is the bridge table. The bridge table has 2 foreign keys that reference to the Books and Authors table. Basically, we establish an one-to-many relationship between the Books and the bridge table, and an one-to-many relationship between the Authors and the bridge table. Below is the SQL code for create these 3 tables. CREATE TABLE dbo.Books ( BookId INT NOT NULL PRIMARY KEY, BookTitle VARCH...

Yesterday, one of my co-workers asked how to search for a character pattern within SQL Server with  case sensitive. That mean 'Adam' != 'adam' I didn't know how. So, here's what I come up with after reading SQL Sever book online. -- Return the string if string start with 'Adam', but not 'adam' DECLARE @str VARCHAR(50) = 'Adam Smith'; --using COLLATE clause with case sensitive collation SELECT @str AS CollateResult WHERE @str like 'Adam%' COLLATE Latin1_General_CS_AI; --using binary_checksum function SELECT @str AS ChecksumResult WHERE BINARY_CHECKSUM(SUBSTRING(@str, 1, 4)) = BINARY_CHECKSUM('Adam'); --Using Like keyword. This doesn't work because like can't differentiate -- between upper and lower case with in a case insensitive SQL Server Instance. SELECT @str AS LikeResult1 WHERE @str like 'adam%'; SELECT @str AS LikeResult2 WHERE @str like '[a]dam%'; Anyway, I learned something ne...

After learning about XPath expression , today we'll use what we learned in previous posts. The examples below show converting XML document to tabular format. This process is sometimes called shredding XML. We'll use the same sample XML document as last post with some modifications. Here's the OPENXML function syntax:    OPENXML (idoc int [in], rowpattern nvarchar [in], [flags byte [in]])   [WITH (ColumnName ColumnType [ColumnPattern | MetaProperty] | TableName)] Arguments   idoc :            Document handle created by sys.sp_xml_preparedocument stored procedure   rowpattern : XPath expression to identify the nodes.   flags :           Is optional.  This flag indicates what type of nodes we want to retrieve from the XML document, attributes only, elements only, or both.     byte value 0 = defaults. Select attributes.     byte value 1 = select attributes. If comb...

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

For the exercise below: score 1 point for every planet in the correct order. If you list the first 3 planet s in correct order, but the 4th one incorrect, you'll get 3 points. You'll need a sheet of paper and pen to write down your answers. Also a clock for timer. Here's a link that has a timer. Exercise 1: You have 5 minutes to memory the order of these 9 planets. After that take 1 hour break and come back then list the planets in the correct order. Image source: http://nineplanets.org/

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