To help you take advantage of the xml data type and XQuery in SQL Server 2005, ADO.NET and the classes in the System.Xml namespace provide you with helpful tools. Here is a list of some changes that have been made to the ADO.NET classes to enable or enhance the SQL Server XML experience.
The System.Data.SqlTypes namespace contains a new class called SqlXml. This is a nullable data type that contains a method called CreateReader, which returns a derived instance of XmlReader that you can use to read the XML data contained in the instance.
The System.Data.SqlDbType enumeration contains a new value called xml that indicates the SqlXml data type (described earlier).
The SqlDataReader class contains a method called GetSqlXml that returns a SqlXml data type instance based on the column index that you pass to this function. The SqlDataReader object can also read the schema information of the xml columns by using the GetSchemaTable method, which has three new columns called XmlSchemaCollectionDatabase, XmlSchemaCollectionOwningSchema, and XmlSchemaCollectionName.
The System.Data.Common.DataAdapter class contains a method called ReturnProvide pecificTypes that is set to true to read in XML data as a SqlXml type. If you use the default value of false, XML data is read into a string field.
Getting Started with the SqlXml Class
You can access instances of the SqlXml class by using the DataReader object's GetSqlXml method. After you have a SqlXml object, you can execute its CreateReader method to retrieve an object that derives from the XmlReader object.
To see how this works, create a Windows application named XmlTest and add a Button control and a DataGrid control to the form. Note that the DataGrid control is being used because it supports hierarchical data, whereas the DataGridView does not. If the DataGrid control is not in your ToolBox, you will need to add it by right-clicking in the ToolBox, clicking Choose Items, and selecting the DataGrid control from the .NET Framework Components tab. On the Button control, change the Text property to Get Vehicles. We are using the DataGrid control instead of the DataGridView control because the DataGrid control can display many tables by assigning a DataSet object to its DataSource property and leaving the DataMember property empty. Add the Northwind database to your project, and add the following App.config file to your project.
Run the script to add MyXmlTable to the Northwind database. The script, titled "SQL: Creating and Populating MyXmlTable," can be found earlier in the chapter.
Next, add a reference to the System.Configuration.dll, System.Data.dll, and System.Xml.dll assemblies. Double-click the button, and add the following code to read the vehicles into the DataSet object.
Visual Basic
Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports System.Configuration Public Class Form1 Private Sub button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles button1.Click Dim ds As New DataSet() Dim nwSetting As ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings("NwString") Using cn As New SqlConnection() cn.ConnectionString = nwSetting.ConnectionString Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "SELECT vehicles FROM MyXmlTable" cn.Open() Using rdr As SqlDataReader = cmd.ExecuteReader() While (rdr.Read()) Dim vehicles As SqlXml = rdr.GetSqlXml(0) If (Not vehicles.IsNull) Then ds.ReadXml(vehicles.CreateReader()) End If End While End Using End Using End Using dataGrid1.DataSource = ds End Sub End Class
C#
using System; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; using System.Configuration; using System.Data.SqlTypes; namespace XmlTest { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); ConnectionStringSettings nwSetting = ConfigurationManager.ConnectionStrings["NwString"]; using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = nwSetting.ConnectionString; using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "SELECT vehicles FROM MyXmlTable"; cn.Open(); using (SqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { SqlXml vehicles = rdr.GetSqlXml(0); if (!vehicles.IsNull) ds.ReadXml(vehicles.CreateReader()); } } } } dataGrid1.DataSource = ds; } } }
When you run this code, a connection is opened to the database and a query is executed to retrieve the vehicles. Using the SqlDataReader object, you can read each row and execute the GetSqlXml method to retrieve an instance of the SqlXml class. As long as the SqlXml object is not null, the XML data is read into the DataSet object by executing the CreateReader method on the SqlXml object, which creates an XmlSqlBinaryReader object. The XmlSqlBinaryReader object is passed into the ReadXml method on the DataSet object. Each SqlXml object that is read into the DataSet object appends to existing XML data. Finally, the DataSet object is assigned to the DataSource property of the DataGrid control. The resulting DataGrid object displays a plus sign indicating that tables are available. If you click the plus sign, you'll see that the DataSet object converted the XML data into DataTable objects for cars, trucks, and repairs (as shown in Figure 15-8).
Figure 15-8: The XML data that was read into the DataSet object was automatically converted to DataTable objects.
Assigning and Retrieving the Schema
Working with typed and untyped xml data is essentially the same on the client by default, because typed xml columns do not automatically transfer their schema to the client. This means that schema validation does not occur until the modified XML data is sent to SQL Server. The problem is that in the previous example, the XML data was loaded into a DataSet object and all of the data types were inferred to be string data types. If you navigate to the repair table and change a code to a nonnumeric value, the new value is accepted. This probably isn't the behavior you want.
You can get the schema from SQL Server, which gives you the opportunity to perform clientside validation. You do this by using the SqlDataReader object's GetSchemaTable method to discover the schema information, and then you can retrieve the schema.
To prepare the SQL Server data for this example, we must add a schema to SQL Server for the vehicles and then alter MyXmlTable to use a typed xml column based on the new schema. Start by executing the following SQL script to create the Schema Collection called VehicleSchema in SQL Server.
This schema is somewhat generic, but it contains an IsDataSet attribute set to indicate that this schema can be loaded into a DataSet object before the data is loaded, to enable type checking on numeric columns (such as the repair cost).
If you retrieve the schema you just added, you will find that the IsDataSet attribute was not stored. Try the following command.
The problem is that the schema was parsed and stored based on what SQL Server needed. The original schema is not stored in the Schema Collection, so you must store it to a file or to a text field in the database. In this example, we want to use the same schema that SQL Server is using, so we will add code to insert the IsDataSet attribute into the schema when it is retrieved from the database.
Now that the schema is loaded into SQL Server, we can modify MyXmlTable to use the schema. Open the table definition, and change the XmlTypeSpecification to use dbo.VehicleSchema, as shown in Figure 15-9.
Figure 15-9: Changing MyXmlTable to use the VehicleSchema
You can add code to discover the schema information and retrieve it. The code that discovers the schema and retrieves it uses the same connection object, which means you must enable the MultipleActiveResultSets (MARS) option in the connection string, as follows.
To discover the schema information and retrieve it, we will add a new method, called GetSchema, which reads the schema from the database and adds the IsDataSet attribute, as shown in the following code.
Visual Basic
Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports System.Configuration Imports System.Xml Imports System.IO Public Class Form1 Private Sub button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles button1.Click Dim ds As New DataSet() Dim nwSetting As ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings("NwString") Using cn As New SqlConnection() cn.ConnectionString = nwSetting.ConnectionString Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "Select vehicles from MyXmlTable" cn.Open() Using rdr As SqlDataReader = cmd.ExecuteReader() GetSchema("vehicles", ds, cn, rdr) While (rdr.Read()) Dim vehicles As SqlXml = rdr.GetSqlXml(0) If (Not vehicles.IsNull) Then ds.ReadXml(vehicles.CreateReader()) End If End While End Using End Using End Using dataGrid1.DataSource = ds End Sub Private Sub GetSchema(ByVal xmlColumn As String, _ ByVal ds As DataSet, ByVal cn As SqlConnection, _ ByVal rdr As SqlDataReader) 'discover and retrieve schema Dim schema As DataTable = rdr.GetSchemaTable() Dim row As DataRow = schema.Select("columnName='" + xmlColumn + "'")(0) Using schemaCmd As SqlCommand = cn.CreateCommand() schemaCmd.CommandText = _ "SELECT xml_schema_namespace(@owner, @name) FOR XML PATH('')" schemaCmd.Parameters.AddWithValue(_ "@owner", row("XmlSchemaCollectionOwningSchema")) schemaCmd.Parameters.AddWithValue(_ "@name", row("XmlSchemaCollectionName")) 'load into XmlDocument to fix IsDataSet attribute Dim doc As New XmlDocument() doc.Load(schemaCmd.ExecuteXmlReader()) Dim mgr As New XmlNamespaceManager(doc.NameTable) mgr.AddNamespace("xsd", "http://www.w3.org/2001/XMLSchema") Dim vehicles As XmlNode = doc.SelectSingleNode(_ "//xsd:element[@name=""" + xmlColumn + """ ]", mgr) Dim dataSetAtt As XmlAttribute = doc.CreateAttribute("msdata", _ "IsDataSet", "urn:schemas-microsoft-com:xml-msdata") dataSetAtt.Value = "true" vehicles.Attributes.Append(dataSetAtt) Dim tempStream As New MemoryStream() doc.Save(tempStream) tempStream.Position = 0 ds.ReadXmlSchema(tempStream) End Using End Sub End Class
C#
using System; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; using System.Configuration; using System.Data.SqlTypes; //using System.Collections; using System.Collections.Generic; using System.Xml; using System.Xml.XPath; using System.IO; namespace XmlTest { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); ConnectionStringSettings nwSetting = ConfigurationManager.ConnectionStrings["NwString"]; using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = nwSetting.ConnectionString; using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "SELECT vehicles FROM MyXmlTable WHERE id=1"; cn.Open(); using (SqlDataReader rdr = cmd.ExecuteReader()) { GetSchema("vehicles", ds, cn, rdr); while (rdr.Read()) { SqlXml vehicles = rdr.GetSqlXml(0); if (!vehicles.IsNull) ds.ReadXml(vehicles.CreateReader()); } } } } dataGrid1.DataSource = ds; } private static void GetSchema(string xmlColumn, DataSet ds, SqlConnection cn, SqlDataReader rdr) { //discover and retrieve schema DataTable schema = rdr.GetSchemaTable(); DataRow row = schema.Select("columnName='" + xmlColumn + "'")[0]; using (SqlCommand schemaCmd = cn.CreateCommand()) { schemaCmd.CommandText = "SELECT xml_schema_namespace(@owner, @name) FOR XML PATH('')"; schemaCmd.Parameters.AddWithValue( "@owner", row["XmlSchemaCollectionOwningSchema"]); schemaCmd.Parameters.AddWithValue( "@name", row["XmlSchemaCollectionName"]); //load into XmlDocument to fix IsDataSet attribute XmlDocument doc = new XmlDocument(); doc.Load(schemaCmd.ExecuteXmlReader()); XmlNamespaceManager mgr = new XmlNamespaceManager(doc.NameTable); mgr.AddNamespace("xsd", "http://www.w3.org/2001/XMLSchema"); XmlNode vehicles = doc.SelectSingleNode( "//xsd:element[@name=\"" + xmlColumn + "\"]", mgr); XmlAttribute dataSetAtt = doc.CreateAttribute("msdata", "IsDataSet", "urn:schemas-microsoft-com:xml-msdata"); dataSetAtt.Value = "true"; vehicles.Attributes.Append(dataSetAtt); MemoryStream tempStream = new MemoryStream(); doc.Save(tempStream); tempStream.Position = 0; ds.ReadXmlSchema(tempStream); } } } }
The new GetSchema method discovers the schema name by calling the GetSchemaTable method and then selecting the metadata row for the column we are interested in (in this case, the vehicles column). This information is plugged into the call to xml_schema_namespace, which retrieves the schema from the database. Instead of loading the schema directly into the DataSet object, we load it into an XmlDocument object and perform a search for the vehicles element, and then we add the IsDataSet attribute to it, along with its required namespace. The DataSet object can read the schema from either a file or a stream, so the XmlDocument object is saved to a temporary MemoryStream object and the DataSet object uses the MemoryStream object to read the schema.
If you run this sample application and click the button to load the vehicles data, the schema is retrieved from the database and then the schema is loaded into the DataSet object. Then the data is loaded and displayed.
With the schema information at the client, navigate to the repair table and try to change one of the cost values to a nonnumeric value. When you leave the cost field, it is validated. Validation will fail, and the value will revert to its original numeric value.
Passing an SqlXml Object Parameter to Update the Database
When it's time to update the database, you can create a parameter that is an SqlXml type and issue an UPDATE statement to the database with this object.
For the following example, add a Button control to the form. Change the Text property to Update Vehicles, and add the following code to its Click event handler.
Visual Basic
Private Sub button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles button2.Click Dim ds As DataSet = CType(dataGrid1.DataSource, DataSet) Dim nwSetting As ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings("NwString") Using cn As New SqlConnection() cn.ConnectionString = nwSetting.ConnectionString Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = _ "UPDATE MyXmlTable SET vehicles = @veh WHERE id=1" Dim ms As New MemoryStream() ds.WriteXml(ms) ms.Position = 0 Dim r As XmlReader = XmlReader.Create(ms) Dim x As New SqlXml(r) cmd.Parameters.AddWithValue("@veh", x) cn.Open() cmd.ExecuteNonQuery() End Using End Using MessageBox.Show("Done.") End Sub
C#
private void button2_Click(object sender, EventArgs e) { DataSet ds = (DataSet)dataGrid1.DataSource; ConnectionStringSettings nwSetting = ConfigurationManager.ConnectionStrings["NwString"]; using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = nwSetting.ConnectionString; using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "UPDATE MyXmlTable SET vehicles = @veh WHERE id=1"; MemoryStream ms = new MemoryStream(); ds.WriteXml(ms); ms.Position = 0; XmlReader r = XmlReader.Create(ms); SqlXml x = new SqlXml(r); cmd.Parameters.AddWithValue("@veh", x); cn.Open(); cmd.ExecuteNonQuery(); } } MessageBox.Show("Done."); }
