Working with SQL Server 2005 XML Data

XML data isn't only sourced from documents, especially now that SQL Server 2005, which not only increases its general support for XML but also supports XML data as a native data type, allows you to do the following:

• Store XML in "typed" form, where a schema is registered with SQL Server and the XML must conform to this schema.

• Store XML in "untyped" form, where no schema is required and the XML just has to be well formed.

• Select the value from an xml column in SQL statements and stored procedures, just like any other column type.

• Query the XML in the column directly, using XPath and XQuery languages, to find and return specific values.

• Modify the XML in a column by adding or removing nodes or by changing the values of existing nodes.

• Bind XML to other SQL Server data types, allowing queries to access values in stored procedure variables and other non-xml columns.

• Access and return sets of node values, which resemble a single-column rowset.

• Create indexes on the xml columns to vastly improve performance when querying and updating the data.

There are two types of XML columns in SQL Server 2005: untyped and typed. Untyped columns have no schema, and data is stored in character format, complete with elements and attributes. Untyped columns are not validated, although they are checked for well-formed data. A typed column has a schema and therefore has validation applied when data is added or modified.

Creating XML Columns

Since the XML type is a standard type in SQL Server 2005, you simply use xml as a data type.

CREATE TABLE MyTable(MyIDColumn int, MyXmlColumn xml)

For a typed column, you must specify the schema when the table is created.

CREATE TABLE MyTable(MyIDColumn int, MyXmlColumn xml(MySchema))

The advantage of typed columns is that the data within them is converted to native types (int, char, etc.), allowing not only validation as data is inserted and modified but also querying within the XML.

Creating Schemas

Before you can create a typed column, you need to create a schema to define the structure of the XML that is allowable for the column. Schemas are stored within a schema collection, which can hold one or more schemas. To use a schema to define an xml data column, you must first create and register a schema collection that includes the schema with SQL Server using the CREATE XML SCHEMA COLLECTION statement. This statement takes a name that will be used to identify the schema collection after registration and a string-type parameter that contains or references the schema or schemas to be registered in that collection. In the simplest case, the code looks like this:

CREATE XML SCHEMA COLLECTION MySchema AS '<xsd:schema xmlns="...">    ... schema content ... </xsd:schema>'

After registration, the schema is identified by the name you specify (in this example, MyNewSchema). An alternative way to register a schema is to assign it to a variable and then use the variable in the CREATE XML SCHEMA COLLECTION statement. This approach is also useful when uploading schemas from the client because the schema itself can be sent as a parameter.

DECLARE @myschema nvarchar(1000) SET @myschema = N'<xsd:schema xmlns="...">   ... schema content ... </xsd:schema>' CREATE XML SCHEMA COLLECTION MyNewSchema @myschema

You can register more than one schema in a collection and have them all referenced with a single name by simply placing them in the string value you pass to the CREATE XML SCHEMA COLLECTION statement.

CREATE XML SCHEMA COLLECTION MyNewSchema '<xsd:schema xmlns="...">   ... schema content ... </xsd:schema> <xsd:schema xmlns="...">   ... schema content ... </xsd:schema> <xsd:schema xmlns="...">   ... schema content ... </xsd:schema>'

Within SQL Server Management Studio, you can apply a schema to an XML column in the table design, as seen in Figure 7.15.

Figure 7.15. Specifying the schema for an XML column

Inserting Data into XML Columns

Inserting data is, as you'd expect, simply typing the XML into the INSERT statement.

INSERT INTO MyTable(MyIDColumn, MyXmlColumn) VALUES(1, '<cars>... etc ...</cars>')

You can also cast the data as you insert it.

INSERT INTO MyTable(MyIDColumn, MyXmlColumn) VALUES(1, CAST('<cars>... etc ...</cars>' AS xml)

Casting is also useful for enforcing the schema.

INSERT INTO MyTable(MyIDColumn, MyXmlColumn) VALUES(1, CAST('<cars>... etc ...</cars>' AS xml(MySchema))

Accessing XML Columns from ADO.NET

For any particular data type to be of real use in applications, the client-side data access technology you use must be able to read, manipulate, and write instances of that data type. This applies to the new xml data type supported by SQL Server 2005. You would expect to be able to work with it in ADO.NET just like you do with any other built-in data types. So it's no surprise to find that version 2.0 of ADO.NET supports the xml data type as a native type.

Support for the xml data type is implemented by several extensions to the ADO.NET classes, including the following :

• A new value named Xml in the SqlDbType enumerationIndicates that a column, a variable, or a parameter is an xml type.

• A new class named SqlXml in the System.Data.SqlTypes namespaceRepresents an xml type instance and exposes a method to create an XmlReader over the data it contains. To use this, you must reference or import the System.Data.SqlTypes namespace into your project.

• A new method named GetSqlXml on the SqlDataReaderReturns a SqlXml instance for a specified column within a row. The column can be specified only by using its zero-based index within the row.

• A new property on the DataAdapterControls whether data is returned from any xml-typed columns in the database as a String or as a SqlXml instance. The property name is UseProviderSpecific-Type, and the value defaults to false, so you must set it to true before loading the data in order to access an xml column value as a SqlXml type.

These additions are used when accessing data in an xml column of SQL Server 2005 through a DataSet and a DataReader. For example, consider Listing 7.18, where a simple SELECT is performed over a table containing an XML column (Instructions). The column can be accessed like any other column, but untyped access like this returns the column data as a string.

Compare this with Listing 7.19, where the column is not accessed directly but instead the GetSqlXml method is used to return the column as a SqlXml type. The SqlXml type has a CreateReader method which returns an XmlReader, which can be used to navigate over the XML.

Listing 7.18. Streaming an XML Column

StringBuilder bldr = new StringBuilder(); using (SqlConnection conn = new   SqlConnection(ConfigurationManager.ConnectionStrings[   "AdventureWorks"].ConnectionString)) {   conn.Open();   SqlCommand cmd = new SqlCommand(     @"select @BL:<@bl>n<@$p>     from Production.ProductModel       WHERE Instructions IS NOT NULL", conn);   SqlDataReader rdr = cmd.ExecuteReader();   while (rdr.Read())     bldr.Append(rdr["Instructions"].ToString() + "<br />");   rdr.Close(); }

If you are dealing with DataSets or DataTables, you can load the data as normal, but you must set the ReturnProviderSpecificTypes property to true, as seen in Listing 7.20, before loading the data, if you wish XML columns to be strongly typed.

This example loads the data twice, first with weak typing and then with strong typing. The output of this is:

Column type=System.String Column type=System.Data.SqlTypes.SqlXml

The use of ReturnProviderSpecificTypes is an important consideration if you are building data layers that return XML columns. There is, of course, nothing stopping you from returning the XML as a string and then loading this into an XmlDocument using the LoadXml method, but it's an extra step you don't need to do with strongly typed data.

Listing 7.19. Streaming a Strongly Typed XML Column

SqlXml xml; XmlReader xmlRdr; StringBuilder bldr = new StringBuilder(); using (SqlConnection conn = new   SqlConnection(ConfigurationManager.ConnectionStrings[   "AdventureWorks"].ConnectionString)) {   conn.Open();   SqlCommand cmd = new SqlCommand(     @"select @BL:<@bl>n<@$p>     from Production.ProductModel       WHERE Instructions IS NOT NULL", conn);   SqlDataReader rdr = cmd.ExecuteReader();   while (rdr.Read())   {     xml = rdr.GetSqlXml(3);     xmlRdr = xml.CreateReader();     while (xmlRdr.Read())     {       switch (xmlRdr.NodeType)       {         case XmlNodeType.Element:           bldr.Append("Element: " + xmlRdr.Name);           break;         case XmlNodeType.Text:           bldr.Append("Text: " + xmlRdr.Value);           break;       }     }     bldr.Append("<br /><br />");   }   rdr.Close(); }

Listing 7.20. Loading Data into a DataTable

SqlXml xml; StringBuilder bldr = new StringBuilder(); using (SqlConnection conn = new   SqlConnection(ConfigurationManager.ConnectionStrings[   "AdventureWorks"].ConnectionString)) {   SqlDataAdapter da = new SqlDataAdapter(     @"select @BL:<@bl>n<@$p>     from Production.ProductModel       WHERE Instructions IS NOT NULL", conn);   DataTable tbl = new DataTable();   da.Fill(tbl);   bldr.Append("Column type=" +     tbl.Rows[0]["Instructions"].GetType().ToString() + "<br />");   da.ReturnProviderSpecificTypes = true ;   tbl = new DataTable();   da.Fill(tbl);   bldr.Append("Column type=" +     tbl.Rows[0]["Instructions"].GetType().ToString()); }