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:
- http://localhost/Dec2002/schema/catalog_map.xml/Writers
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.
- http://localhost/Dec2002/schema/catalog_map.xml/Bookstores
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.