Defining XML Views

If you need to extract data from your SQL Server database in a specific XML vocabulary, XML views are a good technology to choose. By XML vocabulary, I mean XML that corresponds to an agreed-upon formatting of the elements and attributes that make up an XML document. You define the vocabulary by building an XML Schema Definition (XSD) schema or using a prebuilt schema. You construct XML views by adding annotations (additional elements and attributes that locate data within your relational database) to any standard XSD schema. The annotations define the mapping between the XML and your relational database's schema. You can think of the resulting XML view as a virtual XML document that contains the data stored in your database. SQL Server 2000 and SQL Server 2000 Web Release 3 (SQLXML) support a variety of annotations for mapping data stored in SQL Server into an instance of an XML document that the schema describes.

In "Filtering Values in XML Views," October 2002, InstantDoc ID 26715, I showed you how to use the limit-value and limit-field annotations to build a filtered XML view that contains only the values that these two annotations specify. In this column, I show you how to use some additional annotations to obtain XML results from SQL Server in the vocabulary that the XSD schema specifies. Before you get started, consider the XSD schema that Listing 1 shows. The schema specifies an XML vocabulary that contains writers, publications, and bookstores. I use this schema example throughout the column to illustrate how you use annotations within a schema to build your XML view. The examples that follow map the elements and attributes defined in Listing 1's schema to the Pubs sample database included with SQL Server. When I constructed the schema, I specifically chose XML names that are different from the table names in the Pubs database to make the descriptions that follow less confusing.

First, let's look at Listing 1's schema in more detail. The schema defines three XSD complex types, which you can think of as structures or classes in programming languages or as database table structures. WriterType represents the writer's name and phone number, PublicationType names the writer's published books, and BookstoreType represents stores that sell publications. The schema also declares three nested levels of elements that use these types. The Writers element contains a list of Writer elements, and each Writer element contains a list of Publication subelements that represent books published by that writer. The Bookstores element contains a list of Bookstore elements, each of which contains a list of writers who reside in the same state as the bookstore. You can use Writers or Bookstores as the virtual document's root element. Now, let's look at some of the annotations that you can use to build an XML view from Listing 1's schema and the Pubs database.

Relation and Field

The most commonly used annotations are relation and field. They define how you map data from columns in a table or view in your SQL Server database to an element or attribute in your XML schema. Mapping a table name to an XML element creates one instance of the element for each table row. Mapping a column to an XML element or attribute inserts the value from that column as the element's content or the attribute's value in the XML view. I described relation and field in "Using XML Bulk Load to Load ADO-Generated XML Data," March 2002, InstantDoc ID 23699. However, before moving on, I want to describe default mapping as it affects the behavior of other annotations I describe later.

SQLXML applies a default mapping to every mapping schema. The default mapping automatically correlates names from your mapping schema with names in your SQL Server database. Complex types defined in your XSD schema map to tables of the same name in your database, and simple types map to columns in those tables. If your database schema were mapped one-to-one to the XSD schema that defines your XML view, you wouldn't need to add annotations. But this one-to-one mapping almost never occurs because table names typically are plural (e.g., Customers, Orders) and the corresponding XML entities are singular (e.g. Customer, Order). Usually, you need to use the relation and field annotations to specify mapping.

Relationship

Relationship is the next most commonly used annotation. You need relationship because relational databases are generally normalized—organized into a set of tables—and each table contains data for a specific entity. To map data from the tables into an XML document, you use the relationship annotation to specify the columns that correlate—or join—the data from multiple tables. I've discussed this topic in previous SQL Server Magazine columns, so I won't repeat all the details here. However, I want to show how you can chain relationship annotations to support many-to-many (M:N) relationships in the database.

In a relational database, you build M:N relationships between two entities by using a junction table containing two columns that are foreign keys to the related entities. For example, in the Pubs database, you use rows in the titleauthor table to relate authors to the titles they've written. In Listing 1's XSD schema, a Writer element containing a list of the Publication elements represents the same data. To build the structure in the XML document, you need a way to map through the titleauthor table to obtain all titles for a given author. You do this by chaining multiple relationship elements, as callout A in Listing 2 shows. Notice that the first relationship element specifies a mapping between the authors and titleauthor tables, and the second relationship element specifies a mapping between the titleauthor and titles tables. Together, these two relationship elements cause only titles associated with a given author to be included as Publication subelements of the Writer element in the XML view. You can apply this chaining technique for building a hierarchical XML view to an arbitrary number of tables in your database.

