SQL Server 2000 is an XML-enabled database server. If you set up your Microsoft IIS system to take advantage of SQL Server 2000's XML capabilities, you can use a browser to access database records—without writing a single line of ADO code.
Although SQL Server 2000's XML support is noteworthy, you might be wondering how to put it to work to meet real-world business needs. One practical application is to use XML to develop a search application that works on both Web and Wireless Application Protocol (WAP) browsers. With the advent of WAP-enabled phones, businesses are trying to tap into the huge potential of this emerging market. If you're running SQL Server 2000 and IIS, you can meet the needs of remote and mobile users by building applications that run on Web and WAP browsers. To build such applications, you need to know how SQL Server 2000 and IIS work together. Let's look at how to configure your system, access a database through IIS, and create a Web- and WAP-enabled application that searches the database.
Understanding the Architecture and Process
Figure 1 shows how SQL Server 2000 and IIS form a three-tier architecture that lets you access database records from a browser. When the Web server receives a URL, IIS examines the URL to see whether it refers to the virtual directory that has been configured for SQL Server access. If the URL refers to the virtual directory, IIS loads the Internet Server API (ISAPI) DLL extension for SQL Server (sqlisapi.dll). If the URL contains XML commands, the DLL that implements SQL Server 2000's XML functionality (sqlxmlx.dll) translates the URL's XML commands into T-SQL commands. SQL Server 2000 then uses the T-SQL commands to retrieve the database records, which sqlxmlx.dll formats as an XML document. Web Figure 1 shows in detail how this part of the process works. (To view Web Figure 1, go to http://www.sqlmag.com and enter InstantDoc ID 22395.) Finally, IIS displays the XML document in the browser. If you want IIS to transform the XML document into a markup language such as HTML or Wireless Markup Language (WML), you can add Extensible Style Language Transformations (XSLT) style sheets to the process.
Before you can take advantage of SQL Server 2000's XML capabilities, you need to configure IIS by using the IIS Virtual Directory Management for SQL Server utility. You can learn about these configuration steps in Paul Burke, "XML and SQL Server 2000," May 2000.
For this article, I configured the virtual directory as XMLQuery, which maps to C:\inetpub\sqlxml. I configured the virtual name as Templates, which maps to C:\inetpub\sqlxml\templates. I discuss templates in more detail shortly.
After you configure IIS, you can use URLs to access information in a SQL Server 2000 database. You use a URL that includes a SQL query. For example, if you want to retrieve all the book titles in SQL Server 2000's Pubs database, you type the following into your browser:
Figure 2 shows this URL query's results. Note the use of the root parameter at the end of the URL. Unlike Microsoft's XML technology preview for SQL Server, SQL Server 2000's XML support generates records without a root element. If you use a URL without a root parameter, such as
you'll receive an error message stating that the XML page can't be displayed.
The root parameter in the URL is case sensitive. If you use the URL
you receive all the elements labeled
Using a Template to Improve Security
Microsoft intended URL access of databases for Web developers. With URL access, Web developers can easily access information from databases without using ADO. However, using a URL that has a SQL query can be dangerous because you're exposing all the information in the database. To prevent security problems, you can use a template. A template is an XML document that contains the SQL query. For example, Listing 1 shows the template Query1.xml for the
Before SQL Server 2000 can execute the template, you need to make an additional IIS configuration setting. Open the IIS Virtual Directory Management for SQL Server utility, then select the Virtual Directory configured for XML access and view its properties. In the XML Query Properties dialog box, select the Virtual Names tab and click New. In the Virtual Name Configuration dialog box that appears, you need to map the Template virtual directory to a physical path. In the Virtual name text box, type Templates. In the Type drop-down list, select template. In the Path text box, enter the path to the location where you want to store the template files, then click Save. Next, place the Query1.xml template in the folder that you specified. To access the database through the template, type the following into your browser:
Figure 3 shows sample results. Notice that the XML document automatically contains the
The template in Listing 1 performs a simple query, but you can create templates that perform more complex queries. For example, you can create a template that searches for titles that match a specific string. By specifying the search string in the URL and not the template, you can use the same template for different queries. The search string you specify in the query string is passed to the template as a parameter.
To perform this more advanced query, you need to make several changes to the template so that it accepts passed-in parameters. Listing 2 shows the modified template, Query2.xml. In Query2.xml, you need to add the
In Listing 2's
To use the Query2.xml template, you can type the URL with or without an appended Title parameter. If you type the URL with a Title parameter (e.g., http://localhost/xmlquery/templates/query2.xml?Title=Computer), SQL Server retrieves all the titles that contain the word you specified (in this case, all the titles that contain the word Computer). If you don't specify the Title parameter, SQL Server uses the default value that you defined in the
SQL Server retrieves all the titles that contain the word Sushi.
Our template isn't finished yet, however. As an experienced database developer, you likely know about the efficiency of storing SQL queries as stored procedures in the database server. Listing 3 shows the stored procedure for the SQL query in Listing 2. To execute this stored procedure from the XML template, you have to further modify the template. Listing 4 contains the new version of the template, Query3.xml.
Creating the Search Application
With the template completed, you can create the application that uses the template. Suppose you want to provide an application that searches the Pubs database. You can create an HTML file that produces a search form like the one that Figure 4 shows. Listing 5, page 58, contains a file, Search.html, that creates this search form. As callout A in Listing 5 shows, the