Bring Relational DBs and XML Together

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 fortheir data and applications. But much of the data they want to describe in XMLis stored in relational databases already. Should the company translate allthat data into XML and store it in a separate location? The answer is "no." Thecompany would lose the many benefits - such as transactions and referentialintegrity - 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, andI'll walk you through the essentials of using it so you can start putting it towork in your own relational database.

SQLXML vs. DataSet: The Race isOn

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

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

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

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

Next, add two buttons and two labels to the form. Press F7to switch to the code-behind view of the page and add these Importsdirectives 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 WebForm1class to handle the click events for the buttons (see Figure 1).

Private Sub Button1_Click( _

    ByVal sender AsSystem.Object, _

    ByVal e As System.EventArgs)Handles Button1.Click

    Dim startTime As DateTime = Now

    Dim cn As New SqlConnection( _

     "server=localhost;" & _

     "database=northwind;" & _

     "integratedsecurity=sspi")

    Dim da As New SqlDataAdapter( _

     "select * fromproducts", cn)

    Dim ds As NewDataSet()

    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 AsSystem.Object, _

    ByVal e AsSystem.EventArgs) Handles Button2.Click

     Dim startTime As DateTime = Now

    Dim cmd As NewSqlXmlCommand( _

     "Provider=SQLOLEDB;" & _

     "Server=(local);" & _

     "database=Northwind;" & _

     "IntegratedSecurity=SSPI")

    cmd.RootTag ="Products"

    cmd.CommandType =SqlXmlCommandType.Sql

    cmd.CommandText = _

     "select * fromproducts for xml auto, elements"

    Dim i As Int32

    For i = 1 To 1000

        Dim xmlFileStreamAs 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 usingSQLXML and the DataSet class. This example performs 1,000 iterations ofselecting data from the database. The code for the Button1_Click event convertsthe data to XML using SQLXML. The code for Button2_Click performs the sameoperation using DataSet. At the end of both procedures, the total time toperform the conversion is output.

Before you run this application, there's some setup workyou 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 dothis. It is a best practice to use integrated security when accessing SQLServer. By default, however, all .NET Web sites run as the ASP.NET user, andthis user does not have permission to access SQL Server using integratedsecurity. The correct approach is to configure SQL Server to grant theappropriate permissions to the ASP.NET user. On development machines, however,it is common to add the ASP.NET user to the "Administrators" group. You alsocould modify the connection string to use standard rather than integratedsecurity.

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

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

Use the XmlDocument Class

If you want to work with your data as XML, you probablywill want to load the data into an XML DOM object. .NET provides this functionalityin the form of the XmlDocument class. This class supports DOM Levels 1and 2. If you are unfamiliar with the DOM, think of it as an API to an XMLdocument. For example, say you have an invoice as XML and you want to tally theprices of the line items. How do you go about this? You could writestring-parsing functions that would let you sift through the XML data as textand extract the prices, but that would mean users would have to write their ownparsers. XmlDocument does this for you. It is a generic parser that letsyou extract any piece of information from any XML document. XmlDocumentalso lets you modify an existing document, such as adding additional line itemsto 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;" & _

   "IntegratedSecurity=SSPI")

cmd.RootTag = "Products"

cmd.CommandType = SqlXmlCommandType.Sql

cmd.CommandText = _

 "select * fromproducts 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 populatedusing SQLXML.

First, the XmlDocument object is populated usingthe Load method. Once the XmlDocument object is populated, youcan use XPath to search and manipulate the XML. Think of XPath as being likeSQL for XML. The XPath expression "/Products/Product/@UnitPrice"evaluates to "retrieve each UnitPrice attribute, for a Productelement, that is a subelement of Products". XPath also contains severalbuilt-in functions such as sum, count, and string length. Instead of loopingthrough each product price, for example, you could use "sum(/Products/Product/@UnitPrice)"and "count(/Products/Product/@UnitPrice)" to retrieve theinformation needed to compute an average.

One disadvantage of the XmlDocument object is allthe data must be held in memory while you work with it. If you can accomplishyour action in a single pass through the data, you can get better performancefrom 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 thenodes in an XML document. Every time you call Read, you are accessingthe next element. The code then simply scans through the entire XML document,picking out the Product elements and accessing their UnitPriceattribute. XmlReader is never slower than XmlDocument. When youcall XmlDocument's Load method, it is simply using an XmlReaderinternally 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 WebServices

People want to expose a lot of data to the world. Forexample, I would like everyone to know the prices of my products. I couldcreate a Web site to provide this information (and this is an acceptablesolution if the consumer is another person), but I really would like to exposethe information so another application could consume it (for instance, aninventory application that can place orders). I could, then, simply expose mySQL Server directly to the Internet, but this is fraught with peril. Rather, Iwould 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 SQLXML3.0, even that isn't necessary; instead, I can expose my stored proceduresdirectly as Web services.