Key-Fields

The key-fields annotation specifies one or more columns as unique keys for the table or view that you map to an XML element or attribute by using the relation annotation or by using the default mapping. When SQLXML processes an XPath query against an XML view, SQLXML generates a FOR XML EXPLICIT query that SQL Server 2000 processes. FOR XML EXPLICIT queries are sensitive to the ordering of the rows they retrieve from the database that are subsequently processed into an XML result. When the columns you use to relate two entities specified on the relationship annotation don't reference unique keys for the database tables, key-fields ensures the correct ordering and a correct XML result. In fact, Microsoft recommends that you specify key-fields wherever you use the relation annotation to ensure a properly formed XML result. Let's look at an example that requires the key-fields annotation.

Listing 1's schema contains a BookstoreType complex type to represent stores that sell a writer's publications. The schema specifies that each Bookstore element contains a list of Writer elements. Now, let's say that you want to include Writer elements within the Bookstore element only when a writer resides in the same state as the bookstore. You need to use a relationship annotation to specify that SQLXML will use the state columns in their respective tables to correlate Writers and Bookstores. But notice that the state column isn't a key of either table. To obtain the proper result, you need to include the key-fields annotation on both the Bookstore and Writer elements, as callouts B and C in Listing 2 show.

Is-Constant

You might have noticed that I haven't yet discussed the is-constant annotation in Listing 2's schema. You use the is-constant annotation on an element or attribute that doesn't map to any table or column in the database—typically on container elements within an XML document. In this context, I use "container elements" for elements that group a collection of similar elements together. For example, Listing 1's container elements are Writers, which holds a list of Writer elements, and Bookstores, which holds a list of Bookstore elements. You'll remember that SQL Server applies default mapping to all schemas, so because the schema declares both Writers and Bookstores as complex types, the default mapping attempts to map the elements to the tables called Writers and Bookstores in the database. But these tables don't exist, so by inserting the is-constant annotation with the value true, you specify that the element or attribute needs to be included in the XML view but isn't generated from the database. Callout D in Listing 2 shows the is-constant annotation on the Writers and Bookstores elements.

Using the XML View to Run Queries

Now that you've built a mapping between Listing 1's schema and the Pubs database, you can query the XML view that this mapping defines to produce XML query results. The easiest way to execute queries using the XML view that Listing 2 defines is to create a SQLXML virtual directory that has XPath queries enabled. You begin this process by saving Listing 2's mapping schema to a file named catalog_map.xml on your computer. Choose a directory beneath the Inetpub directory to avoid permissions problems on the file when you execute a query that uses the schema. Now create a virtual directory by using the IIS Virtual Directory Management for SQLXML plug-in for Microsoft Management Console (MMC). Name the virtual directory Dec2002, set the local path to the directory where you saved the mapping schema in the previous step, enter the security parameters to access your Pubs database, then select the Allow XPath check box on the Settings tab. On the Virtual Names tab, create a virtual name called schema, select its type as schema, then set the path to (.), which will use the same directory you specified for the virtual directory.

After you configure the virtual directory, you can use Internet Explorer (IE) 5.0 or later to execute an XPath query against the XML view. Try the following queries by typing the URLs into IE's address bar:

This query returns an XML document containing a Writer element for each row in the authors table. Each Writer element includes Publication subelements for each title in the titles table corresponding to the author.

This query returns an XML document containing Bookstore elements for each row in the stores table. Each store contains Writer elements for rows in the authors table only where the author resides in the same state as the store.

In both URLs, Dec2002 refers to the virtual directory you created, and schema/catalog_map.xml refers to the mapping schema you saved from Listing 2. The remainder of the URL is the XPath query that is executed on the XML view. In an upcoming column, I'll show you how to use more annotations. In the meantime, have fun experimenting with the mapping schema from Listing 2 by modifying the annotations or running different XPath queries.