116
C
HAPTER
9
Avoiding three common query mistakes
Incorrect GROUP BY clauses
Figuring out which columns belong in the
GROUP

BY
clause in an aggregate query
often aggravates
T-SQL
developers. The rule is that any column that is not part of an
aggregate expression in the
SELECT
or
ORDER

BY
clauses must be listed in the
GROUP

BY
clause. That rule seems pretty simple, but I have seen many questions on forums
about this very point.
If a required column is missing from the
GROUP

BY
clause, you will not get incorrect

SELECT COUNT(*) AS CountOfOrders, CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, OrderDate
ORDER BY CustomerID
Another issue to watch out for is including only the column
in the
GROUP

BY
clause when the column is used in an expres-
sion in the
SELECT
list. Say you want the results grouped by
the year in which the orders were placed. If you leave the
order date out of the
GROUP

BY
clause, an error will result. If
you add the column, the error goes away, but the results are
not grouped as expected.
Listing 10 Missing the
GROUP

BY
clause
Listing 11 An extra column in the

SELECT COUNT(*) AS CountOfOrders,
YEAR(OrderDate) AS OrderYear
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY YEAR(OrderDate)
Summary
Learning to write
T-SQL
queries is not a skill you gain overnight. You must overcome
many challenges along the way in order to write queries that return the expected
results. Hopefully, this chapter will help you avoid three common mistakes.
Make sure you always think about
NULL
, especially when
NOT
, not equal to, or less
than (
<>
,
!=
, or
<
) is part of the
WHERE
clause. Remember to continue
LEFT

OUTER

JOIN

included in the
GROUP

BY

clause
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
118
C
HAPTER
9
Avoiding three common query mistakes
About the author
Kathi Kellenberger is a database administrator for Bryan Cave
LLP
, an international law firm headquartered in St. Louis, Mis-
souri. She is coauthor of Professional
SQL
Server 2005 Integration
Services (Wrox, 2006) and author of Beginning
T-SQL
2008
(Apress, 2009). Kathi speaks about
SQL
Server for user groups
and local events and has presented at
PASS
, DevTeach/SQL-
Teach, and

XML
elements from
XML
content

Extract scalar values from
XML
data

Check for the existence of elements or values in
XML
data

Modify your
XML
data via
XML
Data Manipulation Language (
XML

DML
)
extensions
SQL
Server 2008 includes XQuery support with some slight improvements over the
SQL
Server 2005 release. This chapter is designed as an introduction to the XQuery
functionality available in
SQL
Server. In this chapter we will assume little or no

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
120
C
HAPTER
10
Introduction to XQuery on SQL Server
your
XML
data as similar to an operating system directory structure, you can immedi-
ately see the similarities. Consider the simple
XML
document in listing 1.
<Math>
<Constants>
<e>2.71828183</e>
<pi>3.14159265</pi>
<square-root-2>1.41421356</square-root-2>
</Constants>
</Math>
If you were to view this
XML
document as a filesystem, it might
look something like figure 1.
Like your filesystem,
XML
is structured hierarchically. If you
wanted to access the contents of the
pi
file in your filesystem,
you could use a file path like this:

data type method
Description
.exist()
Checks for the existence of a node in your XML data
.modify()
Modifies the content of an XML document
.nodes()
Shreds XML content into relational data
.query()
Queries XML content using XQuery syntax
.value()
Extracts scalar values from XML content
Listing 1 Simple XML document
Figure 1 XML
document viewed as a
filesystem hierarchy
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
121
What is XQuery?
You can also use the wildcard character (
*
) in your path expression to match any
node. The following path expression matches all elements under every
<Constants>
element wherever they occur in your
XML
content:
//Constants/*
The comparison of

true
are returned. As an example, consider a situation in which
you want to return
Colonel

Tom

Parker
. Using the
XML
in listing 2, you could apply a
path expression like the following:
/Officers/Colonel[. = "Tom Parker"]
In this case, the predicate compares the content of the
<Colonel>
elements to the
string literal
"Tom

Parker"
. When it finds one that matches, the matching element is
returned. In this example, it doesn’t make much sense to search for the string literal
"Tom

Parker"
, unless you are just checking to see if the name exists in your
<Colonel>
elements.
Using a different predicate, you can retrieve elements by their attributes. Note that
each of the

prefixing attribute names with an at sign (
@
). In the previous example,
the attribute
id
is specified as
@id
in the path expression.
Listing 2 XML with multiple instances of the same element at the same level
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
122
C
HAPTER
10
Introduction to XQuery on SQL Server
XQuery predicates also provide access to a special function known as
position()
. You
can use the
position()
function to return an element at a specific position in your
XML
data. You can also retrieve
Colonel

Tom

Parker
from the sample

documents would be inefficient.

Querying the textual content of your
XML
documents would degrade perfor-
mance, in many cases severely.

Querying that relies on the raw textual representation of your
XML
documents
would be inflexible, because you couldn’t assign data types to your
XML
docu-
ment content.
In order to accommodate more efficient storage and querying, and to increase flexi-
bility, XQuery converts your raw textual
XML
data to a format known as the XQuery/
XPath Data Model (
XDM
).
XDM
relies on a tree-like representation of your textual
XML
document. Consider the
XML
content in listing 3.
<employee id = "109">
<name>Ken J. Sánchez</name>
<title>CEO</title>

also allows you to type your
XML
data, so that you can manipulate
XML
content
using numeric, date, or other type-specific operations.
NOTE
Creating typed
XML
instances in
SQL
Server requires the use of
XML
schemas, which are beyond the scope of this chapter.
Also keep in mind that when you store
XML
data in a
SQL
Server
xml
data type instance, it is automatically converted to
XDM
form internally.
During the conversion process,
SQL
Server strips document type defini-
tions (
DTD
s) and insignificant whitespace from your
XML

XDM
node hierar-
chy. This conceptual root node is indicated by the leading forward slash (
/
) in a path
expression. The conceptual root node allows XQuery to easily query both non–well-
formed
XML
fragments and well-formed
XML
documents.
NOTE
You can use the keyword
DOCUMENT
when declaring
SQL
Server
xml
data
type columns or variables to restrict their contents to well-formed
XML
documents. Alternatively you can use the keyword
CONTENT
when your
column or variable will contain
XML
data that has more than one root
Document Node
Children: employee
Type-name: annonymous complex type

Element Node
Name: date-of-hire
Parent: employee
Children: text
Type-name: date
Value: 2002-10-12
Text
Parent: name
Content: Ken J. Sánchez
Text
Parent: title
Content: CEO
Text
Parent: date-of-hire
Content: 2002-10-12
Element Node
Name: name
Parent: employee
Children: text
Type-name: string
Value: David Bradley
Element Node
Name: title
Parent: employee
Children: text
Type-name: string
Value: Marketing Mgr
Element Node
Name: date-of-hire
Parent: employee

CON-
TENT
keywords indicate facets that constrain your
xml
data. The default
facet is
CONTENT
. More information is available in Books Online at http://
msdn.microsoft.com/en-us/library/ms187339.aspx.
Querying XML
As we discussed in the section “What is
XQuery?”
SQL
Server’s
xml
data type exposes
several methods that allow you to query and
manipulate
XML
data using XQuery. The
.query()
method is the most basic
xml
data type
method. It accepts an XQuery expression and
returns an
XML
result. Consider listing 4, which
creates an
xml

schema is represented by centralized fact tables which are connected
to multiple dimensions. In the snowflake schema, however, dimensions
are normalized into multiple related tables whereas the star
schema&apos;s dimensions are denormalized with each dimension being
represented by a single table.
</definition>
Listing 4 Querying XML data
Figure 3 Retrieving XML via the
.query()
method
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
125
Querying XML
<source>Wikipedia</source>
</concept>
</definitions>';
SELECT @x.query(N'/definitions/concept[2]/name');
As you can see, the XQuery path expression follows the hierarchical structure of the
XML
document. The first step of the path expression starts at the root of the
XML
doc-
ument and then looks below to the
<definitions>
element. The second step uses a
numeric predicate
[2]
, indicating that the second occur-
rence of the

is used on the entire path expression. This ensures that only a single
scalar value is returned. The
.value()
method will not accept any path expression
that isn’t guaranteed, during the pre-execution static analysis phase of processing, to
return a single scalar value.
NOTE
XQuery uses two-phase processing. Initially there’s a static analysis phase,
during which XQuery checks syntax, data typing, and conformance to
any special requirements (such as returning only a single node or single
scalar value when necessary). XQuery performs pessimistic static type
checking, meaning that it’ll throw errors during the static analysis phase
whenever the path expression could potentially generate a static type
error. After the static analysis phase, XQuery goes into the execution phase,
where your path expression is evaluated against your data.
The
xml
data type also provides the
.exist()
method, which accepts a path expres-
sion and returns
1
if the query returns any nodes, and alternatively returns
0
if the
query doesn’t return any nodes. Consider listing 6, which tells you whether the word
dimensions appears in the character data of any of the
<definition>
elements in the
XML

function to
determine whether a given string is contained within your data. The full
list of XQuery functions and operators (often referred to with the abbre-
viation F &O) available to
SQL
Server XQuery is available in Books Online
at http:
//msdn.microsoft.com/en-us/library/ms189254.aspx.
In this example, we used a different predicate that uses the XQuery
contains
func-
tion. This function accepts a node and a string value. In this example, we used the
period character (
.
), which indicates the current context node. The predicate returns
true
for every node that matches the predicate criteria. In this case, every node that
contains the word dimensions returns
true
. The
contains
function (like
XML
in gen-
eral, and by extension XQuery) is case sensitive. The
.exist()
method is most com-
monly used in the
WHERE
clause of

