| Downloads |
|---|
| 22395.zip |
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/
Figure 3 shows sample results. Notice that the XML document automatically contains the <Titles> root element.
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 <sql:header> element, which acts as a container that holds all the parameter definitions you want to use. You then use the <sql:param> element to define each parameter. You can define any number of parameters within the <sql:header> element. In this case, I've defined only one parameter, Title, set to the default value of Sushi.
In Listing 2's <sql:query> element, I modified the SQL query to accept the Title parameter. I preceded the parameter with the at sign (@) because it refers to the named parameter defined in the <sql:param> element.
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:param> element. So, in this case, if you type
http://localhost/xmlquery/templates/query2.xml
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 <form> element contains the action attribute, which points to the Query3.xml template.
When you enter a string in the search form and click Search, the template returns the records in an XML document, similar to the one that Figure 3 shows. However, displaying records in this format isn't useful for viewers of Web or WAP browsers. Thus, you need to format the XML document into HTML code for Web browsers and into WML code for WAP browsers. With SQL Server 2000's XML support, you can simply attach an XSLT style sheet to the XML results to transform those results into the format you need.
To transform the XML document into content for Web browsers, you can use the XSLT style sheet HTML.xsl, which Web Listing 1 shows. (For download instructions, see "More on the Web.") If you're unfamiliar with XSLT, see the XSLT Developer's Guide and the XSLT Reference on the Microsoft Developer Network (MSDN). You can find links to both resources at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk30/htm/xmmscxmloverview.asp. You can also check out the paper "An Introduction to XSLT and XPath" (http://www.wirelessdevnet.com/channels/wap/training/xslt.html). Figure 5 shows sample results from HTML.xsl.
To transform the XML document into content for WAP browsers, you can use the XSLT style sheet WML.xsl, which Web Listing 2 shows. However, taking advantage of the wireless platform involves more than just writing WML code and hoping that WAP-enabled devices can display the pages. The various WAP browsers differ greatly in how they look and act. To achieve the same user experience regardless of the WAP browser, you need to test and tailor the content to suit the different WAP devices. One tool that lets you test WML code is Openwave System's Openwave Simulator (formerly named UP.Simulator). The simulator is part of the Openwave software development kit (SDK), which you can download for free at http://developer.openwave.com/download/index.html#sdk. Different configurations for the Openwave Simulator are available for the various WAP-enabled phones on the market. You can download the configurations you need at http://developer.openwave.com/download/simconfig.html.
After you download the necessary files, you can test the WML code. In this case, you enter the following into the URL text box:
http://localhost/xmlquery/templates/query3.xml?Title=Computer &contenttype=text/vnd.wap.wml&xsl=wml.xsl
Web Listing 3 shows the WML code that the simulator receives, and Figure 6 shows the screen you'll see.
No matter whether you use an XSLT style sheet to transform the XML document into HTML or WML code, you must tell IIS about that style sheet by referencing it in either the template or the URL. To reference an XSLT style sheet in a template, you place the style sheet in the same directory as the template (e.g., C:\inetpub\sqlxml\templates). You then replace the first line of code in the Query3.xml template with code such as
<Titles xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl="HTML.xsl">
This code uses the sql:xsl attribute to specify the style sheet's name (in this case, HTML.xsl).
To reference an XSLT style sheet in a URL, you place the style sheet in the root virtual directory or one of its subdirectories (e.g., http://localhost/xmlquery). You then append &contenttype=text/html&xsl=html.xsl to the URL. So, for example, if you want to search for titles that contain the word Computer, the URL would be
http://localhost/xmlquery/templates/query3 .xml?Title=Computer&contenttype=text/html& xsl=html.xsl
The Finishing Touches
At this point, you've created a search application whose retrieved records display in the correct format on both Web and WAP browsers. However, the search form you created earlier works on only Web browsers. Thus, you need to provide a search form for WAP browsers. The file Search.wml, which Listing 6 shows, creates a WAP search form similar to the one that Figure 7 shows.
You now have two search forms. Instead of relying on users to enter the correct filename (i.e., Search.html or Search.wml) to display the correct search form for their type of browser, you can use an Active Server Pages (ASP) file to automatically determine whether a user needs the HTML or WML search form. As Listing 7 shows, the Redirect.asp file checks the HTTP_ACCEPT environment variable to see whether it contains the string wap. If it does, the Search.wml file executes; otherwise, the Search.html file runs. To use Redirect.asp, you must set the WML MIME type (for files with the .wml extension) on your Web server. If you don't, you'll receive error 406.
Concentrate on What's Important
With the process I've demonstrated, you can access SQL Server 2000 database records without writing any ADO code. This process is especially useful if you need to build applications that retrieve and display database information for remote or mobile users. Instead of getting unduly concerned with the intricacies of database access, you can rely on SQL Server 2000's XML support. That way, you can concentrate on building applications that meet the needs of all your users, whether they're using Web or WAP devices.
