156
C
HAPTER
12
Using XML to transport relational data
entities—the
XML
Schema. Again, the subject of
XML
and
XML
Schema exceeds the
scope of this article; therefore let’s emphasize one principal benefit of using the
XML
Schema.
In
SQL
Server, the
XML
standard is implemented as a data type, and because all data
types typically represent (implement) a specific (data) domain, the purpose of the
XML
Schema in regards to the
XML
data type is to enforce its domain.
The
XML
Schema will provide us with a guarantee that the discography data com-
ing in or going out of our database is valid—that it complies with the business rules.
ENTITIES OF PRINCIPAL IMPORTANCE
Let’s take another look at the physical model. We can see two entities that stand out as

The
XML
data domain is similar to the two examples in the previous paragraph, but
is governed by a much more complex set of rules defined by an
XML
Schema.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
157
Understanding before coding

<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:ma="http://schemas.milambda.net/Music-Album"
xmlns:m="http://schemas.milambda.net/Music"
elementFormDefault="qualified"
attributeFormDefault="qualified"
targetNamespace="http://schemas.milambda.net/Music-Album">
<xs:import namespace="http://schemas.milambda.net/Music"
➥
schemaLocation="common.xsd"/>
<xs:element name="discography">
<xs:complexType>
<xs:sequence>
<xs:element name="album" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="track" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>

out the Band Members from the Album definition, clearly eliminating unnecessary
data redundancy, as shown in listing 2.
Listing 1 The Album XML Schema
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
158
C
HAPTER
12
Using XML to transport relational data

<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:mb="http://schemas.milambda.net/Music-Band"
xmlns:m="http://schemas.milambda.net/Music"
elementFormDefault="qualified"
attributeFormDefault="qualified"
targetNamespace="http://schemas.milambda.net/Music-Band">
<xs:import namespace="http://schemas.milambda.net/Music"
➥
schemaLocation="common.xsd"/>
<xs:element name="bands">
<xs:complexType>
<xs:sequence>
<xs:element name="band" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="member" type="m:person" maxOccurs="unbounded"/>
</xs:sequence>
<xs:attribute name="bandName" type="m:bandName" use="required"/>

XML
,
and the nesting of
XML
elements. For example, following the logical model rule, which
states that the Discography entity contains Album entities, the Album element is
placed inside the Discography element, and because an Album entity contains Track
entities, the latter are represented by elements nested inside the Album element.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
159
Understanding before coding
XML
Schemas use a shared collection of types. These shared types are defined in the
Common
XML
Schema, shown in listing 3.
For instance, the Person entity is present in the Album
XML
as well as the Band
XML
; therefore both can use the same type for the Person entity, rather than explicitly
implementing two separate types with the same set of properties.
<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:m="http://schemas.milambda.net/Music"
elementFormDefault="qualified"
attributeFormDefault="qualified"
targetNamespace="http://schemas.milambda.net/Music">
<xs:simpleType name="personName">

XML
Schemas
instead of one, look at the
XML
examples containing partial discography data of two
well-known rock bands published at http:
//www.manning.com/SQLServerMVPDeep
Dives (both XML Schemas are also located there).
Listing 3 Common XML Schema
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
160
C
HAPTER
12
Using XML to transport relational data
Enabling and maintaining the data flow
After implementing the data store part of the data model, we can now focus on the
operational part of the logical model. We mentioned three data management opera-
tions that will be supported by our solution: entity creation, entity modification, and
entity retrieval.
Regarding their relationship to the data flow, we can divide the supported data
management operations into two groups:

Inbound operations—Govern the flow of data into the database. Create and Update
are both inbound operations;

Outbound operations—Govern the flow of data out of the database. Read is the
outbound operation.
With inbound operations, our objective should be clear. We’ll have to

in figure 1, the dependency of individual sets of data can be observed (follow the arrows
and identify where they all point to). When importing the data into the database, we
should start with the independent entities and finish with dependent ones.
This is a valid order of inbound operations for the Album
XML
Schema:
1
Title (doesn’t depend on any other entity)
2
Album (depends on Title)
3
Track (depends on Title and Album)
4
Person (doesn’t depend on any other entity)
5
Track Author (depends on Person and Track)
6
Band (doesn’t depend on any other Entity)
7
Track Performer (depends on Band and Track)
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
161
Enabling and maintaining the data flow
This is a valid order of inbound operations for the Band
XML
Schema:
1
Band (doesn’t depend on any other entity)
2

