XML and SQL Server 2000

2017-07-07 02:10:07

If you are anything like me, you need to have a concrete example presented before you really understand not only what these new terms mean, but also how they fit together. The next topic, "The Virtual Directory Management Object Model" is difficult to understand if you haven't worked with a virtual root and its associated components and terms. Before we work with a virtual root and its associated components , we have some preparation to accomplish.

This preparation consists of creating a small directory structure to hold our template and schema objects. We must link a virtual root to the directory structure we've created, and then specify where the various file types should be stored.

I've made a couple of assumptions here: first, that your IIS root was installed to your D: drive; and second, that the Inetpub\ wwwroot directory is located directly off the root of the D: drive. These assumptions establish common ground from which to work. Remember throughout this discussion that you can set up this directory structure wherever you please . Moreover, you can call the directories any name you want.

Now let's get to work. In the D:\inetpub\wwwroot directory, create another directory called Nwind. Next, in the Nwind directory, create three more directories called dbobjects, schemas, and templates, respectively. The directory structure should now look like that in Figure 3.1.

Figure 3.1. Preliminary directory structure setup.

This configuration is very simplistic and will work well in a learning environment. The "real world" dictates, however, that the power of virtual directories allows them to be physically anywhere and, for security reasons, virtual directories shouldn't be made a subdirectory of the default Web site.

Use the following steps to configure the virtual directory using the Configure SQL XML Support in IIS option.

Starting the Virtual Directory Management Utility

From the Start menu, select Programs, select Microsoft SQL Server, and then click Configure SQL XML Support in IIS (see Figure 3.2).

Figure 3.2. Starting theVirtual Directory Management Utility.

Naming the Virtual Directory

Expand the server you'll be working with, and then right-click the Web site you want to configure. In the drop-down box, select New, and then Virtual Directory to display the General tab of the New Virtual Directory Properties dialog box (see Figure 3.3).

Figure 3.3. The General tab of theVirtual Directory Management Utility.

The options on the General tab are listed in Table 3.1.

Table 3.1. The General Tab Options

Option	Description
Virtual Directory Name	The name of the virtual directory.
Local Path	The full path to the physical directory associated with the virtual directory (for example, D:\inetpub\wwwroot\Nwind). The Browse button can be used to navigate to the directory if the virtual root is located on the local computer.The Browse button is not available for a remote virtual directory.

In the Virtual Directory Name box, enter a name for the virtual directory. For this example, type Northwind. (Remember you can call it any name you want, but we are using this configuration for all the examples in the book.) Enter the path to the directory Nwind that you created previously ( D:\inetpub\wwwroot\Nwind ). Remember that you also can use the Browse button to point to the directory.

Specifying the Login

Select the Security tab. On this tab, select SQL Server, and enter the valid SQL Server login information. When you go to the next tab, you are asked to confirm this password. See Figure 3.4.

Figure 3.4. The Security tab of theVirtual Directory Management Utility.

The options on the Security tab are given in Table 3.2.

Table 3.2. The Security Tab Options

Option

Description

Always log on as

The Windows and SQL Server account type use the anonymous access scheme supplied by IIS authentication security. With this anonymous access, everyone is logged on using the credentials in User Name and Password.

If SQL Server is selected, a valid SQL Server logon must be entered. If Windows is selected, the supplied login is used for all users.

User Name	User name for the login.
Password	A password for the user.
SQL Server	Specifies that all users use a SQL Server login.
Windows	A Windows account is used for everyone who accesses the directory. By default, the IUSR_Servername is the account used.

Use Windows Integrated Authentication

For NT 4.0, this selects Windows challenge/response authentication. For Windows 2000, this selects Windows Integrated Authentication. SQL Server must allow access for the Windows user accounts.

Use Basic Authentication (Clear Text) to SQL Server Account

Prompts a SQL Server login and password. What actually happens here is that anonymous access is enabled, and during the login, the server tries anonymous access first. If that fails, an error message to the browser causes the login box to appear.

Mapping to the Data Source

Select the Data Source tab. On this tab, in the SQL Server box, enter the name of a server running SQL Server 2000 and the name of an instance of SQL Server 2000, if more than one is running on the designated server. If SQL Server 2000 is running on the local server, you can enter (local) here. In the Database box, enter Northwind as the name of the default database or select it via the drop-down box (see Figure 3.5).

Figure 3.5. The Data Source tab of theVirtual Directory Management Utility.

The options on the Data Source tab are given in Table 3.3.

Table 3.3. The Data Source Tab Options

Option	Description
SQL Server	The name of the server (or the instance) you want. With multiple instances of SQL Server running, you can specify the server name and instance.
Database	The name of the default database on the server. The virtual directory maps to this database.

Specifying the Query Settings

Select the Settings tab. On this tab, select the Allow URL queries, Allow template queries, Allow XPath, and Allow POST options (see Figure 3.6). This enables all forms of submission that are used in the examples.

Figure 3.6. The Settings tab of theVirtual Directory Management Utility.

The options on the Settings tab are given in Table 3.4.

Table 3.4. The Settings Tab Options

