Access SQLXML Technology from .NET Apps

Access SQLXML Technology from .NET Apps

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.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes