Chapter 7: XML Integration

Overview

XML (eXtensible Markup Language) has emerged as one of the most important Internet technologies. XML’s flexible text-based structure enables it to be used for an incredibly wide array of network tasks, including data/document transfer, web page rendering, and even as a transport for interapplication remote Procedure Calls (RPC) via SOAP (Simple Object Access Protocol). XML has truly become the lingua franca of computer languages.

Microsoft first added support for XML to SQL Server 2000, starting with support for the FOR XML clause as part of the SELECT statement and the OpenXML function. As XML continued to grow rapidly in enterprise acceptance and usage, Microsoft quickly provided additional functionality by producing a series of web releases. SQL for XML 1.0 added support for UpdateGrams, Templates, and BulkLoad to the base SQL Server 2000 release. The next two web releases, SQLXML 2.0 and SQLXML 3.0, further enhanced the SQL Server 2000 product by adding support for XML Views and SOAP in addition to several other new capabilities. While SQL Server 2000’s support for XML provided a great starting point for integrating hierarchical XML documents with SQL Server’s relational data, it had some limitations. Once the XML data was stored in a SQL Server database using either the Text or Image data type, there was little that you could do with it. SQL Server 2000 was unable to natively query the hierarchical data that made up the XML document without using complex T-SQL or client-side code.

SQL Server 2005 builds on this starting point by adding support for many new XML features. At a high level, SQL Server 2005 provides a new level of unified storage for XML and relational data. SQL Server 2005 adds a new XML data type that provides support for both native XML queries as well as strong data typing by associating the XML data type to an XSD (Extensible Schema Definition). In addition, it provides bidirectional mapping between relational data and XML data. The XML support is well integrated into the SQL Server 2005 relational database engine, as it provides support for triggers on XML, replication of XML data, and bulk load of XML data, as well as enhanced support for data access via SOAP and many other enhancements. In this chapter you’ll get an introduction to the most important new XML features provided by SQL Server 2005.