XML Queries and Indexing in SQL Server 2005

SQL Server 2005 is the first version of Microsoft's flagship database to truly embrace XML. True, SQL Server 2000 has some bolt-on XML functionality, but in SQL Server 2005 XML is pervasive. In particular, you can use the new XML columns to actually store XML documents as part of a table. In this article, I'll dig into another part of the XML story: indexing those columns for faster searching.

Storing XML in a Table

XML columns allow you to store XML documents or fragments in a SQL Server database (an XML fragment is an XML document without the top-level xml element. When you add an XML column to a table, each row of the table stores an individual XML document or fragment. The individual documents cannot exceed 2GB each. So, for example, you might create a table of document information in a publishing-related database that contained the actual text of documents in XML format as a single column.

To create an XML column, you assign the xml data type to the column, whether you're using the CREATE TABLE statement or creating the table with SQL Server Management Studio. Here's an example:

CREATE TABLE Document (
	DocID int IDENTITY NOT NULL,
	Description nvarchar(50) NOT NULL,
	DocumentStore xml NOT NULL,
 CONSTRAINT PK_Document PRIMARY KEY CLUSTERED 
 (
	DocID ASC
 )
) 

This creates a table with three columns, the third of which can store XML documents. To insert data into an XML columns, you can use string constants. These will be implicitly converted to XML when you perform the insert. For example:

INSERT INTO Document (Description, DocumentStore)
VALUES('Bruce''s poem',
N'

Bruce
The cat/is flat.
')

INSERT INTO Document (Description, DocumentStore)
VALUES('Code of Hammurabi',
N'

Hammurabi
An eye for an eye, a tooth for a tooth.
')

INSERT INTO Document (Description, DocumentStore)
VALUES('Nursery Rhyme',
N'

Mother Hubbard
Jack and Jill/went up the hill.
')

Writing XML Queries

There are two methods to extract data from XML columns: the .value method and the .query method. When you want to extract a single piece of information from an XML column and have it returned as a SQL data type (such as an int or a varchar), use the .value method. Its syntax is as follows:

value(XQuery, SQLType)

Here XQuery is an XQuery expression, and SQLType is the name of the target SQL data type. For example:

SELECT DocumentStore.value('(/Document/@Name)[1]',
 'varchar(50)' )
FROM Document

This gives the results shown in Figure 1.

As an alternative to the .value method you can use the .query method. The .query method is used to run an XQuery expression against an XML column, returning the result as a value of the xml data type. Its syntax is simple:

query('XQuery')

For example:

SELECT DocumentStore.query('(/Document/Text)')
FROM Document

This gives the results shown in Figure 2.