SQLXML - XML enabling the Data Layer

So far, so good, we learned about the available features in SQL Server, we saw how we can access them from a .NET application, but what can we do with all this? Now let’s explore how useful these SQLXML classes are really are, compared to ADO.NET for example. We will study different data access and conversion strategies for the data access layer of an XML-centric application. You can find a data access class based on the findings as part of the sample application on the web site.

We will work through a number of examples that all will take an XML document with order data and store the information in the document in the database. They will also query the database for the order data and return the results in an XML document. Each example will employ a different combination of techniques to access SQL Server and transform the results into XML. We will explore the pros and cons of the different approach with respect to performance, flexibility, extensibility and re-usability.

First let’s see what tools we have in our XML toolbox by now. To read and write XML formatted data we have:

• The XmlDocument class, which is a little clunky to use if we are manually creating an XML document, but it allows random navigation and XPath queries against the data in the document. The big caveat of using an XmlDocument object is that it always holds the complete document in memory. This may not be acceptable if an application has to handle multi-megabyte documents on a regular basis.
• The XmlTextReader/-Writer classes. These classes do not require keeping an entire XML document in memory to read or write it, but they do not offer any navigation or query capabilities. Both are light-weight, but forward-only for high performance and small memory footprints.
• The XmlSerializer and serialization classes, which offer similar performance to the XmlTextReader/-Writer classes, but also require that all data is in memory. We can access the data as an object hierarchy rather than a DOM after we deserialized an XML document.
• The DataSet class allows reading XML data into a relational table structure. We can also run queries against a DataSet or its alter ego, the synced XmlDataDocument. Like the XmlDocument and serialization classes the DataSet also keeps all data in memory.

To read and write XML from and to SQL Server we have:

• SQL Server’s built-in XML features: FOR XML queries and inserts and updates using OPENXML executed from either the .NET Framework’s native SQL Server client library or the SQLXML OLE DB data provider.
• XPath queries against annotated schemas executed through SQLXML.
• Updategrams and Diffgrams to perform insert, update and delete operations from SQLXML
• Templates to combine multiple FOR XML or XPath queries for more complex XML structures. We are not going to examine this approach in more detail since it merely combines the characteristics of executing SQL statements and XPath queries.
• Plain SQL to fill a DataSet with the results from a SqlCommand. We can save the DataSet as XML or an XmlDataDocument.

Turning data into XML

The first round of comparisons will evaluate several different approaches to query data from SQL Server and return the results as XML. We have a wide range of choices to obtain SQL Server data as XML, beginning with straight SQL queries and conversion by hand, over SQL Server’s built-in SQL extensions to directly retrieve results in an XML format to executing XPath queries against an annotated mapping schema with SQLXML.

The Traditional Method – Manual Conversion

Before SQL Server 2000, SQLXML and the .NET Framework, there was exactly one solution to convert data from a SQL Server database to an XML format:  issuing a SQL query and creating an XML document by looping through the returned recordset, Granted, ADO 2.x introduced some capability to persist an ADO Recordset object to XML, but you had to stick with the rather flat, table-like structure ADO generated for you; you could not customize the XML format.

We can still try this approach today, by executing a SQL statement over a SqlCommand object into a DataReader as outlined in the fragment below.

public static void WriteAllOderData( SqlConnection connection,

  string rootName, XmlTextWriter writer )

{

  SqlCommand cmd = new SqlCommand("SELECT * from Orders", connection );

  SqlDataReader reader = cmd.ExecuteReader();  

  writer.WriteStartElement( rootName ); // make sure we have a root

  while( reader.Read() )

  {

    writer.WriteStartElement( "Order" );

    writer.WriteElementString( "OrderID", reader.GetValue(0).ToString() );

    writer.WriteElementString( "CustomerID", reader.GetValue(1).ToString() );

// write more XML by calling the appropriate WriteXXX method

}

While this approach may render the highest performance and allow for the highest level of customization of the generated XML format, it bears a huge development and maintenance liability. We have to tightly couple the data retrieval code and the XML transformation code and we have to write many, many lines of code for each XML type we introduce into our system. The more complex our XML format becomes the more complex become our data access classes as this approach relies on custom code to query and write each XML type.