Defining XML Views
XDR isn't the most pleasant language to work with when coding by hand. And once you have defined an XDR schema, you're not finished defining your XML view. You still have to annotate it in order to map the XDR schema to the relational schema. In addition, it is possible that XDR may not become an approved W3C standard. While no one can say for sure, Microsoft may drop support for XDR in favor of the XML Schema standard.
To help save you time both learning XDR and applying it, I'm going to show you two things that will help you define XML views more productively with less XDR coding. First, I'll demonstrate a SQL query trick that you can use to generate part or all of an XDR schema automatically. Then I'll show you the XML View Mapper tool that Microsoft built to help developers visually annotate XDR schemas without coding.
There are three steps to defining an XML view:
1. |
Identify the elements of your database schema that you want to expose in the view. |
2. |
Build an XDR schema that represents the relational schema, including data type information and other properties, such as key columns. |
3. |
Annotate the XDR schema with mapping information. |
Let's dive into an example to show how to define XML views, as well as how to do it productively rather than simply doing everything by hand. Why do everything by hand when tools and tricks that can help us work more efficiently are available? After all, the more efficiently we work, the more time we'll have to catch up on Dilbert.
If you are the type that enjoys the palpable pleasure of hacking out every last character of code, you'll find that annotated XDR schemas are well documented in the SQL Server Books Online documentation.
Let's take a deeper look at the Northwind database that ships with SQL Server. Figure 8-3 shows a partial entity relationship diagram (which alert readers should recognize as a variant of our data model diagrams from Chapter 4) of the database schema representing Customers, Orders, and the relationship between them.
Figure 8-3. Partial representation of the Northwind database schema
For this example, we'll create an XML view that represents the Orders table. Notice that the Orders table contains a reference to the Customer that placed the order. We'll see shortly how that is represented in an annotated XDR schema.
To keep the example brief and focus on the important aspects, we'll represent only the following fields in the view:
OrderID
CustomerID
OrderDate
ShipCity
Freight
Now that we've identified the elements we want to represent, we move on to step two in the process and define an XDR schema that models them. We could do this by hand, and SQL Server Books Online can instruct you in all of the fine details of how to build the most exquisite XDR schemas known to man. Or, we can do it the easy way and finish in five minutes.
If we were building an XDR schema by hand, we would essentially be building an XML document because XDR schemas, unlike their cousins, DTDs, are themselves XML documents. They just happen to represent the structure of other XML documents. However, we're going to save time and use a simple coding trick to define our schema quickly and accurately.