Option	Description
Allow URL queries	Execute SQL queries directly from the URL. Caution: For security reasons, allowing direct execution of queries in a URL is not recommended. You'll want to remove the database details from the user. I recommend that you don't allow this in a production environment. The proper execution method is through the use of template files mentioned next. Better safe than sorry.
Allow template queries	Execute a template file from the URL.This is enabled by default.
Allow XPath	Execute XPath queries against schemas directly from the URL.
Allow POST	Enable data posting (data writing for you non-HTTPers) to the database. This is not enabled by default. Warning: Forgetting to enable this option will bite you and be extremely difficult to troubleshoot if you are planning to write data to the database. If you are not planning to write data, then do not enable it for security reasons.
Maximum size of POST queries (in kilobytes)	Maximum amount of data that you can send to the server per query.

Specifying the Virtual Names

After the virtual root has been configured, we need to create the virtual names that will point to the template files, schema files, and dbobjects, and associate them to the physical directories that house these entities. To accomplish this, perform the following steps:

Select the Virtual Names tab. On this tab, click New to create the virtual name for the template type.

The options on the Virtual Names tab are given in Table 3.5.

Table 3.5. The Virtual Names Tab Options

Option

Description

DefinedVirtual Names

Specify the virtual name used to access the resource. For example, the virtual name schema in theVirtualroot virtual directory would be accessed with the URL http:// servername /VirtualRoot/schema .

Type

Specifies the type of query that the virtual name will access.

dbobject	A database object (table, and so forth).
schema	An XPath query against a mapping schema.
template	SQL queries in a template file.

Path

Specifies the directory path where the templates and mapping schemas are located. This can be an absolute or relative path (relative to the physical directory associated with the virtual directory). A virtual name of dbobject type requires no path. The Browse button allows navigation to the directory on a local server, but is not available for remote servers.

For template or schema mappings, the path can go to a folder or a file. If it goes to a folder, the filename must be in the URL (for example, http: //servername/Virtualroot/ schema/schema.xml ). If it goes to a file only, that file can be used.

URL paths are not allowed because the utility doesn't validate path entries.

New

Click to create a new virtual name.

Delete

Click to delete a mapping from the list of mappings.

In the Virtual Name Configuration dialog box, enter Template in the Virtual Name box (you could put any name you want here, but let's use the same name in the example). In the Type list, select Template. In the Path box, enter the physical path to the Templates directory that was created earlier ( D:\inetpub\wwwroot\Nwind\Templates ). See Figure 3.7. Click Save to save the virtual name.

Figure 3.7. TheVirtual Name Configuration dialog box of the Virtual Directory Management Utility.

Warning:

The program does not check the existence of the directory you enter, so you won't know if there was a mistake until you can't get anything to work. Moral of the story: Double-check your entry! This saves you from a lot of frustration. It's bitten me more than once.

Again, select the Virtual Names tab. On this tab, click New to create the virtual name for the schema type.

In the Virtual Name Configuration dialog box, enter schema in the Virtual Name box (again, you could put any name you want here). In the Type list, select schema. In the Path box, enter the physical path to the Templates directory that was created earlier ( D:\inetpub\wwwroot\Nwind\Schemas ). Click Save to save the virtual name.

One last time, click New on the Virtual Names tab. On this tab, click New to create the virtual name for the dbobject type.

In the Virtual Name Configuration dialog box, enter dbobject in the Virtual Name box. In the Type list, select dbobject. Click Save to save the virtual name.

On the Virtual Names tab, click OK to save the settings.

That's it! You've created the virtual directory Northwind. All queries using the virtual directory will be against the database Northwind.

The Advanced Tab

For our purposes, the only setting that needs to be changed on the Advanced tab is Disable Caching of Mapping Schemas. Experienced developers know that caching in a development environment can cause innumerable headaches by preventing changes from appearing until the cache timeout period expires , so make sure this option is checked, so that changes we make are available immediately (see Figure 3.8).

Figure 3.8. The Advanced tab of theVirtual Directory Management Utility.

The options on the Advanced tab are given in Table 3.6.

Table 3.6. The Advanced Tab Options

Option	Description
ISAPI Location	Specify the location of the SQLISAPI.DLL file.
Additional user settings	Specify additional optional settings. These settings are passed untouched to the connection string, and passed in to OLE DB.
Caching options	Sqlisapi.dll has a caching option that stores mapping schemas in its memory for subsequent queries. For development purposes, the caching option should be disabled so that schemas are reloaded every time a query is executed against the schema.

URLs,Virtual Directories, and Physical Directories

Let's take one final look at the relationship among URLs, virtual directories, and physical directories. To simplify things, we'll only talk about templates.

Our example has a physical directory defined as the virtual root at

D:\inetpub\wwwroot\Northwind

with the name nwind. A physical subdirectory of this directory

D:\inetpub\wwwroot\Northwind\templates

is defined as a virtual directory with the name template. A URL would access a file in this directory with

http://IISServername/nwind/template/filename.xml

servername/virtual root name/virtual directory name/filename.xml

The TEMPLATES directory holds multiple templates and is physically named TEMPLATES, but the URL calls only one template file (the virtual name was defined as template). This is solely my personal preference. You can name them as you see fit but all the book examples follow this nomenclature .

If we had the virtual root dir3 deeper in the directory structure, for instance,

D:\inetpub\wwwroot\dir1\dir2\dir3\virtual directory

you would still use the URL

D:\inetpub\wwwroot\dir3\virtual directory\filename.xml

I hope the example helped to explain this relationship a little better.