Migrating from SQLXML 3.0 to the .NET Framework

Migrating from SQLXML 3.0 to the .NET Framework

In the late 1990s, as XML began to gain prominence as a useful way to expose data over the Web, developers focused on creating XML dynamically from a relational database and sending it to the client. Microsoft shipped a technology to enable this called SQLXML. This technology for exposing SQL Server as XML has been integrated into SQL Server 2000 so that any SQL query can append the keywords FOR XML to receive the result as a stream of XML.

SQLXML, now in version 3.0, supports a range of features that allow precise control over the format of the XML that is returned, updates to the data using updategrams, templating (where the data is inserted into an XML template), and more. SQLXML 3.0 is still included in SQL Server "Yukon" and can be used in the same way as with SQL Server 2000.

SQLXML is a native COM-related technology with some managed wrapper classes that can be used from managed (.NET) code, but when considering how this is integrated into the .NET Framework, many of the original requirements have changed.

The main advances and requirements of the .NET SQLXML implementation are listed on the next page.

• The use of XQuery as the primary query language provides a complete XML query technology that is able to sort, perform joins, and do type checking.

• A shared mapping technology between XML views and ObjectSpaces maps relational data from SQL Server to the middle tier, depending on whether objects or XML are required by the application. This provides additional functionality and is more flexible than the annotated XML schemas used in SQLXML.

• A programmatic API in ADO.NET called the XmlAdapter provides update capabilities for SQL Server that are easier to use than updategrams in SQLXML. When combined with the XPathDocument2 class, this provides a complete XML API for queries and updates to SQL Server.

• An equivalent implementation in the .NET Framework of SQLXML technologies within the new System.Data.SqlXml namespace contains classes that offer many of the features of SQLXML but use managed code. Specifically these are:

  • The XmlBulkload class, which allows XML documents to be quickly and easily loaded into SQL Server

  • The DBObject class, which provides cursor-like read/write access to a database using XPath expressions to specify the path and exposes the data through a DataReader

  • The XmlRowsetAdapter, which converts XML into relational data and loads it into a DataSet

Although some of the classes are a direct replacement for parts of SQLXML, this is about integrating SQLXML into the .NET Framework and at the same time further extending the XML data access technology to provide a richer and more flexible application framework. These classes are not 100% backward or forward compatible. Figure provides a comparative picture between SQLXML and the features introduced in System.Xml version 2.0.

This section of the chapter looks at the XmlBulkLoad, DBObject, and XmlRowsetAdapter classes, and how they can be used. We'll then briefly overview how you can access XML over HTTP using the new data source controls included in ASP.NET and bind other controls to them to display XML data.

The goals for the XmlBulkLoad, DBObject, and XmlRowsetAdapter classes are to provide:

• The best possible performance, exceeding that of other XML parsing techniques

• Ease of use, avoiding the need to instantiate a parser and parse the XML in code

• Integration with the existing classes and features in the System.Xml namespaces

• A programming model that is similar to the existing SQLXML features

• A consistent programming model for both middle-tier and "Yukon" CLR-hosted code

The XmlBulkLoad Class

One of the most popular features in SQLXML was the ability to load data quickly and easily from an XML document into SQL Server. To offer the same feature in managed code, the new System.Data.SqlXml namespace includes a class named XmlBulkLoad. The XmlBulkLoad class depends on XML views that, as we have seen in this chapter, describe the relationship between an XML document and the schema of the relational database.

The XmlBulkLoad Class Interface

The XmlBulkLoad class has a relatively simple interface. As shown in Figure through 7.12, there are a single constructor, eight properties, and two methods (one of which has various overrides). The properties of the XmlBulkLoad class specify the mapping and the rules to use when performing the bulk load, the buffer and transaction sizes, and the path to the error log. The most important method of the XmlBulkLoad class is Execute, which pushes the data into the database. There is also a method for specifying a nondefault buffer size.

Using the XmlBulkLoad Class

As an example of using the XmlBulkLoad feature, the code in Listing 7.11 creates a new connection to a database and a new instance of the XmlBulkLoad class. Then it creates an instance of an XmlMapping object from the mapping file nwind.msd and assigns this to the Mapping property of the XmlBulkLoad object (in this example the default values are used for all the other properties).

Using the XmlBulkLoad Class

' get connection string
Dim sConnectString As String = "your-connection-string"

