We were listening to a CIO explain how two years ago his company had instituted a mandatory XML policy for the IT staff. Senior management had decided that XML offered too great a competitive advantage for them to pass up using it. The use of XML, he explained, allowed them to quickly change and share their core business structures. The development staff had easily found ways to use XML within their applications. These included importing XML files, typed data sets, transformations, and schemas. However, he did caution that there were still some challenges.

Unfortunately, the database administrators, although they tried, were not having much success. They complained to him that they didn't have the same ease of use and variety of tools that the developers did. With a stern look that seems to be a requirement for his job, he wanted an explanation of how he could solve these types of problems using Visual Studio 2005 and SQL Server 2005. In this article, we will explain, as we did that day, how Visual Studio 2005 and SQL Server 2005 provide a common set of XML features that both developers and database administrators are able to leverage.

XSD and SQL Server 2005
One of the biggest complaints from his database group was the inability to maintain a set of Extensible Schema Definitions (XSD) for their databases. By definition, XSD is a metalanguage that defines an XML-based structure. XSD provides the core definitions of how an XML document is defined. An XSD defines the data types and the element structures that are then used to validate an XML file. XSD provides the syntax and definition in which elements and attributes can be represented within an XML document. It also guarantees that a given XML document will contain a specific format and data type. Using XSD offers a variety of advantages, as shown in Table 1.

When defining a schema, it is considered a structure, and the actual document that is represented by the schema is called a "document instance." Traditionally, developers and database administrators have utilized them in very different ways. The relational model found in an SQL Server database mapped the schema structure to a table and the "document instance" to a record in a table. Developers, however, would map the schema to a class definition and the specific "document instance" to an object.

Importing XSD
XML support within SQL Server has been available since the release of SQL Server 2000. With this release, SQL Server supported the ability to bulk load, parse XML-based documents, and expose database objects through XML-based Web services. Support for this feature was provided by the Web services toolkit, which is commonly known as SQLXML 3.0. This toolkit also enabled SQL Server 2000 to use XML within stored procedures, XML templates, and SQL Server User Defined Fields (UDF). This, explained the CIO, was certainly a good start but didn't solve the fundamental issue he was having around using native XML-based storage, more advanced queries, and reusable XSD structures.

With SQL Server 2005, two key enhancements have been added around the support of XML. SQL now provides a native XML data type that allows the direct storage of XML data within a database. This feature exposes the ability to query, index, and utilize XSD directly. The direct support means that, unlike SQL Server 2000, where a relationship between XSD and XML was maintained through the application, the SQL Server 2005 engine understands how this data integrity should be directly maintained. Essentially, the separate step of importing and processing XML documents is now combined directly into the database engine through the XML data type.

The XML Data Type
The XML data is considered a first-class citizen within SQL Server 2005. It exposes the same full-featured functionality, which is available to all other data types. As a full-fledged data type, this enables the XML-based column to be indexed, and have both row and column constraints defined through an XSD schema definition as shown in Listing 1. Additionally, as an XML-based structure, it can be directly queried using any valid XQuery expression directly embedded into a T-SQL. It is important to understand that flexibility is one of the key benefits of the XML data type. As a native data type, the XML data type enables you to determine whether or not you want to associate an XSD schema with a column. When an SQL column contains an XSD association, it is known as typed XML. A schema is used to type an XML column only after it is imported into the SQL database. Once the schema is imported, it can then be used to create indexes and other metadata type information for the system catalog. Using the CREATE TABLE shown in Listing 2, the Products XSD can be imported into the database.

When the CREATE TABLE is issued, a new table is inserted into the database based on the XSD, which then binds the XML data type to the namespace of this schema. Once the table is created, it can be queried directly from the Enterprise Manager (see Figure 1). All standard types of SQL statements can interact with the typed column. For example, Listing 3 provides an example of an INSERT statement.

In addition to the standard T-SQL statements, data can be retrieved using XQUERY. XQUERY is an XML-based expression language that evaluates an expression and returns a set of values. For database administrators and developers, this provides them with the ability to manipulate XML-based objects on a data tier using similar statements as shown in Figure 2.

Introducing ADO.NET 2.0
The CIO looked convinced that we had addressed his database problems. He then changed his focus to the developers by asking, "What about my developers?" His question really centered on one of the main design goals of ADO 2.0, which was to allow a higher degree of interoperability between relational, XML, and custom objects. Many of these enhancements start with the System.XML namespace within the .NET Framework. This namespace defines a set of XML classes that enable XML support for the application level interfaces.

Essentially, it includes a number of classes that give the user the ability to read, write, manipulate, and transform XML. This namespace is really a core part of ADO.NET and serves as the XML data access API. It enables many of the features that are available to move data between XML and relational data. One of the main examples of this is the DataSet.

SQLXML and the XmlAdapter
Since the release of the initial .NET Framework, the DataSet has always included the ability to both load as XML and serialize its content as XML. The hard part was really the translation that needed to occur between the two methods of representing the data. For example, in order for the XML to load into a DataSet, the associated schema needed to map into the relational DataTables of the DataSet object.

The DataSet object has been enhanced within ADO.NET 2.0 to offer several new options. These include the ability to support multiple in-line schemas, multiple namespace support, and loading/serialization of XML data directly from the DataTable object. In addition, as a way of providing better access to XML-based data, the System.Xml.XmlAdapter class has been added. This class is used to bridge the data between a data source and the representation of the data. It can also be used to query and load XML from an XML View into an XPathDocument object. For example, Listing 4 loads an XPathDocument, creates the XpathEditableNavigator, and appends new elements to an XML file using the XML Writer class.

An XML View is defined as the mapping of a data source. This data source may not always be in XML format. However, once mapped, it can be represented as an XML document. For example, this may include the mapping of a relational table from a database. An XML View is used to create a virtual representation of the data. The data isn't actually converted into an XML format, but shaped and transformed into a structure as if it were XML. The use of XML Views over relational databases facilitates the generation of XML documents into a hierarchical data structure.

XML Views allow relational tables in SQL Server to be mapped to an XML schema through mapping files. These files have been part of the SQLXML 3.0 toolkit. However, like SQL Server 2005, these features have been rolled into ADO.NET as part of the System.Data.SqlXml namespace. This namespace provides a set of XML Views for SQL Server data that can query the data using both the XQuery and the XQueryProcessor class and the Fill method of the XMLAdapter, and manipulate the data using the XPathDocument, XPathEditor, XPathNavigator and XPathChangeNavigator classes. Listing 5 shows an XML schema being associated with an XML document and then validating that the document conforms to the schema content through the use of the validate method on the XPathEditableNavigator.

The changes are written to SQL Server by calling the Update method of the XMLAdapter. This relies on the XML View to write the SQL Statements by executing the defined mappings. The advantage of this approach is that you can treat your SQL Server data no differently than other XML data stores and can take advantage of the full fidelity of XML when making changes.

Of course, XML Views simply provide the mapping of the data to and from SQL Server. Even when not using SQL Server as the data source, you can still take advantage of the changes to the XPathDocument and its related classes to easily query, navigate, and edit XML loaded from other sources. For example, using a new XmlFactory class, a related set of XmlReader, XmlWriter, and XPathNavigator classes can be created for an XML document. These classes enable the user to read and write .NET types to and from XML documents.

As the conversation started to wind down, the CIO nodded his head and smiled. He felt that he had made the right decision several years back to embrace and utilize XML within his enterprise. Also, after our conversation he confided to us that his database and applications arsenal that gave the company a competitive advantage had just been fortified. He felt that any company looking to strengthen and fortify their own arsenal should seriously consider how these changes will help. His final comment as we walked out the door was that he finally got the full solution.

• Figure 1
• Figure 2
• Source Code
• Table 1