This requires a little coordination with Internet InformationServer (IIS). First, you must set up a virtual directory. To begin, navigate toStart, select AllPrograms, then select SQLXML 3.0, and finally selectConfigure IIS Support. When the IIS VirtualDirectory Management for SQLXML 3.0 tool opens, expand your computer,right-click on Default Web Site, select New, then select VirtualDirectory. In the Virtual Directory Name field, enter wsNorthwind. Forthe Local Path field, click on the Browsebutton. Expand My Computer and select Local Disk (C:).Click on Make New Folder and name thefolder wsNorthwind. Click on OK.

Now that you've created the virtual directory, you can mapin the stored procedures you want to expose. First, select the Security tab and enter a valid SQL Server usernameand password. Select the Data Source tab. For the SQLServer field, leave the default as "(local)." Uncheck the Use default databasefor current login box and enter Northwind for the Database field. On the Settings tab, check the Allow Post checkbox. Selectthe 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 toexpose 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 Namefield, 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 Saveand click on OK twice to close the dialogboxes.

Now you are ready to start testing the Web service. OpenInternet Explorer and navigate to http://localhost/wsNorthwind/Procedures?wsdl.You should see the WSDL (Web Services Description Language) document (seeFigure 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 storedprocedure through the Web service interface. Begin by opening Visual Studio.NET. From here, you can open the Web site you created earlier or simply createa new Visual Basic ASP.NET Web application. Add a button and a DataGridto the page. Select the Project menu command, thenselect Add Web Reference. In the Address field, enterhttp://localhost/wsNorthwind/Procedures?wsdl and press the Enter key. This isthe URL of the WSDL for the stored procedure that is exposed as Web servicemethods. The Add Web Reference window should appear (see Figure 6).

Figure 6. The Add Web Reference window lets you create a reference toyour 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 aspart of your project. In the designer, double-click on the button you added tobe taken to the code-behind page. Then add this code to the button's clickevent:

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 retrievethe data as a DataSet or as a moregeneric 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 doingsomething more generic, such as performing a transformation on the data, youmight prefer to work with it as an XmlElement.

Transform XML With XSLT

XSLT is a powerful XML technology that allows you toperform a transformation on an XmlDocument. For example, you couldrender XML as HTML for browsers, or you could render the same XML as WML forwireless 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 serviceinterface that returns an XmlElement, you can apply this stylesheeteasily 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.CreateNavigatorDim xslt As New Xsl.XslTransform()xslt.Load(MapPath("XFormProducts.xslt")) Dim sw As New StringWriter()xslt.Transform(xpathNav, Nothing, sw) PlaceHolder1.Controls.Add( _ NewLiteralControl(sw.ToString()))   The previous code calls the Web service as before, but nowthe 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 SqlMessageobjects.   When performing a transformation with a stylesheet, the XslTransformclass can iterate over any XPathNavigator object. In this case, thenavigator is created to iterate over the XML returned from the Web service.When the Transform method is called, the transformation's HTML resultsare placed into a StringWriter, which essentially is a string buffer inmemory. From there, the HTML can be poured into a placeholder on the page andthe results rendered.   XML provides a robust and widely adopted mechanism fordescribing data. XML is used to store application configuration information andobject state, and it is used for object serialization across a network. XML canbe sent through sockets, message queues, and even e-mail. XML also is criticalfor data exchange between application layers, applications, languages, andplatforms. SQLXML 3.0 proves an effective toolbox for extracting data from SQLServer and getting that data into an XML format both easily and efficiently.   The sample code in thisarticle is available for download.   Scott Swigart is living proof that being a geek at age12 pays off. He is a senior principal at 3 Leaf Solutions, where he spends thebulk of his time providing training and consulting services for early adoptersof Microsoft technologies. Scott started working with .NET as soon as theprogress bar on the installer would make it all the way to the right. Inaddition to working with early adopters, Scott is actively involved in variousprojects for Microsoft.   Real-WorldScenarios for SQLXML At this point, you have walked through creating andmanipulating XML by using SQLXML. I'll share with you some of the real-worldscenarios where I have seen SQLXML put to use.   Web Services: A database administrator is pushedfrequently to open up access to a database so individuals in the organizationcan build client applications to extract, analyze, and otherwise work with thatdata. The administrator would like to be able to expose the data but not worryabout the client programming languages and technologies. By exposing the dataas a Web service, the administrator is exposing the data in a format anydeveloper should be able to consume. The administrator also has granular controlover exactly what data is exposed because they limit the scope to storedprocedures, specifically only the stored procedures they map to Web services.This also gives the administrator a mechanism for exposing information to theInternet without putting SQL Server outside the firewall.   N-Tier Architectures: Another client I worked withused XML as the format for all data passed between application layers. Theydeveloped a data-access layer of COM objects that returned an XML document to abusiness rules layer and ultimately to the user interface. In their initialapproach, they were performing the conversion to XML manually in the code. Byswitching to SQLXML, their performance increased and they reduced the amount ofcode in their data-access layer.   XML Reports: In a third solution, a client neededto generate a large number of static XML reports on a daily basis and simplydrop them into a directory where other applications could access them. A .NETapplication using SQLXML extracted relational data from the database and outputhierarchical XML documents using a combination of XmlTextReaders andsubqueries. In this case, due to the complexity of the documents beinggenerated 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 aboutthis article to [email protected] include the article title and author.