Exploit Yukon's XML Data Type
Take advantage of SQL Server 2005’s new native XML data type to add XML columns to a table, populate and index the columns, and understand basic XQuery syntax.
by Roger Jennings
April 4, 2005
Technology Toolbox: XML, SQL Server 2005 or SQL Server Express beta 2 (February CTP or later), Visual Studio 2005 (February CTP or later) or Visual Basic Express beta 2, XPath 2.0, XQuery 1.0
Combining hierarchical XML content and relational columns in database tables is akin to mixing oil and waterthe two data structures are unnatural partners. The original approach was to store XML documents as character large objects (CLOBs), which requires adding columns containing extracted atomic values or full-text searches to locate specific document instances, nodes, or values. Shredding documents into a set of relational columns is another alternative, but involves complex hierarchical-to-relational mapping. All major players in the relational database management market now have strategies for implementing Part 14 of the ANSI SQL 2003 standard. Part 14, "XML-Related Specifications (SQL/XML)," defines a modified XML Infoset data type that has sets of operators, publishing functions, and rules for mapping SQL to XML components but doesn’t support association with an XML schema. The next ANSI standard, commonly called ANSI SQL 200n, will be based on the XQuery 1.0/XPath 2.0 data model (see Additional Resources) and will enable validating XML data type instances against optional schemas.
SQL Server 2005’s new native XML data type goes beyond the SQL 2003 standard by enabling XQuery 1.0 and XPath 2.0 expressions to retrieve, restructure, and update multiple strongly typed XML document instances. The XML data type is a variation on the varbinary(max) data type that stores UTF-16-encoded XML documents or fragments as condensed binary large objects (BLOBs). The XML column’s structure enables the SQL Server 2005 query optimizer to generate query plans that minimize execution time (see Additional Resources). In this article, I’ll show you how to add XML columns to a table, populate the columns with document instances generated by an SQLXML FOR XML AUTO, TYPE query, and add XmlSchemaCollections. I’ll also explain basic XQuery syntax and provide sample expressions to return selected document instances, nodes, or atomic values and update XML column content.
You’ll need the February 2005 Community Technical Preview (CTP) or later of SQL Server 2005or SQL Server Express (SQLX) and SQL Server Express Manager (XM)with the Northwind sample database installed to execute some of this article’s T-SQL batch statements and XQuery expressions (see Additional Resources). A compatible version of Visual Studio 2005 or Visual Basic Express is required to explore the code of the XQuery.sln Windows form demonstration project that I’ll describe shortly.
Back to top