Writing XQuery Queries In SQL Server 2012

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</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>
      <author>
        <first-name>Joe</first-name>
        <last-name>Cantrell</last-name>
      </author>
      <price>20</price>
    </book>
  </bookstore>
  <bookstore specialty="science" address_city="London">
    <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</title>
      <author>
        <first-name>Toni</first-name>
        <last-name>Wilson</last-name>
      </author>
      <price intl="Canada" exchange="0.7">6.50</price>
    </book>
  </bookstore>
</stores>';

DECLARE @bookstore2 XML = 
N'
<stores>
  <bookstore specialty="computing" address_city="Seattle">
    <book style="textbook">
      <title>Advanced Game Testing</title>
      <author>
        <first-name>Sam</first-name>
        <last-name>Heng</last-name>
      </author>
      <price>20</price>
    </book>
    <book style="textbook">
      <title>XML Basic</title>
      <author>
        <first-name>Najwa</first-name>
        <last-name>Ebanks</last-name>
      </author>
      <price>30</price>
    </book>
  </bookstore>
</stores>';

--insert xml data to the table.
INSERT INTO #business(OwnerId, OwnerName, stores)
VALUES (1, 'Hai Ton', @bookstore1)
      ,(2, 'Phuong Tran', @bookstore2);

--get all rows in the table
SELECT OwnerId, 
       OwnerName, 
       stores
FROM   #business;

--
--to use XQuery query in SQL Server, we put XQquery inside of the query() method.
--note: this case, the XQuery query is just a simple XPath Expression that
--      returns all data in the XML column.
SELECT OwnerName, 
       stores.query('//stores') AS XML_Result
FROM   #business;

--
--get the first book in each bookstore
--Since the first owner has 2 bookstores, there'll 2 books 
--   return for that owner's bookstores
SELECT OwnerName, 
       stores.query('stores/bookstore/book[1]') AS XML_Result
FROM   #business;

--
--get all books with price <= $5.0 regardless of the currency.
--result is an empty sequence for the bookstore column
SELECT OwnerName, 
       stores.query('stores/bookstore/book[price <= 5.0]') AS XML_Result
FROM   #business;

--
--get all books with price <= $20.0 regardless of the currency
SELECT OwnerName, 
       stores.query('stores/bookstore/book[price <= 20.0]') AS XML_Result
FROM   #business;

--
--unlike query above, this query returns true or false as XML.
--because we did not use the [] for the predicate
SELECT OwnerName, 
       stores.query('stores/bookstore/book/price <= 20.0') AS XML_Result
FROM   #business;

--
--get all books with price >= $30.0 regarless of the currency, 
-- or the author first name = "Toni"
SELECT OwnerName,
       stores.query('stores/bookstore/book[price >= 30.0 or ./author/first-name="Toni"]') AS XML_Result
FROM   #business;

Reference:
W3c XQuery 3.0

XQuery Basics: XQuery Data Model Overview

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 XPath Data Model 3.0 document.

Every instance of the data model is a sequence.
A sequence is an ordered collection of zero or more items. Note: a sequence cannot be a member of a sequence.
An item is either a node, a function, or an atomic value.
There're 7 kind of nodes:
  document, element, attribute, text, processing instruction, comment, and namespace.
A function is an item that can be called. A list of XQuery Functions can be found here.
An atomic value is a value of an atomic type.
An atomic type is a primitive simple type or a type derived by restriction from another atomic type.
There're 21 built-in primitive simple types:
   string, boolean, decimal, float, double, duration, datetime, time, date, gYearMonth, gYear, gMonthDay, gDay, gMonth, hexBinary, base64binary, anyURI, QName, NOTATION.

The following pictures provide summary for the W3C XQuery and XPath Data Model 3.0

Images source: W3C XQuery and XPath Data Model 3.0


Reference:
W3C XQuery and XPath Data Model 3.0
XQuery Reference
XQuery Functions for XML data type
XQuery 3.0 from W3C

Shredding XML using OPENXML: Convert XML to Rowset part II

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</title>
    <author>
      <first-name>Toni</first-name>
      <last-name>Wilson</last-name>
    </author>
    <price intl="Canada" exchange="0.7">6.50</price>
  </book>
</bookstore>';

--
--An integer for pointing to the internal 
-- representation(XML Document Object Model)
--  of the XML document above.
DECLARE @DocHandle INT; 

-- Parse the XML document to XML DOM tree representation 
-- and returns a handle
EXECUTE sys.sp_xml_preparedocument @DocHandle OUTPUT, @XmlDoc;

-- Select both authors first and last name
-- Note:
--  The @ symbol indicates select attribute nodes.
--  The | operator union the both attribute and element nodes.
--  The column pattern XPath expression in the WITh clause will overwrite 
--      the flag(3rd parameter) of the OPENXML function.
SELECT *
FROM OPENXML(@DocHandle, '/bookstore/book/author', 1)
WITH(AuthorFirstName VARCHAR(20) '@first-name | first-name'
    ,AuthorLastName VARCHAR(20) '@last-name | last-name');

-- Select all information for all books
-- Note: since we start at /bookstore/book, 
--    to get to author first and last name,
--    I modified the XPath expression to 
--    'author/@first-name | author/first-name'
SELECT *
FROM OPENXML(@DocHandle, '/bookstore/book', 3)
WITH(Style VARCHAR(20) '@style'
    ,Id VARCHAR(20) '@id'
    ,Title VARCHAR(30) '@title | title'
    ,AuthorFirstName VARCHAR(20) 'author/@first-name | author/first-name'
    ,AuthorLastName VARCHAR(20) 'author/@last-name | author/last-name'
    ,Price DECIMAL(6,2) 'price');

-- Change the columns display ordering.
SELECT Title, Style, Id, AuthorFirstName, AuthorFirstName, Price
FROM OPENXML(@DocHandle, '/bookstore/book', 3)
WITH(Style VARCHAR(20) '@style'
    ,Id VARCHAR(20) '@id'
    ,Title VARCHAR(30) '@title | title'
    ,AuthorFirstName VARCHAR(20) 'author/@first-name | author/first-name'
    ,AuthorLastName VARCHAR(20) 'author/@last-name | author/last-name'
    ,Price DECIMAL(6,2) 'price');

-- Removes the internal representation
--  and invalidate the document handle.
EXECUTE sys.sp_xml_removedocument @DocHandle;

Result for 3 queries above:

Next several posts, we'll learn how to use XQuery for querying XML.

Reference:
XPath Operators
OPENXML
OPENXML Function
OPENXML Examples
sp_xml_preparedocument
sp_xml_removedocument
XML Edge Table
XML Document Object Model(XML DOM)

Many-to-many Relationship

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 VARCHAR(50) NOT NULL
);
GO

CREATE TABLE dbo.Authors (
   AuthorId  INT NOT NULL PRIMARY KEY,
   LastName  VARCHAR(30) NOT NULL,
   FirstName VARCHAR(30) NOT NULL
);
GO

--bridge table. 
CREATE TABLE dbo.BooksAuthors (
   BookId INT NOT NULL FOREIGN KEY REFERENCES dbo.Books(BookId),
   AuthorId INT NOT NULL FOREIGN KEY REFERENCES dbo.Authors(AuthorId),
   CONSTRAINT BooksAuthors_PK PRIMARY KEY(BookId, AuthorId)
);
GO

--generate some sample data
INSERT INTO dbo.Books (BookId, BookTitle)
VALUES (1, 'Be Happy'),
       (2, 'Smiles'),
       (3, 'NoSQL 101');
GO

INSERT INTO dbo.Authors (AuthorId, LastName, FirstName)
VALUES (1, 'Preston', 'Ton' ),
       (2, 'Phuong' , 'Tran'),
       (3, 'Jocelyn', 'Lui' )
GO

-- Let assume:
-- book with title "Be Happy" was written by Preston Ton and Phuong Tran.
-- book with title "Smiles" was written by Phuong Tran and Jocelyn Lui.
-- book with title "NoSQL 101" was written by Jocelyn Lui.
-- Here is the sample data for the bridge table:
INSERT INTO dbo.BooksAuthors (BookId, AuthorId)
VALUES (1, 1),
       (1, 2),
       (2, 2),
       (2, 3),
       (3, 3);
GO

--A query to show the all books with all authors. 
SELECT b.BookId  , b.BookTitle
      ,a.AuthorId, a.LastName + ', ' + a.FirstName AS AuthorName 
FROM dbo.BooksAuthors AS ba
JOIN dbo.Authors AS a ON ba.AuthorId = a.AuthorId
JOIN dbo.Books   AS b ON ba.BookId   = b.BookId;

One final note: for the bridge table, we can add some other columns if needed.

Character Search With Case Sensitive

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 new. Hope you find this post useful. If you know other solution, please let me know. I like to learn different ways to solve the same problem.

References:
binary_checksum function
Collate clause
Like