method can also be used to create
XML
data, but in this
chapter we’ll only use it to retrieve data. The return type of the
query()
method is
XML
. We’ll use this method to specify the target of the extraction
operation and to transform the source data if needed.

The purpose of the
nodes()
method is to read data from an
XML
entity and
return a set of
XML
nodes. This method returns a row of
XML
data for each node
in the
XML
entity that corresponds to the given criteria. We’ll use this method
to retrieve the data from the
XML
source in the form of a dataset representing a
single entity or a single relationship between our entities.
The execution of all three methods is governed through an XQuery statement or an
XPath expression passed to each of the methods as an argument. A detailed explanation
of XQuery and XPath expressions is once again outside the scope of this chapter, but

T-SQL
) query.
An XPath expression can be extended with an XPath predicate, the purpose of which
is to restrict the traversal of the
XML
entity even further.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
162
C
HAPTER
12
Using XML to transport relational data

In this chapter, no data management operations against
XML
entities will require any
knowledge of XQuery.
In table 3, we can see the XPath expressions pointing to individual entities of the
Album
XML
Schema, and in table 4 we can see the XPath expressions pointing to indi-
vidual entities of the Band
XML
Schema.
Note that in tables 3 and 4, the names of the elements are prefixed with a reference to
the respective
XML
namespace implemented by each
XML

Track /ma:discography/ma:album/ma:track
Person /ma:discography/ma:album/ma:track/ma:author
Band /ma:discography/ma:album/ma:track/ma:band
Table 4 XPath expressions used to extract the entities from the Band XML
Entity XPath expression
Band /mb:bands/mb:band
Person /mb:bands/mb:band/mb:member
A few words on XPath expressions and XQuery statements (continued)
For example, the
/orders/order/orderDate[.

>

20080101]
XPath expression con-
tains an XPath predicate (enclosed in square brackets) restricting the XPath expres-
sion to point to only those elements named
orderDate
that contain values greater
than 20080101.
We could compare the XPath predicate with the
WHERE
clause of a
T-SQL
query.
Compared to the XPath expression, the XQuery statement provides additional func-
tionality needed in extracting the data from
XML
entities and transforming it. An XQue-
ry statement can also be used to write

violations—most of all, we’ll need to prevent the import of data that already exists in
the database.
EXTRACTING ALBUM DATA
The source of the Album data is an
XML
entity based on the Album
XML
Schema
shown in listing 1 earlier in this chapter. This
XML
Schema provides the structure to
hold the data for the Title, Album, and Person entities, including data for the Track
Author and Track Performer associative entities.
All the details regarding XPath functions implemented in
SQL
Server are available
in the Books Online article titled “XQuery Functions against the xml Data Type.”
A few words about XML namespaces
First of all, the subject of
XML
namespaces exceeds the scope of this chapter. But
what you should know about
XML
namespaces in order to understand their role in
these examples is that they represent the business domain in which a particular
XML
entity exists.
In our examples, we’ve introduced three
XML
namespaces: one for Album data, an-

XML
namespaces that will be
used in XPath expressions. All the details regarding
XML
namespaces in
SQL
Server
and the
WITH

XMLNAMESPACES
clause can be found in Books Online.
General information regarding
XML
namespaces can also be found online: http://
www.w3.org/
TR
/xml-names/.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
164
C
HAPTER
12
Using XML to transport relational data
In the following examples,
@xml
designates a variable of the
XML
type holding the

