Content area
The last subject we'd like to cover in this article is that of XML template files. These provide us with several key benefits. First, they allow us to significantly shorten the URL we send through the browser by encapsulating all aspects of the query into an external file. Second, they permit us to hide some of the details of the database structure in an external file, so that they aren't as readily visible to the casual visitor to a Web site utilizing these techniques.
Full text
Applications: Microsoft Internet Information Server 4.0/5.0, Microsoft SQL Server 2000 Operating Systems: Microsoft Windows NT 4.0 / 2000 / XP
Today's Web-enabled business applications are almost universally database-driven. In the past, the most common method for connecting to databases over the Web had been to use Microsoft ActiveX Data Objects (ADO) together with ASP code. This, of course, is still a perfectly viable solution; however, SQL Server 2000 now allows you to connect to and manipulate databases directly over HTTP, facilitated by the powerful data-manipulation capabilities of XML. You can leverage these capabilities in your ASP applications in many ways, some of which we'll show you in this article.
The ground we'll cover
In this article, we'll provide a solid introduction to the topic of communicating with SQL Server 2000 databases through HTTP and IIS. First, we'll talk about how to configure the required virtual directories in Internet Information Server to bridge the gap between Web server and database server. Next, we'll show you some practical examples of how to specify SQL queries directly through a URL in your Web browser. Finally, we'll broaden our discussion to cover XML query templates and how to use an XSL stylesheet to format the results of a database query for display over the Web.
IS meets SOL Server
Before you can access SQL Server databases over HTTP, you need to configure a virtual directory on your Web server. To do this, launch the Configure SQL XML Support in IIS utility, located in the Microsoft SQL Server program group on your Start menu. The resulting application looks very much like the Internet Services Manager you're probably already familiar with; however, the utility only displays SQL Server virtual directories. At this point, there are probably no such directories defined on your server, so expand the tree view in the left pane of the utility, select your Web site, and then choose New I Virtual Directory from the Action menu.
The resulting New Virtual Directory Properties dialog box allows you to configure an access point between your Web server and your database server. At first, the process may look intimidating, but we assure you that it's really quite straightforward. Let's go through the process one step at a time by creating a virtual directory for the Northwind database.
Virtual directory configuration
Before you begin configuring the virtual directory, you'll need a physical directory to point it to. Create a directory in your inetpub\wwwroot folder called Northwind. While you're there, go ahead and create a subdirectory within Northwind, called Template. You'll see the purpose of this folder shortly. Now, let's proceed with establishing the necessary virtual directory settings to connect to the Northwind database.
On the General tab, you'll need to provide a name for the virtual directory and specify the path to the physical folder you just created. Name the virtual directory Northwind and browse to the physical Northwind folder you created. Next, select the Security tab. Ensure that the Always Log On As option button is selected; then enter sa for the User Name and provide your sa password in the Password text box. When you click on the Data Source tab to continue the configuration process, you'll be prompted to confirm the password.
Now, on the Data Source tab, enter or browse to your SQL Server and then choose the Northwind database from the dropdown list. Next, on the Settings tab, go ahead and select all four check boxes. We'll only be using the first two of these for now, but we'll discuss the other options in a future article.
Click on the Virtual Names tab next. Click the New button, then type dbobject in the Virtual Name text box and select dbobject from the Type dropdown list. Click Save. Then click the New button again. This time, enter template in the Virtual Name text box and select template from the Type dropdown list. Next, enter or browse to the physical template subdirectory of the Northwind directory you created at the beginning of the process and then click Save. This process is depicted in Figure A.
The SOL 2000 ISAPI extension
Finally, take a quick look at the Advanced tab. Note that the SQLISAP Location text box points to a file called SQLISAPI.DLL. This ISAPI extension provides the actual mechanism that facilitates direct communication between SQL Server 2000 and IIS. Don't change anything on this tab; just click the OK button to save the virtual directory. Now, just to better understand what this utility has done for us, launch the Internet Services Manager snap-in.
Navigate to your Web site and find the newly created Northwind virtual directory. Select this node and then choose Properties from the Action menu. In the resulting dialog box, click the Configuration button on the Virtual Directory tab. In the resulting Application Configuration dialog box, shown in Figure B, note that the SQLIS-- API.DLL ISAPI extension is associated with the file extension, *. This means that for all files stored in the Northwind virtual directory (and any subdirectories), as well as for all HTTP requests issued against it, the ISAPI extension will process the file or request. It's this extension that makes the direct dialogue between SQL Server and the Web server possible.
Querying SOL data through US
Now that we've configured a virtual directory, we're ready to start accessing the Northwind database through HTTP requests against IIS. The mechanism for doing so is a rich and flexible one, but let's begin with a simple example. Launch your Web browser and enter the following URL into the address bar:
You should see the correct result, Davolio, displayed in your browser window. Clearly, we've actually carried out a successful request directly to our SQL Server through HTTP. Note that, because the space character isn't valid in a well-formed URL, we've used the + instead. As an alternative, you could replace all spaces with their URL-- friendly equivalent, %20. In an ASP application, you can use the HTMLEncode method of the Server object to preprocess any URLs in this way.
Now, let's try something a little different. Enter the following URL into the browser:
What's going on here? Why are all of the names run together? This is because SQL Server returns the data as a continuous stream, without delimiters; it's up to you to split the data stream into its constituent parts, a tricky prospect to say the least.
Let's try another test. So far, we've attempted to return only a single column. Enter the following URL and let's see what happens:
You'll see that this query returns an error, as shown in Figure C. What's this about? Who said anything about XML?
As it turns out, SQL Server 2000 doesn't support streaming (i.e., returning text data directly into the browser window) for multiple-column results. So far, accessing SQL Server over HTTP doesn't look too impressive, eh? Well, hang on, because with the introduction of XML-formatted result sets, things will get a lot more interesting and useful!
XML does the trick!
So if streaming (i.e., non-XML results) is supported only for single columns, how can we get useful multi-column result sets? And are there any situations where we might use streaming effectively? Let's answer the last question first. If you're interested in only a single discreet data point, a streamed result is perfectly acceptable and lacks the overhead of a complete XML document. What's more, the result can actually be text or binary data, and the browser will be smart enough to understand what to do with it. For example, let's suppose you wanted to display a particular employee's photo (a column of SQL image data type). You could do this with the following URL:
Figure D shows the results of this query. You could, for instance, put a URL of this form into the src attribute of an < img> tag in an HTML page on your Web site.
Give me more columns!
But let's face it: Most of the time, you'll be interested in multi-column recordsets, so how do you handle them through US and SQL Server? The answer is XML. By adding a couple of extra pieces of information to the URL, we can handle results of any arbitrary complexity (including those formed with JOIN statements). Take a look at the following URL:
Two differences should stand out. First, note that we've added FOR XML AUTO to the SELECT statement. With SQL Server 2000, the results of a database query may now be returned natively in XML format. Second, we've added a parameter to the URL, root=EmployeeTable. For maximum flexibility, the XML engine in SQL Server 2000 returns an XML fragment, but in order to turn this into a well-formed XML document we've got to have a root node. You can specify any name you like for this node; we chose EmployeeTable. Enter this URL into your Web browser and observe the results, shown in Figure E on the previous page.
Templates, XML and XSL
Clearly, we're making progress, but there are two issues to address. First, our URLs are beginning to get long and cumbersome. Moreover, under Internet Explorer, URLs are limited to 2,083 characters, so we'll get into physical trouble if we keep going on as we are. Many queries, especially those involving multiple tables, can become quite lengthy. The second problem is that our results aren't exactly pretty. Sure, they're probably readable, more or less, but nobody wants raw XML in their Web applications. It would be nice if we could do something about both problems. As it happens, we can!
Easier on the eyes
Let's start by addressing the issue of presenting our results in a more visually appealing way. To do this, we'll need an XSL stylesheet. You've seen previous examples of using XSL with XML from
Active Server Pages, so our examples shouldn't be too intimidating. Listing A presents the code for a sample stylesheet we can use against data from the Northwind database. Save this listing as Employees.xsl in the template subfolder of your Northwind directory.
Before we talk about how the stylesheet works, let's see it in action. To pass your XML result set through the XSL stylesheet, all you need to do is add an xs I attribute to the URL, as shown in this example:
This example also shows just how long our URLs are starting to get! But as you can see from Figure F, this is much better than returning back the raw, unformatted XML data! This is just the sort of results we'd typically expect from an ASP page using ADO to access the data.
Now let's quickly walk through the stylesheet code. We begin with the standard XML and XSL declarations (remember that XSL stylesheets are themselves XML files). Next, we define a transformation template with an
Following this code, you'll notice that we've opened an HTML < table> and displayed a couple of explanatory header rows. This information doesn't come out of our SQL database, so what's important to realize here is that, through XSL, we can add to the data in any way we like. This isn't possible when you return the raw XML directly to the browser.
Next, you'll see an
Using XML templates
The last subject we'd like to cover in this article is that of XML template files. These provide us with several key benefits. First, they allow us to significantly shorten the URL we send through the browser by encapsulating all aspects of the query into an external file. Second, they permit us to hide some of the details of the database structure in an external file, so that they aren't as readily visible to the casual visitor to a Web site utilizing these techniques. Third, they provide for much greater flexibility to call parameterized stored procedures, carry out highly complex multi-table queries, and more. All of these can be accomplished directly from the URL, providing that we don't go over its length limit; however, external XML template files are easier to read. Finally, XML template files are reusable. That is, rather than copying and pasting the same query into many different places (and dealing with the maintenance headache of changing them all in the event of a necessary modification), we have a single, centralized XML file that can be accessed from many different points. And finally, using ASP, we can even modify these XML template files on the fly should we wish to. We won't demonstrate that technique in this article, but it's something you can experiment with on your own.
So how do we create an XML template file? It's really pretty simple. Let's take the same query we executed with our XSL stylesheet above and convert it into an XML template. As you recall, the URL-based query was as follows:
Now examine Listing B, which contains the XML template file equivalent for this query. As you can see, this is much more readable than the URL equivalent. You can feel free to break lines wherever you like and you don't have to escape all of the spaces as the URL form required you to do. You'll also notice that we've specified our XSL stylesheet as an attribute of the root Emp 0oyeeTab le element.
There are several other optional elements you can include within an XML template, which is beyond the scope of this article. For our purposes, all you need to do is put any standard SQL query inside the
Note the addition of the ContentType parameter. This is necessary because when you use an XML template, the SQL ISAPI extension in IIS assumes that you want XML returned to the browser. By specifying a Content Type of "text/html," you'll get exactly the same display results as we did with the much longer URL-based query.
Your Web server just got wise!
As we alluded, there are many other applications of the techniques we've introduced in this article, including invoking stored procedures, utilizing XPath queries, using ASP to rewrite XML template files on an ad hoc basis, and more elaborate XML template files. These are outside our focus today, but experiment with accessing your SQL Server 2000 databases using the ubiquitous HTTP protocol, and remember that this is just the beginning. In a future article, we'll explore some of these topics in more detail with more advanced examples in ASP, XML and XSL.
Copyright Element K Journals Jun 2002 tags and then displays the relevant fields from the Northwind database. The 