Shredding XML using OPENXML: Convert XML to Rowset part I

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 combined with XML elements, select attributes first then elements.
    byte value 2 = select elements. If combined with XML attributes, select attributes first then elements.
    byte value 3 = select attributes and elements.
    byte value 8 = this flag's value is used to combine with byte value 0 and 2.

Note: the column pattern in the WITH clause will override the 3rd parameters in OPENXML.

Here's a picture provides overview of the process of converting xml to table format.

Image source: Microsoft Technet.

-- step 1:
-- 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</title>
    <author>
      <first-name>Toni</first-name>
      <last-name>Wilson</last-name>
    </author>
    <price intl="Canada" exchange="0.7">6.50</price>
  </book>
</bookstore>';

--
--An integer to point to the internal representation(XML Document Object Model) of the XML document above.
DECLARE @DocHandle INT; 

--parse the XML document to XML DOM tree representation and returns a handle
EXECUTE sys.sp_xml_preparedocument @DocHandle OUTPUT, @XmlDoc;

--step 2: convert XML to grid format using OPENXML function.
--when not using the WITH clause, the result is an Edge Table Format
SELECT *
FROM OPENXML(@DocHandle, '/bookstore');

The following examples, we will convert the authors first-names to tabular format.

--
----------------------------------------------------
-- All these 3 queries return the same result set --
----------------------------------------------------
-- when the 3rd parameter of OPENXML is missing, 
--the default mapping is used. Select the attributes only 
SELECT * 
FROM OPENXML(@DocHandle, '/bookstore/book/author')
WITH("first-name" VARCHAR(20));

--3rd parameter of OPENXML = 0,
--Select the attributes only 
SELECT * 
FROM OPENXML(@DocHandle, '/bookstore/book/author', 0)
WITH("first-name" VARCHAR(20));

--3rd parameter of OPENXML = 1, select the attributes only 
SELECT * 
FROM OPENXML(@DocHandle, '/bookstore/book/author', 1)
WITH("first-name" VARCHAR(20));

--3rd parameter of OPENXML = 2, 
--select the elements only 
SELECT * 
FROM OPENXML(@DocHandle, '/bookstore/book/author', 2)
WITH("first-name" VARCHAR(20)); 

--3rd parameter of OPENXML = 3, 
--select both attributes and elements  
SELECT * 
FROM OPENXML(@DocHandle, '/bookstore/book/author', 3)
WITH("first-name" VARCHAR(20)); 

--
--3rd parameter of OPENXML = 9 (this is flag value 8 + flag value 1,
-- result the same as using flag value 0 or 1), select attributes only  
SELECT * 
FROM OPENXML(@DocHandle, '/bookstore/book/author', 9)
WITH("first-name" VARCHAR(20));

--
--3rd parameter of OPENXML = 10(this is flag value 8 + flag value 2, 
-- result the same as using flag value 2), select elements only.
SELECT * 
FROM OPENXML(@DocHandle, '/bookstore/book/author', 10)
WITH("first-name" VARCHAR(20)); 

--
--3rd parameter of OPENXML = 11 (this is flag value 8 + flag value 3,
-- result the same as using flag value 3), select both attributes and elements. 
SELECT * 
FROM OPENXML(@DocHandle, '/bookstore/book/author', 11)
WITH("first-name" VARCHAR(20));

--
--3rd parameter of OPENXML not in (0, 1, 2, 3, 9, 10, 11), 
-- default is used -- select only attributes.  
SELECT * 
FROM OPENXML(@DocHandle, '/bookstore/book/author', 444)
WITH("first-name" VARCHAR(20));

--
-- Final step: Removes the internal representation
--  and invalidate the document handle.
EXECUTE sys.sp_xml_removedocument @DocHandle;

For all queries above, the column pattern in the WITH clause is not needed because the column name(first-name) is the same as first-name attribute and first-name element in the XML document above.
That's all for now. I don't want to make this post too long. I'll provide more examples next 2 posts.

Reference:
OPENXML
OPENXML Function
OPENXML Examples
sp_xml_preparedocument
sp_xml_removedocument
XML Edge Table
XML Document Object Model(XML DOM)

