New features about XML in SQL Server 2008

1 Introduction

Microsoft has introduced XML-related features and Transact-SQL keyword FOR XML and OPENXML in Microsoft SQL Server 2000. This allows developers to write Transact-SQL code to obtain query results in the form of XML streams and to split an XML document into a rowset. SQL Server 2005 significantly expands these XML capabilities, and introduces a local XML data type that supports XSD schema validation, XQuery based operations, and XML indexing. Built on previous versions of XML functionality, SQL Server 2008 has been improved to meet the challenges that customers face when storing and manipulating XML data in a database.

2 Evolution of SQL Server XML functionality

SQL Server's XML capabilities evolve with each version since the SQL Server2000 version. Before we examine the improvements in SQL Server 2008, it may be useful to summarize the evolution of XML functionality through previous versions.

2.1 XML functionality in SQL Server 2000

In SQL Server 2000, Microsoft has launched the Transact-SQL keyword FOR XML and OPENXML. For XML is an extension of the SELECT statement that returns the query results of the XML stream as shown in the following example.

SELECT ProductID, ProductName
　　 FROM Products Product
　　 FOR XML AUTO

This query returns an XML fragment as shown in the following example:

<Product ProductID="1" ProductName="Widget"/>
<Product ProductID="2" ProductName="Sprocket"/>

The OPENXML feature performs the opposite of the FOR XML conditional clause, creating a rowset for an XML document, as shown in the following example:

DECLARE @doc nvarchar(1000)
SET @doc = '<Order OrderID = "1011">
　<Item ProductID="1" Quantity="2"/>
　<Item ProductID="2" Quantity="1"/>
　</Order>'
DECLARE @xmlDoc integer
EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @doc
SELECT * FROM
OPENXML (@xmlDoc, 'Order/Item', 1)
WITH
(OrderID integer '../@OrderID',
ProductID integer,
Quantity integer)
EXEC sp_xml_removedocument @xmlDoc

Note the use of sp_xml_preparedocument and sp_xml_removedocument stored procedures to create a memory display of the node tree of the XML document. This Transact-SQL code returns to the rowset below.

OrderID	ProductID	Quantity
1011	1	2
1011	2	1