You can access data in the form of XML using SQL Server 2000 in several ways. First, XML data generated by SQL Server 2000 can be retrieved through HTTP by using queries passed in a QueryString. (XML data can also be returned through calling XML templates that are stored on the Web server.) You can also retrieve XML data using a SELECT command and FOR XML keywords, by calling a stored procedure, or by using XPath queries. Finally, SQL Server 2000 fully supports XDR schemas, which includes the ability to map XML elements and attributes to tables and fields. Let's find out how these methods work.

Configuring Directories
SQL Server 2000 allows virtual directories created for Internet Information Server (IIS) to have direct access to data within an SQL database. Upon installing SQL Server 2000 (either the full installation or the client tools) on a machine with IIS, the process of creating these specialized virtuals is as simple as accessing the correct tool.

To begin the configuration process, go to Start|Programs|Microsoft SQL Server program group and select the Configure SQL XML Support in IIS option. After selecting this option, you should see a screen similar to the one you're used to seeing when accessing the Internet Services Manager. Open the default Web server, right-click it, select new, and then click the "Virtual Directory" option. This process is shown in Figure 1.

The next screen will prompt you for the virtual directory's alias and physical location. Supply the same information that you would for creating a normal IIS virtual. For these samples, a virtual alias of Northwind will be used with a physical path pointing to a northwind directory located in the wwwroot folder. Feel free to change the virtual's alias and physical path.

After you have entered this information, click on the Security tab (located at the top of the screen). Enter the correct user ID and password information to allow users to access SQL Server 2000 through the virtual directory. This section allows you to specify a Windows or SQL Server account type. It also allows you to use Windows Integrated Security, if desired. Security is always a crucial part of any application, and the SQL Server 2000 documentation provides detailed information about this important topic.

	Figure 1. What You See. Click here.

After entering the correct security information, click the Data Source tab. The information required by this section is similar to what you would provide when creating a DSN or Universal Data Link. Simply supply the name of the SQL Server 2000 machine and the appropriate database within the server. For these examples, we'll rely on the Northwind database.

After completing these steps, click on the tab named Settings. This tab contains several options that can be selected to lock down the virtual directory's access to the database. Figure 2 shows the different options.

Check the boxes next to each option in the Settings tab and click the Apply button. When building real applications, you'll want to select the options that allow adequate access to the SQL server machine but also provide an adequate level of security. We'll get to that in a minute.

The Virtual Names tab allows templates, schemas, and dbobjects to be associated with the newly created virtual directory. This screen allows you to tell the virtual directory where to go when the calling application needs access to a particular template, schema, or dbobject. Because these topics haven't been covered yet, we'll momentarily defer a more in-depth discussion on them. For now, click the OK button so that the virtual is created with all the different properties you have entered. Once completed, click on the default Web server located in the left pane, and the newly created virtual should now appear in the right pane. You can make changes to any of the virtual properties by right-clicking on it and selecting Properties. Now that you've created an SQL Server 2000 virtual directory, let's take a look at the many ways it can be used to retrieve XML.

URL Queries
Using the virtual directory we just created, queries can be executed against the Northwind database by placing the SQL query in a URL. To see this in action, open your browser and type the following URL into the location box. If you used a virtual alias other than northwind or if you're querying a remote IIS server, you'll need to substitute the appropriate names:

http://localhost/northwind?sql=SELECT+
*+FROM+Customers+WHERE+CustomerID='ALFKI'
+FOR+XML+AUTO&root=root

The following XML document will appear in the browser, assuming the virtual directory has been configured properly:

<?xml version="1.0" encoding="utf-8" ?>
<root>
   <Customers CustomerID="ALFKI" 
      CompanyName="New Name" 
      ContactName="Maria Anders" 
      ContactTitle=
         "Sales Representative" 
      Address="Obere Str. 57"
      City="Berlin" 
      PostalCode="12209" 
      Country="Germany" 
      Phone="030-0074321"
      Fax="030-0076545"
   />
</root>

Let's break that URL into pieces. To start, the URL path to the virtual named northwind is followed by the SQL query that we want to execute against the Northwind database. In this case, the query is defined as: SELECT * FROM Customers WHERE CustomerID='ALFKI'.

To allow the database to recognize the query properly, it is set equal to a QueryString parameter named sql. You'll also notice that the query is URL-encoded so that it can be passed properly to the database. All spaces have been replaced with the + character, and other characters that invalidate the URL are URL-encoded, as well. At the end of the query, two new keywords have been added. These are the FOR XML keywords that serve the obvious purpose of returning data from the database in the form of XML. These keywords are followed by the AUTO keyword that tells the database to return an XML document that allows for elements to be nested. If this SQL query returned customers and orders, using the AUTO keyword automatically nests all order elements under the appropriate customer that placed the order. All of the fields specified in the query will be returned as attributes.

After the FOR XML AUTO keywords, another QueryString parameter named root is included. This parameter is used to name the root element of the XML document that will be returned. We can run the same exact query and substitute the value of Northwind for the root QueryString parameter value. The resulting XML document will look exactly the same as the one mentioned earlier except that the root element will now be named Northwind.

While this sample is quite simple, you can use HTTP to execute more complex queries that join different tables. This query builds on our example by joining the Customers and Orders tables found in the Northwind database:

http://localhost/northwind?sql=SELECT
+Customer.CustomerID%2cCustomer.Contact
Name%2c%5bOrder%5d.OrderID+FROM+Customers+
Customer+INNER+JOIN+Orders+%5bOrder%
5d+ON+Customer.CustomerID%3d%5bOrder%
5d.CustomerID+FOR+XML+AUTO&root=Northwind

The output from this query is shown in Listing 1.

In cases where you do not want any nesting to occur between the customers and orders, SQL Server 2000 offers another keyword that can be used instead of AUTO. This alternative keyword is named RAW. Using the RAW keyword results in all fields being grouped into one XML element "row" structure.

Virtually any query can be executed using HTTP. For example, let's assume that a particular application has a stored procedure named sp_GetXml as shown here:

CREATE  PROCEDURE sp_GetXml
   (    
      @CustomerID varchar(5)
   )
AS
   BEGIN
      SELECT CustomerID, CompanyName, 
         ContactName
      FROM Customers
      WHERE CustomerID LIKE 
         @CustomerID + '%'
      FOR XML AUTO
   END

To execute this procedure and pass in the appropriate argument, the following HTTP query can be used: http://localhost/northwind?sql=exec+sp_GetXml+'A'&root=root.

This same logic applies to more advanced stored procedures and allows for dynamic substitution of the argument values ("A" in this case) depending on the results that the end user wants to see.

Thus far, all of the examples involving HTTP queries have resulted in XML data being contained within attributes. What if you need some fields returned as elements? This can easily be accomplished by using a new keyword in SQL Server 2000 named ELEMENTS. Use of the ELEMENTS keyword is restricted to cases in which the FOR XML AUTO keywords are also used. Whenever data needs to be contained within elements rather than attributes, simply add FOR XML AUTO, ELEMENTS to the end of the SQL SELECT statement or stored procedure.

Figure 2. Everything's Virtually Set. Click here.

Queries with XML Templates
The ability to query SQL Server 2000 directly through HTTP by embedding SQL into the query string is certainly a powerful and useful feature. However, it can represent a potential security risk. Once one end user learns about the ability to query the database directly using the browser, data within the database could be compromised if the user attempts to perform insert, update, or delete procedures.

To allow for more secure data transactions in which the user cannot query directly through a URL, SQL Server 2000 introduces the concept of XML templates. SQL Server virtual directories can be configured to block URL queries. Instead, queries can be directed toward XML template files that contain the desired SQL query.

Before discussing template specifics, let's revisit the SQL Server Virtual Directory administration console. You can reach this tool by going to the Start|Programs|Microsoft SQL Server program group and selecting the Configure SQL XML Support in IIS option. Open up the computer hierarchy to reveal the Default Website. Click on this, and in the right pane you'll see the northwind virtual directory we created. Right-click on this virtual, choose Properties, and then go to the Settings tab.

The first checkbox, Allow URL queries, should be checked. To prevent users from accessing the database through a URL query, uncheck the box. All SQL queries must now be done through XML templates, XPath, or from template posts.

To allow XML templates to perform SQL queries, go to the Virtual Names tab and click the New button. Although the virtual name can be anything, the examples that follow will use a name of "templates." Enter this name and, in the dropdown box named Type, select template. Next, either type in a path to where your XML templates will be stored or click the Browse button. A path of d:\inetpub\wwwroot\xml\sql2000\templates was used for the examples here, although the path can point to anywhere you wish. Once you have supplied all the necessary information, click the Save button.

Now that a virtual has been mapped to a folder designated to hold XML query templates, let's create a valid XML template that can be used to execute SQL queries. Listing 2 shows a sample template. It uses a namespace prefix of sql with a URI of urn:schemas-microsoft-com:xml-sql. This namespace prefix is used to identify elements used by the SQL Server XML ISAPI. One of the elements is named query. It serves the rather obvious purpose of marking up SQL query statements located within the template file.

To use this template, go to this URL (substitute the proper server name and virtual if they differ from these): http://localhost/northwind/templates/listing2.xml.

