How to Apply Filters
Now, let's look at an example that shows both cases. Before you start, consider the database schema that the T-SQL code in Listing 1 defines. The code creates two tables. The first table is a customers table containing the company name, a unique customer ID, and a bit flag indicating whether the customer has purchased a service contract. The second table is an addresses table that contains the address type, street address, and state. I purposely simplified these schemas to reduce the length of the listings. To try out the examples, create the tables in a database on your system by executing the script that Listing 1 shows.
To define an XML view, you need a schema that defines the structure and contents of the virtual XML document. Listing 2 shows an XSD schema that describes the XML document in Figure 1. At callout A in Listing 2, the schema declares a type called AddressType to represent a billing or shipping address. Note that the address doesn't include the type. The tag that contains the data—in this case, BillingAddress or ShippingAddress—specifies the address type. Next, the schema defines the Customers element, which is the root of the document. The Customers element contains one Customer element for each customer in the database. The company name, billing address, and shipping address are represented as subelements of Customer. To map the elements to columns in the database, you add annotations to this schema.
In Listing 2, the sql namespace prefix identifies annotations. The limit-field and limit-value attributes, in conjunction with the relation attribute, specify the column (or field) used for the filter and the value to be selected, such as the value of the AddressType column for shipping addresses. Listing 2's mapping schema contains three filters. The filter at callout B limits the view to customers who have purchased a service contract. This callout illustrates the first-case use of filters. Callout C shows the second filter, used with a relationship annotation to select only addresses with the value billto in the type column. The relationship subelement specifies the association between the customer and the address and references the foreign key CustomerID. So, callout C shows examples of the second-case use of filters. A third filter, which callout D shows, obtains shipping addresses in a similar fashion.
Querying the XML View
After you've built your XML view, you can use XPath to query it. XPath is a path-oriented, pattern-matching language invented for use in Extensible Style Language Transformations (XSLT) style sheets. You can use ADO or a SQLXML virtual directory to execute an XPath query against an XML view. For these examples, let's use the SQLXML method. Start by saving the mapping schema from Listing 2 to a file named CustAddr.xml. (You can avoid access security problems by saving this file to a directory beneath the Inetpub\www directory that IIS uses.) Then, to create a virtual directory, use the IIS Virtual Directory Management for SQLXML plug-in for Microsoft Management Console (MMC). Name the virtual directory Nov2002, then set the Local Path to the directory where you stored the mapping schema. Enter the security parameters to access the database that contains the tables from Listing 1, then select the Allow XPath check box on the Settings tab. On the Virtual Names tab, create a virtual name called CustView and set the path to ('.'), which will use the same directory you specified for the virtual directory. Then, save the virtual directory. You've now defined the XML view.
You use Internet Explorer (IE) 5.0 or later to execute an XPath query against the XML view. To execute a query, type http://localhost/Nov2002/CustView/CustAddr.xml/Customers in the address bar. Nov2002/CustView/CustAddr.xml selects the XML view that Listing 2's mapping schema defined, and /Customers is the XPath query to retrieve all customers. The query produces the results that Figure 1 shows. You can experiment with this example by modifying the annotations in the mapping schema or specifying different XPath queries.
Beyond the Limits
Using limit-field and limit-value inside a mapping schema is the equivalent of using conditions in a SQL view's WHERE clause. If you set up a trace through SQL Server Profiler, then execute the query, you'll see that the T-SQL code sent to SQL Server contains WHERE clauses that use the values specified in limit-field and limit-value. However, SQL views and XML views with filters differ in the flexibility of their comparisons. Whereas conditions in a SQL view's WHERE clause give you wide flexibility in making comparisons, filters in XML views support only equality comparison. More complex scenarios require a SQL view that exposes the data in a convenient form for mapping to XML. But for many simple scenarios, limit-field and limit-value provide an effective method of exposing normalized data in an XML view.