Filtering Values in XML Views

DBAs typically normalize data to improve the performance of their relational databases. Normalization organizes data into separate tables that each store data for one type of entity (e.g., customers, orders, products) or that provide the same physical data storage for different logical entities (e.g., shipping address, billing address). In the normalization, foreign keys associate values in one table with values in another table. In contrast, an XML document is rarely normalized because XML typically organizes data by using a distinct element to represent one type of entity, then nests one element within another to create a hierarchy that represents an association between entities. One challenge to defining a hierarchical XML view on a normalized database is specifying how the tables in the relational database correspond to the hierarchical XML elements in the XML view. When you define your XML view, you can easily meet this challenge by specifying the foreign keys that associate two tables in the relational database. But defining an XML view on a normalized database might present another challenge: How do you distinguish different logical entities stored in the same table? For example, when you define your XML view, how do you select a shipping address from several kinds of addresses in a normalized address table that includes an address-type column? The answer lies in the annotated schemas that you use to define your XML view.

Annotated schemas is a powerful feature of SQL Server 2000—and subsequent XML for SQL Server 2000 (SQLXML) Web releases—that lets you construct an XML view of your database. An XML view exposes all or part of your database as if it were an XML document, which you can query through XPath to produce XML results. You define XML views by using annotated XML Schema Definition (XSD) schemas, called mapping schemas, that define how data stored in your database tables is mapped to the elements, attributes, and structure of the XML document. You can think of an XML view as a traditional SQL view. However, whereas a SQL view uses a SQL query to define a virtual table in your database, an XML view uses a mapping schema to define a virtual XML document. In a SQL view, you use WHERE clauses to filter the data exposed in the view. In a mapping schema, you use the limit-field and limit-value annotations to define filters that limit the data exposed in the XML view.

When to Use Filters

Filters let XML views select only a certain type of data from normalized tables in your SQL Server database. Let's look at two related cases in which filters are useful for producing XML views. I'll begin with an outline of the scenarios, then show you a working example that includes both uses.

The first case selectively includes data that a mapping schema exposes. Let's call this process selective inclusion. Here's an example. Suppose that each customer record in your database includes a Boolean flag that indicates whether the customer has purchased a service contract. You need to expose an XML view of the table that contains only customers who have purchased a contract, so you use selective inclusion to map only these customers into the XML view.

The second case shows how you can use filters to select categorized information into different parts of an XML document. Extending the first example, assume that you have a database with normalized storage of all addresses in one table. The table stores typical address columns such as street address, city, state, country, and ZIP code but also includes an address type and a customer reference. The address type specifies whether the address is the ship to, bill to, or main corporate address. The customer reference in the addresses table uses a foreign key to the customers table to associate the address with a particular customer. Suppose you want to build an XML view of the addresses and customers tables that contains a list of customers. In this view, you want one customer element to represent each customer and each customer element to contain separate BillingAddress and ShippingAddress subelements, as the output in Figure 1 shows. To produce this XML view, you need to use filters.

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 Inetpubwww 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.