Search
Web Braintrove
Site Navigation
Home
Products
Tags
Levels
Dates
Authors
5 MOST RECENT
Perform a Case-Insensitive String Comparison without Using Translate()
Force a Field Value to a Boolean Using Rules
Quickly Crop to a Shadow
Sum Repeating Time Values
Preview and Open Forms after Upgrading to Internet Explorer 8
5 MOST POPULAR
Pass Query String Parameters to an ASP.NET Xml Control
Access a Method in a Master Page with Code-Behind
Prevent Namespace Prefixes from Being Copied to the Output
Create Groups in a Drop-Down List Box
Change the Default Action of the Power Button on the Windows Vista Start Menu
5 MOST FORGOTTEN
Install Windows 7 Beta onto a Virtual Machine
Convert Between Time and Decimal
Configure Lab Color Mode Defaults for Braintrove Articles
Permanently Change the Default Styles for New Word Documents
Use the Eyedropper Tool to Pick a Color From Anywhere
Books
Beginning XSLT 2.0: From Novice to Professional
Learning Web Design : A Beginner's Guide to HTML, Graphics, and Beyond
Beginning ASP.NET 2.0 in C# 2005: From Novice to Professional
Microsoft Visual C++ .NET Step by Step
Learn HTML 4 In a Weekend
Magazines
SQL Server Magazine
Asp.netpro
Asp.net Professional
Inside Visual Basic
.net: The Internet Magazine
DVDs
Beginner's .NET XML Web Services 2004
Programming Visual Basic .NET and ADO.NET with SQL Server and Access
Beginner's ASP.NET in VB.NET 2003
Beginner's ASP.NET in C# 2003
Visually Learn Visual Basic .NET in 12 Hours
Microsoft MVP
PopularMost Viewed
This article has been viewed 2867 times.

Communicate with a SQL Database During an XSL Transform

Page 1 of 2
Written by Gregory Scot Collins
Tuesday, 12 September 2006, 5:30 AM
This article has been tested to work with the following products and versions. No guarantee of compatibility, with or without modification, is offered for products or versions other than those listed.
One of the most powerful features of XSLT is the ability to open arbitrary XML documents at any point during a transform. This is accomplished via the document() function. An XML file retrieved by the function call is returned as a node-set to a variable. Assuming the file exists and is valid XML, you can process the node-set stored in the variable just as you would your main document.
We will examine how to use the document() function to retrieve arbitrary values from a SQL database table. This will be accomplished by opening a parameterized ASP.NET page that will use a stored procedure to return XML-formatted table rows. The transform will then render values from the SQL XML document in the HTML output.
While the technique described herein demonstrates how to retrieve information from a database during an XSL transform, it could easily be modified to store information to the database.
Because we will be using Active Server Pages, you will need access to a .NET enabled Web server. We will assume that you have one installed locally and that you will be testing this on http://localhost. If you are working with a remote server, substitute in your server name and any required path. We will also assume that you have a local installation of Microsoft SQL Server 2005 and Microsoft SQL Server Management Studio (the Express editions may also be assumed).

Create the Page.xml file

We need a simple XML file to transform that will provide a parameter to be passed to our ASP.NET page and then on to SQL Server to determine which table row to return. We will use the id attribute of the root node Page as the parameter. Copy the contents of Listing 1 into a text editor, and then save the file as Page.xml.

Create the Page.xsl file

Copy the contents of Listing 2 into a text editor, and then save the file as Page.xsl. Be sure to save it in the same folder as the Page.xml file.
The key to to accessing your database during the transform is the document() function that takes a single parameter: the path to the document to load. In this case the document is an Active Server Page (see Listing 4) to which we pass a parameter that has been concatenated with the URL. The parameter is pulled live from our XML document and will change based on the page being rendered.

Create the Default.aspx file

The following file is our entry Web page that renders our XML file in the browser using the XSL transform. Copy the contents of Listing 3 into a text editor, and then save the file as Default.aspx. Be sure to save it in the same folder as the Page.xml file.
Bookmark this Article
StumbleUpon  Stumble It!
Digg  Digg It!
del.icio.us  del.icio.us
Resources
Microsoft SQL Server 2005 Express Edition Service Pack 1
Microsoft SQL Server Management Studio Express
List of Figures
Listing 1 - The Page.xml file.
Listing 2 - The Page.xsl file.
Listing 3 - The Default.aspx file.
Listing 4 - The GetStats.aspx file.
Listing 5 - The SQL code to create the database table and stored procedure.
See Also
Use Logical Operators in Place of Conditional Expressions
Article Tags
Great Deals
TigerDirect Exclusive Deals, Limited Time Offers, Act Now And Save!
Find all current special offers on Adobe products.
Try SugarSync Free!
Join WebHost4Life.com
TigerDirect
Computers4SURE (4SURE.com - An Office Depot Co.)
Copyright © 2006-2010 Braintrove. All rights reserved. Braintrove, braintrove.com, and the Braintrove logo are trademarks of Gregory Scot Collins in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners. Any rights not expressly granted herein are reserved.