.nodes()
method
Figure 5 Results of using the
.exist()

method to check for node existence
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
127
FLWOR expressions
FLWOR expressions
You can take advantage of powerful XQuery
FLWOR
expressions (an acronym for the
XQuery keywords
for
-
let
-
where
-
order

by
-
return
) in
SQL
Server.
FLWOR

.nodes()
method
Querying
.nodes()
results
Although the
.nodes()
method returns a result set of
xml
data type, it is a function-
ally limited version of the
xml
data type. You can’t query the result set instances di-
rectly. The only way to access the contents of the result set are through the use of
the other
xml
data type methods, such as
.value()
or
.query()
. If you do try to
query the contents directly, you’ll get an extremely verbose error message similar to
the following:
Msg 493, Level 16, State 1, Line 35
The column 'Col' that was returned from the nodes() method cannot be
used directly. It can only be used with one of the four XML data type
methods, exist(), nodes(), query(), and value(), or in IS NULL and IS
NOT NULL checks.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

content. In this case, we’ve taken the content of every
<name>
element in
the source
XML
document and reformatted that content as
<topic>
elements.
The
let
keyword allows you to bind tuples generated by the
for
clause tuple
stream to variables. Consider listing 9, where we use the
let
clause to assign the char-
acter content of each
<name>
element to a variable named
$j
. The results are the same
as those generated by listing 8.
SELECT @x.query
(
N'for $i in //name
let $j := $i/text()[1]
return <topic>{$j}</topic>'
);
NOTE
The

expression with the
order

by

clause
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
129
XQuery comparison operators
The
order by
clause can accept the
ascending
or
descending
keywords to indicate
sort direction. Ascending is the default if you don’t explicitly specify a sort order. If
you don’t use an
order

by
clause in your
FLWOR
expressions, results are always
returned in document order. Document order is the default order in which elements
occur in your
XML
document or data. The
FLWOR

(
N'for $i in //concept
let $j := ($i/name/text())[1], $k := ($i/source/text())[1]
where $k eq "Wikipedia"
order by $j ascending
return <topic>{$j}</topic>'
);
The predicate in the
where
clause uses the same operators as predicates in path
expressions. These operators are described in the next section.
XQuery comparison operators
XQuery supports several operators for comparing values, nodes, and sequences. A
sequence is an ordered collection of zero or more items. The items can be nodes or
atomic values, although
SQL
Server supports only homogenous sequences, or those
that don’t mix nodes and atomic values in a single XQuery sequence.
NOTE
The term ordered, as it applies to XQuery sequences, generally means doc-
ument order as opposed to alphabetic or numeric order. In the XPath 1.0
recommendation, the concept of node sets is used instead of sequences. In
node sets, the order is unimportant and duplicate nodes are disallowed.
XQuery sequences stress the importance of order (as order is important
in
XML
documents), and allow duplicate nodes.
Sequences are represented as follows in XQuery:
(10, 1, (2, 3), 5, 4, 6, 7, 8, 8, (), 9)
Sequences are a core concept within XQuery, and worth discussing further. Some of

a single atomic scalar value is equivalent to that atomic scalar value. Because of this
property, the sequence (3.141592) is equal to the atomic scalar value 3.141592, and
the code sample in listing 12 returns a result of
true.
DECLARE @x xml;
SET @x = N'';
SELECT @x.query('(3.141592) eq 3.141592');
XQuery supports several operators that can be used in expressions and predicates.
These operators are listed in table 2.
XQuery comparison operators are classified in three groups, as shown in table 2. Value
comparison operators are those operators that allow you to compare scalar atomic values
to one another. Listing 13 demonstrates the
lt
value comparison operator. The result
returned is
true
.
Listing 12 Comparing a sequence with a single value to a scalar value
Table 2 XQuery comparison operators
Value comparison operators General comparison operators
eq Equal to = Equal to
ne Not equal to != Not equal to
gt Greater than > Greater than
ge Greater than or equal to >= Greater than or equal to
lt Less than < Less than
le Less than or equal to <= Less than or equal to
Node comparison operators
is Node identity equality
>> Left node follows right node
<< Left node precedes right node

general comparison operator.
Because the “3” in the sequence on the left is equal to the “3” in the sequence on the
right, the result of the comparison is
true
.
The final group of operators consists of the node comparison operators. These opera-
tors allow you to compare nodes. In listing 15, the first expression uses the node com-
parison
<<
operator to determine whether the
/family/mother
node appears before
the
/family/father
node, in document order. The second expression uses the
is
operator to determine whether the first node returned by the
//child
path is the
same as the first node returned by the
/family/child
path expression. The result of
both expressions in the example is
true
.
DECLARE @x xml;
SET @x = N'<?xml version = "1.0"?>
<family surname = "Adams">
<mother>Morticia</mother>
<father>Gomez</father>