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.
IN THIS ARTICLE:
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.
Stumble It!
Digg It!
del.icio.us
