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.
Prev. page
[1]
