Creating a Virtual Directory for Use with SQL Server 2000

Before you get started, for some of the examples, you will need to create an Internet Information Server (IIS) virtual directory that will enable you to access the SQL Server-provided Northwind database from the Web. All the examples in this chapter use Northwind.

1. First, create a physical directory where you want to store the Web files you create. Create a special subdirectory below this directory called Template where the XML templates you create will be saved (templates are covered later in this chapter in the section "Using XML Templates"). D:\SQL2E\Template is an example.

   Now, we can create the virtual directory using the IIS Virtual Directory Management for SQL Server tool:

2. From the Start menu, navigate to the Microsoft SQL Server program group and click Configure SQL XML Support in IIS (see Figure 41.1).

   Figure 41.1. Configure SQL XML Support in IIS.

   

3. Select a server and right-click the Web site you will use. (The default Web site usually works if you have it, as you can see in Figure 41.2.) Select New and click Virtual Directory.

   Figure 41.2. IIS Virtual Directory Management for SQL Server.

   

4. On the General Tab (see Figure 41.3) of the New Virtual Directory Properties dialog, type NorthwindVdir under Virtual Directory Name. In the Local Path group box, type the name of the physical directory you created ( D:\SQL2E\Template ).

   Figure 41.3. Specifying the Virtual Directory Name in IIS Virtual Directory Management.

   

5. On the Security tab, configure a valid SQL Server login (such as sa for these examples).

   TIP

   Please remember that for a production environment, you will want to specify a login that has very few permissions in its assigned roles. Internet access to SQL Server might open up a new avenue for hackers to exploit. For purposes of this chapter, use a login whose role has at least SELECT , INSERT , UPDATE , and DELETE permissions for Northwind.

6. On the Data Source tab, under SQL Server, enter the database server name. (If you use SQL Server instances, use the instance name.) Then under Database type, select Northwind (see Figure 41.4).

   Figure 41.4. Specifying a SQL Server data source.

   

7. On the Settings tab, check the Allow URL Queries, Allow Template Queries, Allow XPath, and Allow POST check boxes.

8. On the Virtual Names tab, click New to create a virtual name for the Template subdirectory you created. Name it Templates , select the type Template, and enter the path to the Template subdirectory you created ( D:\SQL2E\Template ). Click Save.

9. Now, again on the Virtual Names tab, click New to create a virtual name that will be used to access Northwind's intrinsic database objects (tables, rows, fields, and so on). Name this one dataobjects and leave the type selection as dbobject . Click Save.

10. Click OK in the dialog box to close and save the virtual directory settings.

Barring any unforeseen networking considerations, your NorthwindVdir virtual directory is ready to use. Test it using a URL query (covered later in the section "Using URL Queries") by opening Internet Explorer (IE) and typing the following (substituting your Web server's name for < myserver > ) in the address box and pressing Enter. (Note: you can probably use localhost instead of the server's instance name if you only have one instance of SQL Server up and running):

 http://<  myserver  >/NorthwindVdir?sql=SELECT CategoryID FROM Categories FOR XML AUTO&root=testXML. 

The result is shown in Figure 41.5.

Figure 41.5. The URL query http://<myserver>/NorthwindVdir?sql=SELECT CategoryID FROM Categories FOR XML AUTO&root=testXML .

Additionally, try the following URL that tests the dataobjects virtual name you created (explained in the section "Retrieving Binary Data in XML"):

 http://<myserver>/NorthwindVdir/dataobjects/Categories[@CategoryID='1']/@Picture 

This should load an image of some dinner items into the browser (see Figure 41.6).

Figure 41.6. The URL query http://<myserver>/NorthwindVdir/dataobjects/Categories[@CategoryID='1']/@Picture .

The IIS Virtual Directory should now be working and be fully configured to resemble the following (see Figure 41.7):

Figure 41.7. A fully configured IIS Virtual Directory for SQL Server 2000.