Build a search application that works on both Web and WAP devices
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:
http://localhost/xmlquery?sql=select+*+from+titles+
for+xml+auto&root=Titles
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
http://localhost/xmlquery?sql=select+*+from
+titles+for+xml+auto
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
http://localhost/xmlquery?sql=select+*+from+TITLES+
for+xml+auto&root=TITLES
you receive all the elements labeled <TITLES> but not those labeled <Titles> or <titles>. Case sensitivity is especially important in the later transformation of the results to the desired markup language.
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 <Titles> query I just discussed. If you use a template, you expose only the template's name and not the name of your database and its columns.
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:
http://localhost/xmlquery/templates/query1.xml/
Prev. page
[1]
2
3
next page 