XPath: Query XML Exercises

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">
    <author>
      <first-name>Mary</first-name>
      <last-name>Bob</last-name>
      <publication>Selected Short Stories of
        <first-name>Mary</first-name>
        <last-name>Bob</last-name>
      </publication>
    </author>
    <editor>
      <first-name>Britney</first-name>
      <last-name>Bob</last-name>
    </editor>
    <price>55</price>
  </book>
  <magazine style="glossy" frequency="monthly">
    <price>2.50</price>
    <subscription price="24" per="year"/>
  </magazine>
  <book style="novel" id="myfave">
    <author>
      <first-name>Toni</first-name>
      <last-name>Bob</last-name>
      <degree from="Trenton U">B.A.</degree>
      <degree from="Harvard">Ph.D.</degree>
      <award>Pulitzer</award>
      <publication>Still in Trenton</publication>
      <publication>Trenton Forever</publication>
    </author>
    <editor>
      <first-name>Preston</first-name>
      <last-name>Phuong</last-name>
    </editor>
    <price intl="Canada" exchange="0.7">6.50</price>
    <excerpt>
      <p>It was a dark and stormy night.</p>
      <p>But then all nights in Trenton seem dark and
      stormy to someone who has gone through what
      <emph>I</emph> have.</p>
      <definition-list>
        <term>Trenton</term>
        <definition>misery</definition>
      </definition-list>
    </excerpt>
  </book>
  <my:book xmlns:my="uri:mynamespace" style="leather" price="29.50">
    <my:title>Who's Who in Trenton</my:title>
    <my:author>Robert Bob</my:author>
  </my:book>
</bookstore>

Let's go over some XPath syntax
Expression                     Description
   /                           Root node(absolute path)
   Node                   The node (relative path)
   //                          All descendant nodes of the current node
   ./                          Current node
   ../                         Parent of current node
   ../../                      Grandparent of current node
  @                         Attribute nodes

Let's go over some examples using the XML document above. Note XML is case sensitive.
1. ALL elements in the XML document.
bookstore
./bookstore
//bookstore

2. All <book> elements. 
bookstore/book
./bookstore/book
//bookstore/book

3. All element with attribute.
//@*
bookstore//@*

4. All <book> element with author's <first-name> element value equals to Mary.
bookstore/book/author[first-name = "Mary"]
bookstore//author[first-name = "Mary"]
.//author[first-name = "Mary"]
*//author[first-name = "Mary"]

5. All <book>  element with author's [last-name] element value equal to Bob and editor's <first-name> element value equals to Preston.
bookstore/book/author[last-name = "Bob" and ../editor[first-name = "Preston"]]

6. All <book> element with author's [last-name] element value equals to Bob and <price> element >= 10. 
bookstore/book/author[last-name = "Bob" and ../price >= 10]

7. All <book> element with author's [last-name]  element value equals to Bob and <price>  element >=10 
bookstore/book[author[last-name = "Bob" and ../price >= 10]]

8. All <book> element with id attribute.
bookstore/book[@id]

9. All <book> element with attribute style = textbook.
bookstore/book[@style = "textbook"]

10. All <book> element with <price> element value >= 10
/bookstore/book/price[.>=10]
/bookstore/book/price[node()>=10]

Notice the examples above the two periods (..) syntax is equivalent to go up one level, and single period (.) is equivalent to current context. This just like in a file system. Try this, if you are using windows OS, launch command prompt and type this command and press enter: cd ../ (this command change the current directory to the parent folder of the current path) and this: dir . (this command lists all the file and folders in the current path).

Anyway, just like SQL, XPath only need a few days to learn but a lifetime to master.

Memory Exercise: 9 planets

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/

XML Path Use Case: Pivot Row Data

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].[StateProvince] AS sp ON a.StateProvinceID = sp.StateProvinceID
WHERE sp.StateProvinceCode = 'WA'
GROUP BY sp.Name, a.City
ORDER BY cnt DESC;

--
--Let's pivot top 10 cities in Washington state with the most addresses
SELECT piv.* 
FROM (
 SELECT TOP(10) 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 cnt DESC
) AS src
PIVOT (
  MAX(cnt) FOR City IN ("Burien","Bellingham","Bremerton","Seattle","Redmond","Everett","Issaquah","Edmonds","Renton","Lynnwood")
) AS piv;

--
-- What if you don't want to manually type all the 10 cities in the IN operator above? 
-- Here's how to combine all 10 cities into one single string then copy and paste.
SELECT '"' + src.City + '",'
FROM (
 SELECT TOP(10) 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 a.City
 ORDER BY cnt DESC
) AS src
FOR XML PATH('');

--
-- Last but not least, here's how to delimit the cities by comma 
--    and enclose each city with a pair of single quote.
SELECT DISTINCT '''' +  a.city  + ''',' 
FROM Person.Address AS a
JOIN [Person].[StateProvince] AS sp ON a.StateProvinceID = sp.StateProvinceID
WHERE sp.StateProvinceCode = 'WA'
FOR XML PATH('');

That's all for now. Finally we can apply what we learned on last month XML posts.