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
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
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
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'
') INSERT INTO Document (Description, DocumentStore) VALUES('Code of Hammurabi', N' Bruce The cat/is flat. ') INSERT INTO Document (Description, DocumentStore) VALUES('Nursery Rhyme', N' Hammurabi An eye for an eye, a tooth for a tooth. ') 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
varchar), use the
.value method. Its syntax is as
XQuery is an XQuery expression, and
is the name of the target SQL data type. For example:
SELECT DocumentStore.value('(/Document/@Name)', '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:
SELECT DocumentStore.query('(/Document/Text)') FROM Document
This gives the results shown in Figure 2.