Breaking this URL into individual pieces, you can see that the northwind virtual root is first specified. Within this virtual, the URL then uses the templates virtual name—this maps to the physical templates directory that we've already configured. Finally, the URL specifies the name of the template (listing2.xml). Executing this template using a browser results in an XML document containing different orders nested under customers elements.

Using templates rather than URL queries has several advantages. First, an end user now has no control over changing the SQL statements. By removing access to SQL Server through URL queries, only the SQL Server XML ISAPI can be used to process template files, and only an individual with write access to the template file can make changes. This prevents unauthorized inserts, updates, or deletes from being performed. Second, XML templates support the dynamic inclusion of parameters. This allows you to change the value of a SQL WHERE clause without having to make adjustments to the template file.

Working with parameters is as simple as including an XML header element (see Listing 3). Within the header element a param element has been defined with a name attribute equal to CustomerID. This parameter is given a default value of "A." You can use the parameter in the template just like you would in a stored procedure. Simply append the "@" character to the front of the parameter and then place it within the SQL statement or call to a stored procedure.

For this example, the CustomerID parameter is used in a WHERE clause. If the parameter is set to "B," the SQL statement will return all rows from the Customers and Orders tables with a CustomerID that starts with "B." Calling this template and passing the correct CustomerID parameter value is as simple as adding the parameter name and value on the end of the query string: http://localhost/northwind/templates/listing2.xml?CustomerID=B

XPath Queries, Schemas, Templates
As with SQL queries, XPath queries can also be embedded within an XML template file. This code exhibits a simple XML template containing an XPath query:

   <Northwind xmlns:sql=
      "urn:schemas-microsoft-com:
      xml-sql">
      <sql:xpath-query mapping-schema=
         "listing4.xdr">
         /Customer[@CustomerID=
            'ALFKI']/Order
      </sql:xpath-query>
   </Northwind>

This query uses a schema to return all orders placed by a customer with a CustomerID equal to ALFKI (see Listing 4). If XPath statements are to work, an XDR schema must be used to map different XML elements and attributes to the appropriate database tables and fields. The schema shown in Listing 4 contains special SQL Server 2000 annotations to do the mapping. Although a description of the schema annotations won't be covered here, the SQL Server 2000 documentation covers each one in detail and provides examples of using them within schema files.

As with the SQL queries embedded in XML template files, XPath queries use the urn:schemas-microsoft-com:xml-sql URI along with the sql prefix to identify custom elements and attributes used in the template. For XPath queries, an element named xpath-query is used to mark up the query syntax. This element also has an attribute named mapping-schema that is used to identify the path to the appropriate schema file (listing4.xdr in this case) used to map tables and fields to specific XML items. This code shows another template file that uses a more complex XPath query:

<Northwind xmlns:sql=
   "urn:schemas-microsoft-com:xml-sql">
   <sql:xpath-query mapping-schema=
      "listing4.xdr">
      /Customer[@CustomerID=
         'ALFKI']/Order/
         Employee[@LastName='Suyama']
   </sql:xpath-query>
</Northwind>

When executed, the XPath query returns the name of the employee that was involved with handling a specific order placed by a customer, as shown here:

<Northwind xmlns:sql=
   "urn:schemas-microsoft-com:xml-sql">
   <Employee EmployeeID="Emp-6"             
      LastName="Suyama" 
      FirstName="Michael"
      Title="Sales
      Representative"/>   
</Northwind>

XPath queries used within template files can also accept parameters. This process functions in a similar manner to parameters used in XSL style sheets. Like XSL, the "$" character is used to designate a variable. Listing 5 shows how to incorporate variables into templates containing XPath queries. Passing a parameter to the template can be accomplished by passing the parameter name and its associated value in the URL: http://localhost/northwind/templates/listing5.xml?ID=ORD-10643. Hitting this URL results in the XML document shown in Listing 6.

By using these techniques, XML data can be received directly from a SQL Server 2000 database. While URL queries, XML templates, XDR schemas, and XPath queries provide powerful ways to obtain XML data directly from SQL Server 2000, there are other key features that I did not cover, including FOR XML EXPLICIT queries, OPENXML and Updategrams. These topics, and code examples of how to leverage SQL Server 2000's XML features in both ASP and ASP+, will be discussed next time. Stay tuned!


Dan Wahlin is Director of Internet/Wireless Application Development for netEXE, Inc. and also works as a corporate trainer for Global Knowledge. Dan's current endeavors include working with several Microsoft technologies including SQL Server, ASP, Visual Basic, and COM/COM+. He also spends a lot of time syncing up a variety of distributed back-end systems using XML, XSLT, and SOAP. This article is adapted from his forthcoming book, XML for ASP+ Developers (SAMS, Feb. 2001). Dan can be reached at dwahlin@onebox.com.