('
/ma:discography/ma:album/ma:track
') Discography (Track)
The Title entity contains both the Album and the Track titles. Because in
SQL
Server
2005 it’s not possible to specify a union XPath expression, the two sets must be merged
into one using the
T
-
SQL

UNION
clause.
Using the union XPath expression, the query could be simplified as shown in
listing 5.
with xmlnamespaces
(
'http://www.w3.org/2001/XMLSchema-instance' as xsi
,'http://schemas.milambda.net/Music' as m
,'http://schemas.milambda.net/Music-Album' as ma
)
select Discography.Album.query
('
Listing 4 Extracting the titles
Listing 5 Simplified query with union XPath expression
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
165
Enabling and maintaining the data flow

').value
(
'.'
,'datetime'
) as Published
from @xml.nodes
('
/ma:discography/ma:album
') Discography (Album)
Listing 7 shows the code to extract the tracks.
with xmlnamespaces
(
'http://www.w3.org/2001/XMLSchema-instance' as xsi
,'http://schemas.milambda.net/Music' as m
,'http://schemas.milambda.net/Music-Album' as ma
)
select Discography.Track.query
Listing 6 Extracting the albums
Listing 7 Extracting the tracks
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
166
C
HAPTER
12
Using XML to transport relational data
('
data(@ma:title)
').value
(

') Discography (Track)
Listing 8 shows the code to extract the persons.
with xmlnamespaces
(
'http://www.w3.org/2001/XMLSchema-instance' as xsi
,'http://schemas.milambda.net/Music' as m
,'http://schemas.milambda.net/Music-Album' as ma
)
select distinct
Discography.Person.query
('
data(@m:firstName)
').value
(
'.'
,'nvarchar(150)'
) as FirstName
Listing 8 Extracting the persons
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
167
Enabling and maintaining the data flow
,Discography.Person.query
('
data(@m:middleName)
').value
(
'.'
,'nvarchar(150)'
) as MiddleName

/ma:discography/ma:album/ma:track/ma:band
') Discography (Band)
EXTRACTING BAND DATA
The source of the Band data is an
XML
entity based on the Band
XML
Schema shown
in listing 2 earlier in this chapter. This
XML
Schema provides all the data for the Band
and Person entities, including the data for the Band Member associative entity.
Compare the
XML
namespace declarations in listing 10 with the declaration in the
code listings presented earlier. Is there something different? Why?
Listing 9 Extracting the bands
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
168
C
HAPTER
12
Using XML to transport relational data

with xmlnamespaces
(
'http://www.w3.org/2001/XMLSchema-instance' as xsi
,'http://schemas.milambda.net/Music' as m
,'http://schemas.milambda.net/Music-Band' as mb

Listing 11 shows the code to extract the persons.
with xmlnamespaces
(
'http://www.w3.org/2001/XMLSchema-instance' as xsi
,'http://schemas.milambda.net/Music' as m
,'http://schemas.milambda.net/Music-Band' as mb
)
select distinct
Bands.Band.query
('
data(@mb:bandName)
').value
(
'.'
Listing 10 Extracting the bands
Listing 11 Extracting the persons
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
169
Enabling and maintaining the data flow
,'nvarchar(450)'
) as [Name]
,nullif(Bands.Band.query
('
data(@mb:established)
').value
(
'.'
,'datetime'
), N'') as Established

XML
source by com-
bining the queries used in retrieving the data of the individual primary entities of a
particular relationship. The combinations are listed in table 5.
Table 5 Retrieving the associative entities
Associative entity Provided by combining these primary entities
Track Author Track joined with Person—based on the nesting of the Author XML ele-
ment inside the Track XML element of the Album XML
Track Performer Track joined with Band—based on the nesting of the Band XML ele-
ment inside the Track XML element of the Album XML
Band Member Band joined with Person—based on the nesting of the Person XML ele-
ment inside the Band XML element of the Band XML
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
170
C
HAPTER
12
Using XML to transport relational data
Similarly, the one-to-many relationships between primary entities can be retrieved:

A Track is related to the corresponding Album based on the nesting of the
Track
XML
element inside the Album
XML
element.

A Track is related to a Title based on the value of the Title
XML

XML
source, extract the data that represents each primary entity (in
the order mentioned in the section “Preparing the inbound data flow”).
2
Insert the data into the database table, but exclude rows that already exist at the
destination (using the
EXCEPT
clause or the
NOT EXISTS
predicate).
3
Save the data of each primary entity in a table variable, including the surrogate
key values that the rows received when they were inserted into the database
table.
4
After both primary entities of a particular one-to-many relationship have been
inserted and temporarily saved in the corresponding table variables, insert the
data representing these relationships to the associative database tables.
5
After all the data has been extracted and all primary and associative entities
have been inserted, the process finishes.
After you’ve carefully studied both stored procedures and have identified all the con-
cepts presented in this chapter, prepare a
T
-
SQL
script to import the
XML
samples.
Execute the script in steps: one

XML
entity. You can find all the details regarding the
FOR

XML
clause in
Books Online.
Let’s start with the simpler of the two queries. As we defined earlier in this chapter,
a Discography contains one or more Bands containing one or more Members. In the
Band
XML
Schema, the relationship between the Band and the Person entities is
implemented in form of
XML
elements representing the Band Members nested inside
the
XML
element representing each individual Band.
In listing 12, you can observe how the
FOR

XML
query used to retrieve the Person
entity data is nested inside the
FOR

XML
query used to retrieve the Band entity data.
The result from the nested query is exposed as a column in the outer query, and the
name of this column is specified in the

for xml path('mb:member'), type
)
from Music.Band
order by Music.Band.Name
for xml path('mb:band'), root('mb:bands'), type
Listing 12 To export the Band data from the database
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.