Bring Relational DBs and XML Together

Use SQLXML to expose your relational data as XML.

asp:cover story

LANGUAGES: VB .NET

TECHNOLOGIES: SQLXML | XML | XSL | Web Services

 

Bring Relational DBs and XML Together

Use SQLXML to expose your relational data as XML.

 

By Scott Swigart

 

Many companies are turning to XML as the lingua franca for their data and applications. But much of the data they want to describe in XML is stored in relational databases already. Should the company translate all that data into XML and store it in a separate location? The answer is "no." The company would lose the many benefits - such as transactions and referential integrity - of having a relational database manage your data.

 

Microsoft's response to this dilemma is called SQLXML. This article describes the pieces of this free extension to SQL Server, and I'll walk you through the essentials of using it so you can start putting it to work in your own relational database.

 

SQLXML vs. DataSet: The Race is On

If you've worked with .NET, you know DataSet has built-in XML support. If your goal is to convert data to XML as fast as possible, however, DataSet is not the best choice.

 

Consider this scenario. Suppose you need an application to query SQL Server daily and dump out a set of XML files that something else can consume (some C++ UNIX application, perhaps). You could accomplish this by using either DataSet or SQLXML managed classes.

 

SQL Server 2000 shipped with XML support in the box (Integrate SQL Server 2000 With XML). The XML specifications were (and are), however, in a state of flux, so SQLXML was released with the understanding that periodic updates would be required to keep the product in sync with changing XML specifications. SQLXML, currently in version 3.0, is that update. SQLXML 3.0 also contains several extremely useful features. You have a set of managed classes to access XML from .NET applications, and it also lets you expose stored procedures directly as Web services. (I'll describe this in more detail later.)

 

If you want to retrieve data from SQL Server and convert it to XML, SQLXML is going to offer much better performance - in particular, raw speed - than other options such as loading it into a DataSet and calling WriteXml. Before you can get started with SQLXML 3.0, you need to download and install it from http://msdn.microsoft.com/sqlxml (at press time, the latest version is SQLXML 3.0 Service Pack 1). Next, open Visual Studio .NET and create a new Visual Basic Web application. Select the Project menu command, then select Add Reference. Highlight the Microsoft.Data.SqlXml component, click on Select, and click on OK. At this point, you have configured the development environment so you can begin working with the SQLXML managed classes.

 

Next, add two buttons and two labels to the form. Press F7 to switch to the code-behind view of the page and add these Imports directives to the top of the file:

 

Imports System.Data.SqlClient

Imports Microsoft.Data.SqlXml

Imports System.IO

Imports System.Xml

Imports System.Xml.Xpath

Imports System.Xml.Xsl

 

Next, add the code to the WebForm1 class to handle the click events for the buttons (see Figure 1).

 

Private Sub Button1_Click( _

    ByVal sender As System.Object, _

    ByVal e As System.EventArgs) Handles Button1.Click

 

    Dim startTime As DateTime = Now

 

    Dim cn As New SqlConnection( _

     "server=localhost;" & _

     "database=northwind;" & _

     "integrated security=sspi")

 

    Dim da As New SqlDataAdapter( _

     "select * from products", cn)

    Dim ds As New DataSet()

 

    Dim i As Int32

    For i = 1 To 1000

        ds.Clear()

        da.Fill(ds)

        Dim sw As Stream =

         File.OpenWrite(Server.MapPath("/out.xml"))

        ds.WriteXml(sw)

        sw.Close()

    Next

 

    Dim endTime As DateTime = Now

 

    Label1.Text = FormatNumber( _

     endTime.Subtract(startTime).TotalSeconds, 2)

End Sub

 

Private Sub Button2_Click( _

    ByVal sender As System.Object, _

    ByVal e As System.EventArgs) Handles Button2.Click

 

     Dim startTime As DateTime = Now

    Dim cmd As New SqlXmlCommand( _

     "Provider=SQLOLEDB;" & _

     "Server=(local);" & _

     "database=Northwind;" & _

     "Integrated Security=SSPI")

 

    cmd.RootTag = "Products"

    cmd.CommandType = SqlXmlCommandType.Sql

    cmd.CommandText = _

     "select * from products for xml auto, elements"

 

    Dim i As Int32

    For i = 1 To 1000

        Dim xmlFileStream As Stream = _

        File.OpenWrite(Server.MapPath("/out.xml"))

        cmd.ExecuteToStream(xmlFileStream)

         xmlFileStream.Close()

    Next

 

    Dim endTime As DateTime = Now

    Label2.Text = FormatNumber( _

    endTime.Subtract(startTime).TotalSeconds, 2)

End Sub

Figure 1. Here's an example of generating XML using SQLXML and the DataSet class. This example performs 1,000 iterations of selecting data from the database. The code for the Button1_Click event converts the data to XML using SQLXML. The code for Button2_Click performs the same operation using DataSet. At the end of both procedures, the total time to perform the conversion is output.

 

Before you run this application, there's some setup work you need to do. Because the page will access the database and retrieve data, you need to configure your server so the ASP.NET Web site has permission to do this. It is a best practice to use integrated security when accessing SQL Server. By default, however, all .NET Web sites run as the ASP.NET user, and this user does not have permission to access SQL Server using integrated security. The correct approach is to configure SQL Server to grant the appropriate permissions to the ASP.NET user. On development machines, however, it is common to add the ASP.NET user to the "Administrators" group. You also could modify the connection string to use standard rather than integrated security.

 

Once security is configured properly, you may build the application and view the page within the browser. When you click on each button, you'll see the performance difference between using DataSet and SqlXmlCommand to generate XML (see Figure 2).

 


Figure 2. SqlXmlCommand generates 1,000 files in about half the time of DataSet.

 

Use the XmlDocument Class

If you want to work with your data as XML, you probably will want to load the data into an XML DOM object. .NET provides this functionality in the form of the XmlDocument class. This class supports DOM Levels 1 and 2. If you are unfamiliar with the DOM, think of it as an API to an XML document. For example, say you have an invoice as XML and you want to tally the prices of the line items. How do you go about this? You could write string-parsing functions that would let you sift through the XML data as text and extract the prices, but that would mean users would have to write their own parsers. XmlDocument does this for you. It is a generic parser that lets you extract any piece of information from any XML document. XmlDocument also lets you modify an existing document, such as adding additional line items to an invoice. Figure 3 shows how data can be loaded into XmlDocument. Note: In this particular case, the average price of a product is calculated.

 

Dim cmd As New SqlXmlCommand( _

   "Provider=SQLOLEDB;" & _

   "Server=(local);" & _

   "database=Northwind;" & _

   "Integrated Security=SSPI")

 

 

cmd.RootTag = "Products"

cmd.CommandType = SqlXmlCommandType.Sql

cmd.CommandText = _

 "select * from products as Product for xml auto"

Dim xmlDoc As New XmlDocument()

xmlDoc.Load(cmd.ExecuteStream())

 

Dim prices As XmlNodeList = xmlDoc.SelectNodes( _

 "/Products/Product/@UnitPrice")

Dim price As XmlNode

Dim total As Double

Dim count As Int32

 

For Each price In prices

   total += price.Value

   count += 1

Next

 

Label3.Text = FormatNumber(total / count)

Figure 3. Here, an XmlDocument object is populated using SQLXML.

 

First, the XmlDocument object is populated using the Load method. Once the XmlDocument object is populated, you can use XPath to search and manipulate the XML. Think of XPath as being like SQL for XML. The XPath expression "/Products/Product/@UnitPrice" evaluates to "retrieve each UnitPrice attribute, for a Product element, that is a subelement of Products". XPath also contains several built-in functions such as sum, count, and string length. Instead of looping through each product price, for example, you could use "sum(/Products/Product/@UnitPrice)" and "count(/Products/Product/@UnitPrice)" to retrieve the information needed to compute an average.

 

One disadvantage of the XmlDocument object is all the data must be held in memory while you work with it. If you can accomplish your action in a single pass through the data, you can get better performance from XmlReader:

 

Dim xr As XmlReader = cmd.ExecuteXmlReader()

Dim total As Double

Dim count As Int32

While xr.Read()

   If xr.Name = "Product" Then

      total += xr.GetAttribute("UnitPrice")

      count += 1

   End If

End While

Label4.Text = FormatNumber(total / count)

 

The XmlReader class lets you iterate through the nodes in an XML document. Every time you call Read, you are accessing the next element. The code then simply scans through the entire XML document, picking out the Product elements and accessing their UnitPrice attribute. XmlReader is never slower than XmlDocument. When you call XmlDocument's Load method, it is simply using an XmlReader internally to parse the stream and identify the elements. As shown in Figure 4, this code is functionally identical to using XmlDocument.

 


Figure 4. XmlTextReader produces the same results as XmlDocument.

 

Show Off Your Data With Web Services

People want to expose a lot of data to the world. For example, I would like everyone to know the prices of my products. I could create a Web site to provide this information (and this is an acceptable solution if the consumer is another person), but I really would like to expose the information so another application could consume it (for instance, an inventory application that can place orders). I could, then, simply expose my SQL Server directly to the Internet, but this is fraught with peril. Rather, I would like to develop a front end that exposes the data as XML. Using ASP.NET, I could build a Web service wrapper that does exactly that, but with SQLXML 3.0, even that isn't necessary; instead, I can expose my stored procedures directly as Web services.

 

This requires a little coordination with Internet Information Server (IIS). First, you must set up a virtual directory. To begin, navigate to Start, select All Programs, then select SQLXML 3.0, and finally select Configure IIS Support. When the IIS Virtual Directory Management for SQLXML 3.0 tool opens, expand your computer, right-click on Default Web Site, select New, then select Virtual Directory. In the Virtual Directory Name field, enter wsNorthwind. For the Local Path field, click on the Browse button. Expand My Computer and select Local Disk (C:). Click on Make New Folder and name the folder wsNorthwind. Click on OK.

 

Now that you've created the virtual directory, you can map in the stored procedures you want to expose. First, select the Security tab and enter a valid SQL Server username and password. Select the Data Source tab. For the SQL Server field, leave the default as "(local)." Uncheck the Use default database for current login box and enter Northwind for the Database field. On the Settings tab, check the Allow Post checkbox. Select the Virtual Names tab. Enter Procedures for the name, select soap for the type, and enter c:\wsNorthwind for the Path. Click on Save.

 

At this point, you have set up the virtual directory to expose stored procedures using SOAP. The last step is to map the individual procedures. You can expose a stored procedure that returns an XmlElement, a DataSet, or both.

 

Click on Configure. In the Method Name field, enter SalesByCategoryDS. In the SP/Template field, click on the ellipsis (...) button, select SalesByCategory, and click on OK. Select the Single DataSet radio button and click on Save.

 

Next, expose the same stored procedure to return an XmlElement. In the Method Name field, enter SalesByCategoryXML. In the SP/Template field, click on the ellipsis (...) button, select SalesByCategory, and click on OK. Finally, click on Save and click on OK twice to close the dialog boxes.

 

Now you are ready to start testing the Web service. Open Internet Explorer and navigate to http://localhost/wsNorthwind/Procedures?wsdl. You should see the WSDL (Web Services Description Language) document (see Figure 5).

 


Figure 5. The WSDL document describes your Web service's interface - what methods you are exposing, what arguments they take, and what they return. This information is essential for anyone who wants to consume your Web service.

 

Consume the Web Service

Now you can build a client that accesses the stored procedure through the Web service interface. Begin by opening Visual Studio .NET. From here, you can open the Web site you created earlier or simply create a new Visual Basic ASP.NET Web application. Add a button and a DataGrid to the page. Select the Project menu command, then select Add Web Reference. In the Address field, enter http://localhost/wsNorthwind/Procedures?wsdl and press the Enter key. This is the URL of the WSDL for the stored procedure that is exposed as Web service methods. The Add Web Reference window should appear (see Figure 6).

 


Figure 6. The Add Web Reference window lets you create a reference to your SQLXML Web Service and a proxy class as part of your project.

 

Next, click on Add Reference. This adds a reference to the SQLXML Web service and creates a proxy class as part of your project. In the designer, double-click on the button you added to be taken to the code-behind page. Then add this code to the button's click event:

 

Dim ws As New localhost.Procedures()

Dim returnVal As Integer

DataGrid1.DataSource = ws.SalesByCategoryDS( _

   "Beverages", "1997", returnVal)

DataGrid1.DataBind()

 

One advantage of SQLXML Web services is you can retrieve the data as a DataSet or as a more generic XmlElement. In the previous example, the data is returned as a DataSet, which makes it easy for an ASP.NET Web site to consume. If you are doing something more generic, such as performing a transformation on the data, you might prefer to work with it as an XmlElement.

 

Transform XML With XSLT

XSLT is a powerful XML technology that allows you to perform a transformation on an XmlDocument. For example, you could render XML as HTML for browsers, or you could render the same XML as WML for wireless devices.

 

This stylesheet code outputs <> elements in an XML source document as HTML table rows:

 

   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

   

   

      

         

         

      

   

 

If the stored procedure is called through a Web service interface that returns an XmlElement, you can apply this stylesheet easily to transform the data using this code:

 

Dim ws As New localhost.Procedures()

Dim results() As Object = _

 ws.SalesByCategoryXML("Beverages", "1997")

Dim xmlResults As XmlElement = results(0)

Dim xpathNav As XPathNavigator = _

 xmlResults.CreateNavigator

Dim xslt As New Xsl.XslTransform()

xslt.Load(MapPath("XFormProducts.xslt"))

Dim sw As New StringWriter()

xslt.Transform(xpathNav, Nothing, sw)

PlaceHolder1.Controls.Add( _

 New LiteralControl(sw.ToString()))

 

The previous code calls the Web service as before, but now the service returns an array of objects. These objects include the data as an XmlElement, and it could also include a return value as well as error messages as SqlMessage objects.

 

When performing a transformation with a stylesheet, the XslTransform class can iterate over any XPathNavigator object. In this case, the navigator is created to iterate over the XML returned from the Web service. When the Transform method is called, the transformation's HTML results are placed into a StringWriter, which essentially is a string buffer in memory. From there, the HTML can be poured into a placeholder on the page and the results rendered.

 

XML provides a robust and widely adopted mechanism for describing data. XML is used to store application configuration information and object state, and it is used for object serialization across a network. XML can be sent through sockets, message queues, and even e-mail. XML also is critical for data exchange between application layers, applications, languages, and platforms. SQLXML 3.0 proves an effective toolbox for extracting data from SQL Server and getting that data into an XML format both easily and efficiently.

 

The sample code in this article is available for download.

 

Scott Swigart is living proof that being a geek at age 12 pays off. He is a senior principal at 3 Leaf Solutions, where he spends the bulk of his time providing training and consulting services for early adopters of Microsoft technologies. Scott started working with .NET as soon as the progress bar on the installer would make it all the way to the right. In addition to working with early adopters, Scott is actively involved in various projects for Microsoft.

 

Real-World Scenarios for SQLXML

At this point, you have walked through creating and manipulating XML by using SQLXML. I'll share with you some of the real-world scenarios where I have seen SQLXML put to use.

 

Web Services: A database administrator is pushed frequently to open up access to a database so individuals in the organization can build client applications to extract, analyze, and otherwise work with that data. The administrator would like to be able to expose the data but not worry about the client programming languages and technologies. By exposing the data as a Web service, the administrator is exposing the data in a format any developer should be able to consume. The administrator also has granular control over exactly what data is exposed because they limit the scope to stored procedures, specifically only the stored procedures they map to Web services. This also gives the administrator a mechanism for exposing information to the Internet without putting SQL Server outside the firewall.

 

N-Tier Architectures: Another client I worked with used XML as the format for all data passed between application layers. They developed a data-access layer of COM objects that returned an XML document to a business rules layer and ultimately to the user interface. In their initial approach, they were performing the conversion to XML manually in the code. By switching to SQLXML, their performance increased and they reduced the amount of code in their data-access layer.

 

XML Reports: In a third solution, a client needed to generate a large number of static XML reports on a daily basis and simply drop them into a directory where other applications could access them. A .NET application using SQLXML extracted relational data from the database and output hierarchical XML documents using a combination of XmlTextReaders and subqueries. In this case, due to the complexity of the documents being generated and the great debugging capabilities available in Visual Studio .NET, SQLXML proved to be the perfect tool for the job.

 

Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish