In this chapter, you've seen how to publish a virtual directory for XML data access, either using the Configure SQL XML Support In IIS MMC snap-in or by writing a script that uses the automation objects for virtual directory management.
You've also learned how to execute queries in a URL and retrieve the results over HTTP. Although this is a valid way to retrieve data in a test or development environment, in most production solutions, you need to create templates or schemas that encapsulate the data you want to publish.
In Chapter 4, I described how a Microsoft SQL Server virtual directory can be published to allow users access to a database over HTTP. In this chapter, Ill explain how you can publish XML templates to encapsulate the data you want to make available. Using XML templates gives you a more controlled security environment than simply allowing users to send queries in a URL to a server for data retrieval.
Templates contain one or more queries that retrieve data from the database. The results are then sent to the calling browser or client application. Because the client receives only the query results and not the source code for the query, the data access logic is encapsulated like the source code of an ASP file. This arrangement allows you to publish data securely over the Internet. For example, Northwind Traders could make the product catalog available over the Internet by creating a template that retrieves the necessary data from the database. Customers could then simply access the template using a browser. Customers would never see the SQL used to access the data; they would see only the resulting product list.
I described XML templates in Chapter 3. A template is an XML document based on the Microsoft XML-SQL namespace. When you use templates with HTTP, you can store the template file in a virtual name of template type and access the file by specifying the template name in a URL. For example, a template namedproducts.xml saved in a virtual
You create the virtual name of template type by using the Configure SQL XML Support In IIS MMC snap-in tool or the automation objects for virtual directory management, as described in Chapter 4. A single virtual directory can contain many virtual names, so it’s up to you to decide how to arrange your templates. You might want to create multiple virtual
To use templates, you must configure the virtual directory to allow template queries. You accomplish this by selecting Allow Template Queries on the Settings tab of the Virtual Directory Properties dialog box in the Configure SQL XML Support In IIS MMC snap-in tool or by setting the AllowFlags property of the SQLVDir object to 8.
As I mentioned, a template is an XML file containing a document based on the Microsoft XML-SQL namespace. The template usually contains at least one query. Let’s assume that Northwind Traders wants to publish its catalog on the Web. A
<?xml version=‘1.0’ ?> <categorylist xmlns:sql=‘urn:schemas-microsoft-com:xml-sql’> <sql:query> SELECT categoryid, categoryname FROM categories FOR XML AUTO, ELEMENTS </sql:query> </categorylist>
You can save this category list template as categories.xml in the
To make your templates more flexible, you can add parameters. This strategy allows users to request data based on one or more variable values. For example, a template containing a parameter would allow users to supply a category ID and return a list of the products in the specified category.
Parameters are placed in the header of the template, which is defined using the header tag. Each parameter is then defined using the param tag, with an optional default value. You could use the following template to retrieve products by category:
<?xml version=‘1.0’ ?> <productlist xmlns:sql=‘urn:schemas-microsoft-com:xml-sql’> <sql:header> <sql:param name=‘categoryid’>1</sql:param> </sql:header> <sql:query> SELECT productid,
productname, unitprice FROM products WHERE categoryid = @categoryid FOR XML AUTO, ELEMENTS </sql:query> </productlist>
This template is saved as Products.xml in the Demos\Chapter5\Templates folder on the companion CD. This template includes a single parameter with a default value of 1. Notice that the param tag includes a name attribute used to identify the parameter; you use this name in the SQL query by prefixing it with the @ symbol.
To access data using a parameterized template, you specify the parameters in the URL as a query string. For example, the products in category number 2 could be retrieved from the products.xml template using the following URL:
You can execute this query by opening the shortcut named Product List in the Demos\Chapter5 folder on the companion CD.