SQL Server’s SQLXML templates to feed AJAX pages (Part 1)

Introduction

In, this article, I’m going to demonstrate using the SQLXML virtual directory component for IIS to display a set of data in an AJAX-like web page.

To load a dataset into a page using asynchronous javascript, a developer would usually create an intermediate page using some server-side language to retrieve the data and format them as XML, then return that page’s content in response to an asynchronous request from the AJAX page.

Instead, I’m going to use the XML capabilities of SQL Server to output the result of my query directly as XML, and then get that XML stream via a transparent HTTP request to a SQLXML template (by transparent, I mean that I’ll just have to call a regular URL, optionally passing some GET parameters, no need to worry about connection strings or anything of the sort).

Let’s get started

Install SQLXML

First we need to install and configure the SQLXML virtual directory component for IIS.

I’m not going to go through all the necessary steps in details, as it’s not the point of this article. You can download the MSI package from Microsoft. The installation process is very straightforward.

Now that SQLXML is installed I will configure a virtual directory and a template directory.

Create the directory structure for the SQLXML templates

Just like any other IIS virtual directory, you can map any directory on your computer to a directory relative to the root of your web server. In my case, I created a folder in c:\inetpub\wwwroot named “sqlxml”. Within that new folder, I also created another folder named “temp”, which will contain the SQLXML template files.

Configure SQLXML to read the AdventureWorks database

Open Programs > SQLXML 3.0 > Configure IIS Support.

On the left panel of the new window, click on your machine, then on the right panel, right-click the root website and select New > Virtual Directory.

This is the Virtual Directory Properties window. That’s where you configure access to your database. It is split into several tabs.

General

In the virtual directory name textbox, enter a name. Any name is fine. It will allow access to the content of that directory by typing http://%5Bserver%5D/%5Bvirtual name]. I used “sqlxml” as the name.

In local path, enter the actual location of the directory on your hard drive. In my case, it’s c:\inetpub\wwwroot\sqlxml (the directory I created earlier).

Security

This defines how the database server can be accessed. It’s equivalent to the login settings in a connection string. As it will only be accessed locally for the sake of this article, I won’t worry about security and use Windows Authentication.

Data source

That’s where we select what data server and which database we’ll use.

In SQL Server, I entered the name of my local SQL Server (note that the server is not required to be on the same machine), and I selected “AdventureWorks” as the database (I also unchecked use default database for current login).

Settings

This tab offers a lot of options in terms of ways of querying the data. Some of them are potentially unsafe and should never be used in a production environment. I only checked “allow template queries”. Templates are xml files that can receive parameters and execute SQL queries.

Virtual names

That’s where I will define the location of my template directory. It’s the folder I created earlier within the sqlxml folder in wwwroot.

To define the template directory, I click new virtual name, then I enter “temp” as the name, select template as the type, and c:\inetpub\wwwroot\sqlxml\temp\ as the path, and hit save to validate.

I’m done, so I can click Ok and close the configuration utility. There are a lot of other options available, but I’m not going to digress away from the subject of this article.

Create a test SQLXML template

We need to make sure that the template directory is properly configured. That’s why we’ll write a very simple template to make sure we can get data from the database.

In the template directory (c:\inetpub\wwwroot\sqlxml\temp\) create a new text file named “test.xml” and type the following content in it:

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT TOP 20 * FROM Production.Product FOR XML RAW
</sql:query>
</root>

Once the file is saved, you can test it by typing http://localhost/sqlxml/temp/test.xml in your browser.

Normally, you should see the data from the Production.Product table displayed as XML in your browser. That’s as simple as that!

Next, we’ll see how to use a template to fill a page with data.

Advertisements

One Response to “SQL Server’s SQLXML templates to feed AJAX pages (Part 1)”

  1. SQL Server’s SQLXML templates to feed AJAX pages (Part 2) « Le Bash Blog Says:

    […] templates to feed AJAX pages (Part 2) This is the second part of the article, please read the first part if you haven’t […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s


%d bloggers like this: