With the release of the SQLXML Managed Classes, which Microsoft shipped with SQLXML in March of this year, developers now can access SQLXML technology (XML views, templates, XPath, XQuery) from .NET applications. This function enables .NET application developers to retrieve data from relational databases as if they were big XML documents. They no longer have to transform the results of a query into XML–SQLXML performs this operation automatically.

Figure 1 illustrates the SQLXML concept.

Figure 1: The Concept Behind SQLXML

Figure 2 shows the basic architecture of the SQLXML Managed Classes.

Figure 2: The Architecture of the SQLXML Managed Classes

As you can see from Figure 2, all the classes are embedded into ADO.NET, so if you know how to use ADO.NET, you dont need to learn anything else to use the SQLXML Managed Classes.

If you have already worked with ADO.NET, youll love the dataset object as well. It is an in-memory representation of a database. Once you get data from a database into this object, you no longer need a connection to the underlying database. You can access the data, modify it, and finally update the database with your changes. Also, with no connection needed, you can pass data through the tiers of your application without any performance and scalability problems.The Class SQLXMLAdapter
The class SqlXMLAdapter provides all the necessary functions for retrieving relational data through XML views and loading it into a dataset object. For example, you can:

• Load data with an XPath expression with the Fill method of the SqlXMLAdapter class
• Work with the data and update the database by calling the Update function

SQLXML analyses the dataset object and calculates all necessary update operations. It then translates these changes into UpdateGrams, which describe changes on a database in an XML format. The database internally translates UpdateGrams into SQL statements (INSERT, UPDATE, DELETE).

The following listing shows how you can load data into a dataset object, edit the data, and update the database with the changes:

using System;using System.Data;using Microsoft.Data.SqlXML;public class SqlXMLAdapterSample{   public static void Main()   {      String strConnectionString =         Provider=SQLOLEDB;Server=(local);         database=Northwind;user id=sa;pwd=;      SqlXmlCommand cmd = new         SqlXmlCommand(strConnectionString);      cmd.CommandType = SqlXmlCommandType.XPath;      cmd.CommandText = /Customer[@CustomerID=ALFKI];      cmd.SchemaPath = Customers.xsd;      DataSet ds = new DataSet();      SqlXMLAdapter adapter = new SqlXMLAdapter(cmd);      adapter.Fill(ds);      // Now you have can edit the data in the dataset      // object      adapter.Update(ds);   }}

In the first line, you create a new instance of the class SqlXMLCommand. This class represents a command, which is executed through SQLXML. Then you set the property CommandType to SqlXMLCommandType.XPath, which tells SQLXML that you are using an XPath statement for the data-retrieving process. In this case, you are using the XPath statement /Customer[@CustomerID=ALFKI]. As a result, you get the customer with the CustomerID ALKFI. Finally, you must tell SQLXML which XML view you are using. You can do this with the property SchemaPath.

Once youve finished that, you can create an instance of the class SqlXMLAdapter and retrieve the requested data with a call of the method Fill. By now you have a normal dataset object, which you already know from ADO.NET. With a call of the method Update, SQLXML creates all the necessary UpdateGrams and executes them against the database.Use Templates
When you use SQLXML, you can place your XPath, XQuery, or SQL statements in templates. Templates also are XML documents that encapsulate database queries. You could say that templates separate your database queries from your program code. When you include hard-coded queries in your programs, templates make the process of maintaining those programs easier–you dont have to change the program code when you change a query.

The following listing shows how to use templates:

using System;using System.IO;using System.Data;using Microsoft.Data.SqlXML;public class TemplateSample{   public static void Main()   {      String strConnectionString =         Provider=SQLOLEDB;Server=(local);         database=Northwind;user id=sa;pwd=;      Stream stream;      SqlXMLCommand cmd = new         SqlXMLCommand(strConnectionString);      cmd.CommandStream = new FileStream(Template.XML,         FileMode.Open, FileAccess.Read);      cmd.CommandType = SqlXMLCommandType.Template;      stream = cmd.ExecuteStream();      StreamReader reader = new StreamReader(stream);      Console.Writeline(reader.ReadToEnd());      Console.ReadLine();   }}

The contents of the template file Template.XML is shown in the following lines:

         SELECT * FROM Customers      WHERE CustomerID=ALFKI      FOR XML AUTO   

Apply XSL Transformations
Another cool feature of SQLXML is that you can apply XSL transformations on an XML result document. This feature is particularly useful for programming data-driven Web sites:

1. Access your database through SQLXML and XML views.
2. Retrieve XML documents as the result.
3. Transform these documents into HTML Web sites.

Follow these three steps and youll create a data-driven Web site without any database programming!

You can also apply XSL transformations in .NET applications by using the SQLXML Managed Classes. The following listing shows you how to apply these transformations:

using System;using System.IO;using System.XML;using System.XML.Xsl;using System.XML.XPath;using Microsoft.Data.SqlXML;public class XslTransformSample{   public static void Main()   {      String strConnectionString =         Provider=SQLOLEDB;Server=(local);         database=Northwind;user id=sa;pwd=;      Stream stream;      SqlXMLCommand cmd = new         SqlXMLCommand(strConnectionString);      cmd.CommandText = SELECT * FROM Customers         FOR XML AUTO;      cmd.RootTag = ROOT;      stream = cmd.ExecuteStream();      // Apply the XSL-Transformation      XMLTextReader reader = new XMLTextReader(stream);      XPathDocument xpath = new XPathDocument(reader,         XMLSpace.Preserve);      XslTransform xslt = new XslTransform();      xslt.Load(Transform.xsl, null);      XMLTextWriter writer = new        XMLTextWriter(output.html,        System.Text.Encoding.UTF8);      xslt.Transform(xpath, null, writer);   }}

The file Transform.xsl contains the following transformation code:

                                                      
            
               
Company Name
               
Contact Name
            
            
         

Retrieve Data from Relational Databases
The basic idea behind SQLXML is enabling developers to retrieve data from relational databases like SQL Server through XML views. This technology also can be accessed in .NET applications through the SQLXML Managed Classes, which fit into the ADO.NET concept as well as the idea of dataset objects.