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.