Working with XML from SQL Server 2005

First, Introduction

One of the major changes in Microsoft SQL Server 2005 is the inclusion of XML data types. This data type is the first class type, just like INT or VARCHAR, and SQL Server 2005 allows you to query and process this data type in-place using a series of XML-specific functions. It also supports the collection of XML schemas in the storage database, enabling database based schema validation. In addition, SQL Server 2005 greatly expands the XML portfolio (SELECT ...). FOR XML statements, extends the OpenXML () XML decomposition function, and provides a new nodes () function for XML data types for more lightweight decomposition.

Since this new XML feature is enhanced for the database server, it would not be surprising to also enhance the SqlClient data provider in Microsoft Ado.net 2.0. Changes have also been made to the Ado.net DataSet to support the DataColumn of type XML, and the "integration point" between System.Data and System.Xml has been widened. In this article, I'll explore the use of SQL Server-based XML data types on clients.

SQL Server 2005 can produce two types of XML output. Statement SELECT * from AUTHORS FOR XML AUTO produces an XML stream, rather than a row set of one row. The output type does not change compared to the output type in SQL Server 2000. Because of the constraints in the Query Analyzer tool, the XML stream output appears as a row set of rows in SQL Server Query Analyzer. You can differentiate this stream from the "normal" column by its specific unique identifier name "xml_f52e2b61-18a1-11d1-b105-000805f49916b". This name is actually an indicator of the underlying TDS (this is a tabular data flow, SQL Server network format) parser in which the column should flow to the client rather than send as a normal rowset. There is a special method Sqlcommand.executexmlreader used to retrieve this particular stream on the client. In SQL Server 2005, SELECT ... For XML statements are enhanced in many ways. Here are only a few: