Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

SQL Server I/O: Using HTTP with SQL Server XML

Last updated Mar 28, 2003.

As part of the XML-from-the-database experience, we need to talk about using SQL Server from the Web, natively. The process isn't that involved, and is quite powerful once you implement it.

As always, it's important to keep security in mind when you're developing an application. There are several ways to implement the security: from the database, at the application level, or in the middle-tier. When you write applications that will be availble over the Web, you have to make this decision early, since it is such an open platform. Fortunately, you can use Internet Information Server (IIS) in a Windows-account mode, such that the users are prompted for a login that the server knows about. This can be a local server account or on the domain, whichever works best. You can also place the Web site behind a firewall, so that the users are authenticated before they ever arrive at your SQL Server. Using the integrated security this way makes the authentication process easy.

You could also create a front-end page before this one, and enforce a Web authentication scheme there.

The process for working with XML data from SQL Server is to run the wizard for the web services for SQL Server, create a few directories, set up a virtual directory pointing to them, and then publish query pages to that directory. SQL Server will then respond to queries posed through that Web site, or use template files to display the data. Templates are the better way to go, as they hide a bit of the complexity of the query structure from the users, and also provide a little more security.

Let's break it down, step by step.

First, you need Internet Information Server installed. Test that by hitting a Web page on the server from another system even before you start. I can't tell you the number of times I've had to debug what looked like a complex XML return problem, only to find that the IIS server wasn't working in the first place. This is a special challenge if you've got Windows Server 2003 installed, since it uses a new paradigm for Web services.

In any case, you need to create at least three directories on your hard driver per distributed application. I usually create these underneath the C:\INETPUB\WWWROOT directory, but your directories may vary. The reason I keep mine there is to keep security and backups consistent.

For this example, I created these directories:

C:\INETPUB\WWWROOT\PUBSDEMO
C:\INETPUB\WWWROOT\PUBSDEMO\SCHEMA
C:\INETPUB\WWWROOT\PUBSDEMO\TEMPLATE

The first directory name is up to you, but the other two need to follow underneath it. It's best if you keep the names I've got here. The first directory is where you hook up the virtual directory for SQL Server to publish the XML data, and the SCHEMA directory is used for XML schemas that shape the data. The TEMPLATE directory is used to create XML templates that you can use to hide the complexity of the XML query or even hide it from the selection syntax I'll show in you a moment. For now, I'll keep it simple and leave these two blank.

With the IIS Server installed and tested, and the directories created, locate the Start-Button menu item called Microsoft SQL Server then Configure XML Support in IIS. This brings up a panel that allows you to set the virtual directories and map them to the ones you've just created. Once inside, right-click the Default Web Site and select New then Virtual Directory.

On the General tab, set the name of the virtual directory you want. I type pubsdemo here, so my URL would look like this:

http://NSALT/pubsdemo

Next, map that virtual directory to the physical directory, which in my case is:

C:\INETPUB\WWWROOT\PUBSDEMO

On the Security tab, select the type of security you want. I set mine to Windows Integrated, but you can also use SQL Server security. Doing so makes every connection use the same account, and by extension has the same account privileges. You'll allow the users to type queries in at the URL momentarily, so be careful with this choice. Don't allow that account to do DDL statements unless you know that's what you want.

After you set the desired security, move to the Data Source tab. Set the name of the SQL Server and the database the connection will use. In my case, that's pubs.

On the Settings tab, you set how the queries will be posted. If you are in a secure environment, you can set the top option that allows direct queries right in the URL, which is what I've done. Understand that the query and the results are clear text, so make sure you're OK with that. The other options set whether you allow POST operations to enter data back to the server from the browser, and whether you allow template queries. I set them all for this demo. Next week, I'll lock this down a bit.

On the Virtual Names tab select <New Virtual Name>, and then enter schema and select the schema type. Set the directory for the schemas. My schema directory is:

C:\INETPUB\WWWROOT\PUBSDEMO\SCHEMA

Now click Save and then repeat the process, this time entering template as the name, template as the type, and enter your template directory. Mine is:

C:\INETPUB\WWWROOT\PUBSDEMO\TEMPLATE

You're all set. To test the server, enter the following format in your browser:

http://NSALT/pubsdemo?sql=SELECT+*+FROM+titles+FOR+XML+AUTO&root=books

Replace NSALT with the name of your server, and enter any query you like after the ?= part. Make sure you include the &root=SomeRootTagName part if you use XML, otherwise the server won't have a place to "hang" the results on.

Next time, I'll show you how extend this process with templates, which makes the whole process more secure.

Online Resources

I won't cover XML schemas in my tutorials. You can find a great one at http://www.w3schools.com/schema/schema_intro.asp.

InformIT Tutorials and Sample Chapters

In addition to the link above, Nicholas Chase has an explanation of XML schemas here.