' create an XmlBulkLoad instance
Dim oLoad As New XmlBulkLoad()

' create a MappingSchema instance for the XSD mapping schema and
' assign it to the Mapping property - must use full physical path
oLoad.Mapping = New MappingSchema("c:\mappings\nwind.msd")

' create the connection and open it
Dim oConn As New SqlConnection(sConnectString)
oConn.Open()
...

The data to load into the database is in the XML file named bulkload .xml, and the Execute method of the XmlBulkLoad object is called specifying this file and the database connection (see Listing 7.12). After this completes, a new SqlCommand object fetches the data from the table and displays it in an ASP.NET DataGrid control located within the <body> section of the page; then it deletes the new rows to allow the page to be executed again without incurring primary key violations.

Executing The Load Process

...
' specify the source XML document path and execute the bulk load
oLoad.Execute(sMappingPath & "bulkload.xml", oConn)

' extract the values from the table to check that it worked
Dim oCmd As New SqlCommand("SELECT TOP 10 CustomerID, CompanyName, " _
                         & "City, Country FROM Customers", oConn)
Dim oReader As SqlDataReader = oCmd.ExecuteReader()

' display data in a DataGrid elsewhere on the page
Grid1.DataSource = oReader
Grid1.DataBind()
oReader.Close()

' delete the rows just added so page can run again
oCmd.CommandText = "DELETE FROM Customers WHERE CustomerID LIKE 'AA%'"
oCmd.ExecuteNonQuery()

oConn.Close() ' remember to close the connection

The Mapping File and XML Data

The mapping file used in this example, nwind.msd, contains the mappings between the XML schema of the source document and the schema of the database. XML view mapping files were discussed in detail earlier in this chapter. A portion of the MSD mapping document we used earlier is repeated in Listing 7.13 for reference in this XmlBulkload example. This shows that the identical XML view can be used for either the XmlAdapter or the XmlBulkload class.

The MSD Mapping Document

<m:MappingSchema
     xmlns:m="http://schemas.microsoft.com/data/2002/09/28/mapping">
 <m:DataSources>
  <m:DataSource Name="XML" Direction="Target" Type="Xml">
   <m:Schema Location="nwind.xsd" />
  </m:DataSource>
  <m:DataSource Name="Northwind" Direction="Source"
                Type="SQL Server">
   <m:Schema Location="nwind.rsd" />
   <m:Variable Name="Customers" Select="Customers" />
  </m:DataSource>
 </m:DataSources>
 <m:Mappings>
  <m:Map SourceVariable="Customers" TargetSelect="/Customer">
   <m:FieldMap SourceField="CompanyName" TargetField="@name" />
   <m:FieldMap SourceField="CustomerID" TargetField="@CustomerID" />
   <m:FieldMap SourceField="Country" TargetField="@Country" />
   <m:FieldMap SourceField="City" TargetField="@City" />
  </m:Map>
 </m:Mappings>
</m:MappingSchema>

The other two files for the three-part mapping are specified in the relevant <m:DataSource> sections: nwind.xsd is the XML schema that describes the XML document, and nwind.rsd is the relational schema for the tables in the Northwind database. The nwind.msd file provides the mapping between these two schemas so that the XmlBulkLoad class knows how to translate the XML file into the correct format for the database tables.

The XML data file, named bulkload.xml, contains the new data that will be loaded into the database and conforms to this XML view. The content of this file is shown in Listing 7.14.

The XML Data File for the Bulk Load

<?xml version="1.0" ?>
<Customers>
  <Customer name="New Customer 1" CustomerID="AAAA1"
                  Country="England" City="London" />
  <Customer name="New Customer 2" CustomerID="AAAA2"
                  Country="Australia" City="Perth" />
  <Customer name="New Customer 3" CustomerID="AAAA3"
                  Country="USA" City="Chicago" />
  <Customer name="New Customer 4" CustomerID="AAAA4"
                  Country="Poland" City="Warsaw" />
  <Customer name="New Customer 5" CustomerID="AAAA5"
                  Country="Japan" City="Tokyo" />
</Customers>

The screenshot in Figure shows the result of executing the code. You can see the five new rows that are inserted by the Execute method of the XmlBulkLoad object.

The DBObject Class

The DBObject class provides cursor-like access into SQL Server, selecting a single row and then a single column within that row using XPath-like expressions. The value of the column can then be exposed through a DataReader and updated through the DBObject.

The DBObject Path Syntax

To select a row and column, the DBObject class exposes a property named Path. The value of this property is set to a String that contains the XPath-like expression that specifies the required row and column. The general syntax is:

"dbobject/table-name[row-specifier]/@column-name"

The table-name is the name of the table in the database. It cannot include any spaces or [ or / characters. The column-name is the name of the column within the table. It cannot include any space characters. The row-specifier selects the row within the table. The format is:

@column-name=$value

The column-name is the name of a column within the table and $value is the value in the target row. The common scenario is to use a primary key column. A value is passed into the XPath to replace the $value placeholder using an XmlQueryArgumentList instance populated with the parameter value. Multiple specifiers can be included to select a row based on the values in more than one column by concatenating them with AND, for example:

@columnA-name=$value1 AND @columnB-name=$value2

As usual, if a string value contains a single quote, the string must be delimited with double quotes, or the single quotes within the value must be escaped. Likewise, if a string value contains a double quote, the string must be delimited with single quotes, or the double quotes within the value must be escaped.

Here's an example Path value that selects the Address column from the row in the Customers table that has the value passed in by an XmlQuery ArgumentList for the CustomerID column:

"dbobject/Customers[@CustomerID=$ThisCustID]/@Address"

The DBObject Class Interface

The DBObject interface is remarkably simple, containing a single constructor, a single property, and two methods (see Figure through 7.15). Each method provides four overrides.

The XmlQueryArgumentList Class Interface

In the description of the DBObject class, you saw how an XmlQueryArgument List instance is used to pass values into the XPath expression assigned to the Path property of the DBObject. The main use of the XmlQuery ArgumentList class is to pass arguments into an XQuery process or an XSLT stylesheet. The commonly used methods it exposes are shown in Figure.

Using the DBObject Class

As an example of the DBObject at work, Listing 7.15 shows code that extracts the value of the CompanyName column from the Customers table in the sample Northwind database for the row where the CompanyID primary key value is ANTON. The code creates and opens a connection to the database, then creates a new instance of the DBObject class and specifies the XPath-like expression that selects the row and column.

Using the DBObject Class

' get connection string
Dim sConnectString As String = "your-connection-string"

' create connection and open it
Dim oConn As New SqlConnection(sConnectString)
oConn.Open()
 
' create DBObject instance and set Path property
Dim oDBObject As New DBObject()
oDBObject.Path _
    = "dbobject/Customers[@CustomerID=$CustomerID]/@CompanyName"
...

The Path value assigned to the DBObject instance contains a parameter named CustomerID (prefixed with the $ character), so an XmlQuery ArgumentList is required to pass that parameter to the DBObject at runtime. Listing 7.16 shows how a new XmlQueryArgumentList is created, and a parameter named CustomerID (with the value ANTON and an empty string for the namespace) is added to it. Then the code calls the Retrieve method to get back a SqlDataReader instance pointing to the value in that column. To display the value, the SqlDataReader is bound to an ASP.NET DataGrid control elsewhere on the page.

Retrieving a Column Value

...
' create XmlQueryArgumentList and add parameter
Dim oArgs As New XmlQueryArgumentList()
oArgs.AddParam("CustomerID", "", "ANTON")

' retrieve the value from the database
Dim oReader As SqlDataReader = oDBObject.Retrieve(oArgs, oConn)

' display data in a DataGrid elsewhere on the page
Grid1.DataSource = oReader
Grid1.DataBind()

' remember to close the connection
oConn.Close()

The screenshot in Figure shows the result.

Updating a Column Value with a DBObject

The code from the previous example in Listing 7.16 can easily be modified to demonstrate how the DBObject can be used to push a value into the column of a database table row. Much of the code is the same as before (see Listing 7.17), but this time it reads a value from a text file on disk as a Stream and inserts this into the specified column before reading it back and displaying the value in the page.

The highlighted code in Listing 7.17 shows the FileStream being created from the text file. This, along with an XmlQueryArgumentList containing the CustomerID parameter and the database connection, is passed to the Update method of the DBObject.

Updating a Column Value with a DBObject

' create and open connection as before
...

' create DBObject instance and set Path property
Dim oDBObject As New DBObject()
oDBObject.Path _
    = "dbobject/Customers[@CustomerID=$CustomerID]/@CompanyName"

' create XmlQueryArgumentList and add parameter
Dim oArgs As New XmlQueryArgumentList()
oArgs.AddParam("CustomerID", "", "ALFKI")

' open the data to use from a file as a Stream
Dim oFileInfo As New FileInfo("c:\data\DBObjectNewData.txt")
Dim oStream As FileStream = oFileInfo.OpenRead()

oDBObject.Update(oStream, oArgs, oConn)

' close file stream
oStream.Close()

' code here to retrieve the column value using the same technique
' as in the previous example, and display it in a DataGrid

oConn.Close()  ' remember to close the connection

The code uses exactly the same techniques as the previous example to extract the value again to prove that it worked. You can edit the text file and run this page again to see that it is updated. Note that the file must be in Unicode format; you can select this format when you edit the file in Windows Notepad. The screenshot in Figure shows the result when you enter the text "This is the new company name" into the file DBObjectNewData.txt.

The XmlRowsetAdapter Class

The XmlBulkLoad class loads data into SQL Server through a normal Connection object. But there are times when you want to read complex XML data into an object that can persist it when disconnected but exposes the data in a traditional relational format.

The obvious choice is a DataSet, and you can load XML into a DataSet using its own native methods, such as ReadXml and ReadXmlSchema. However, these methods are not optimally efficient and usually cannot cope with XML that does not follow the specific format required. Instead, a new class called XmlRowsetAdapter has been added in version 2.0 of the Framework. This class is designed to provide a bridge between XML documents in almost any format, including those containing complex hierarchical data, and the DataSet.

In effect, the XmlRowsetAdapter provides a kind of "bulk load" function for use with a DataSet, taking advantage of the new three-part mapping technology. It can "shred" complex XML into multiple tables in a DataSet, in much the same way as the XmlBulkload object does with SQL Server.

The XmlRowsetAdapter Class Interface

The interface of the XmlRowsetAdapter is refreshingly simple. It exposes just one constructor, one property, and two methods (though there are three overloads for one of the methods). All of these interface members are listed in Figure through 7.19.

Using the XmlRowsetAdapter Class

To demonstrate the XmlRowsetAdapter in action, the example in Listing 7.18 uses the same nwind.xsd three-part mapping between XML and the Northwind database. This is used to create the database structure in the DataSet. The code then loads into the DataSet the same XML document used in the XmlBulkLoad example earlier in this chapter.

So, we start by creating a new empty DataSet and a new instance of the XmlRowsetAdapter class. The mapping file named nwind.msd is used to create a new MappingSchema instance, and this is assigned to the Mapping property of the XmlRowsetAdapter.

Using an XmlRowsetAdapter to Load a Table in a DataSet

' create empty DataSet and new XmlRowsetAdapter
Dim oDS As New Dataset()
Dim oXRA As New XmlRowsetAdapter()

' set Mapping of XmlRowsetAdapter to three-part mapping file
oXRA.Mapping = New MappingSchema("c:\mapping\nwind.msd")
...

The FillSchema method of the XmlRowsetAdapter is called next (see Listing 7.19). It uses the relational schema (nwind.rsd) that is referenced in the mapping file to create the database structure within the DataSet. Then the Fill method is called to load the data from the XML file named bulkload.xml into the DataSet. Afterward, the contents of the DataSet are displayed in two ASP.NET DataGrid controls located in the <body> section of the page.

Filling the Schema and Loading the Data

...
' load schema for XML into DataSet
' adapter knows where to find schema from mapping information
oXRA.FillSchema(oDS)

' load XML data into DataSet
oXRA.Fill(oDS, "c:\data\bulkload.xml")

' display list of tables in DataSet in first DataGrid
Grid1.DataSource = oDS.Tables
Grid1.DataBind()

' display data loaded into Customers table in second DataGrid
Grid2.DataSource = oDS
Grid2.DataMember = "[northwind].[dbo].[Customers]"
Grid2.DataBind()

The screenshot in Figure on the next page confirms that both the database structure and the XML data were loaded into the DataSet. The first DataGrid is bound to the Tables collection of the DataSet, and you can see that it contains the same set of tables as found in the Northwind database. Notice that they have fully qualified names, which include the database name and owner, as well as the "local" table name.

This also illustrates why the second DataGrid is bound to the DataMember named [northwind].[dbo].[Customers], rather than just Customers. But it obviously works because you can see the five rows that were inserted into the table from the XML data file.