Datasheet

Storing XML Data

In the XML document shown in the previous section, it is fairly obvious how the data might map to a

database table. For instance, you could have a

FoodStuff table containing columns for Category (pos-

sible a foreign key field linking to a separate

Category table), text columns for Name, Size, and

Rating, and a bit type column for IsNasty. In the XML, the root <foodStuffs> element would sim-

ply be used as a placeholder containing the data, and each

<foodStuff> element would represent a row

in the table.

However, XML documents can come in other forms, too. Here’s an example:

<?xml version=”1.0” encoding=”utf-8” ?>

<body>

<h1>Funny bone results in dog days!</h1>

Rumor has it (sources unknown) that a well known

comedy dog double act is due to split any day now.

The latest information to come out of the rumor mill is that

a dispute arose about the location of a buried bone, and that

until it is found the dogs in question are only communicating

via their lawyers.

More news as it happens!

</body>

This is, in fact, a piece of HTML. But it’s a little more than that — it’s actually a fragment of XHTML —

an XML dialect of HTML. It’s also a perfectly legal XML document, but creating a table capable of hold-

ing this information in row form would be practically impossible. Instead, storing this in a database

would mean putting the whole lot in a single column of a row, in text form. SQL Server includes an

xml

datatype for storing this sort of data, or you could just use a text column. When you store data using the

xml datatype, however, there is additional functionality that you can use, such as querying data within

the document using the XQuery language.

You look at this facet of SQL Server later in the book.

Retrieving Data as XML

As well as being able to retrieve XML data stored in xml type columns directly as XML, SQL Server also

makes it possible to retrieve data from any result set in the form of XML data. This involves an addi-

tional

FOR XML clause, which has a number of uses and ways of customizing the format in which XML

data is obtained, but can also be used simply. For example:

SELECT * FROM Product FOR XML AUTO

This query obtains data as a single string as follows:

<Product ProductId=”A5B04B50-9790-11DA-A72B-0800200C9A66”

ProductName=”Thingamajig” ProductCost=”30.0000”

ProductCategoryId=”914FC5A0-9790-11DA-A72B-0800200C9A66”/>

<Product ProductId=”79360880-9790-11DA-A72B-0800200C9A66”

ProductName=”Widget” ProductCost=”54.0000”

ProductCategoryId=”6F237350-9790-11DA-A72B-0800200C9A66”/>

Chapter 1

44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 32