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 <<row>>
elements in an XML source document as HTML table rows:
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output
method="html"/>
<xsl:template
match="row">
<tr>
<td><xsl:value-of
select="ProductName"/></td>
<td><xsl:value-of
select="TotalPurchase"/></td>
</tr>
</xsl:template>
</xsl:stylesheet>
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 mailto:editors@devproconnections.com.
Please include the article title and author.