Let SQL Server Do the Work
Our workhorse here is the good, old FOR XML clause, but we're going to use it in an unorthodox, but perfectly legal, fashion. The FOR XML clause takes some optional arguments after the mode argument (RAW, AUTO, or EXPLICIT) discussed earlier in the chapter. You can add the argument XMLDATA after the mode argument, and SQL Server will return some additional information along with your XML data set. This argument tells SQL Server to return not only the result set formatted in XML, but also an XDR schema that defines the structure of XML data returned. How convenient!
Executing a query that returns fields of the tables that you want in your XDR schema is one way to get a good start on building an XDR schema for your XML view. Let's look at how we could use this off-label use of XMLDATA to generate most, if not all, of our XDR schema automatically.
For our example we simply want a few fields from a single table. This query should give us what we need:
SELECT OrderID, CustomerID, OrderDate, ShipCity, Freight FROM Orders WHERE OrderID = 0 FOR XML AUTO, XMLDATA
In this case, I don't care if any actual rows of data are returned because I'm interested only in the XDR schema that SQL Server will generate. So I've specified a value for the OrderID in the WHERE clause that I know won't return any rows.
SQL Server returns the following results:
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="Orders" content="empty" model="closed">
<AttributeType name="OrderID" dt:type="i4" />
<AttributeType name="CustomerID" dt:type="string" />
<AttributeType name="OrderDate" dt:type="dateTime" />
<AttributeType name="ShipCity" dt:type="string" />
<AttributeType name="Freight" dt:type="fixed.14.4" />
<attribute type="OrderID" />
<attribute type="CustomerID" />
<attribute type="OrderDate" />
<attribute type="ShipCity" />
<attribute type="Freight" />
</ElementType>
</Schema>
Look at that! By executing a simple query, we're almost done writing the entire XML view, and we wrote only one line of SQL code to do it.
The schema definition is pretty straightforward, but since this is the first time we've looked at an XDR schema, I want to highlight a few aspects. The Orders table is defined as an XML element, and its fields are defined as attributes of that element. This sounds very similar to the way SQL Server formats results when you execute queries using the AUTO mode of the FOR XML clause. The data types of the columns are also defined. The string and dateTime types are straightforward enough. The i4 data type represents a 4-byte integer value. The data type fixed.14.4 represents a decimal value that can have no more than 14 digits to the left of the decimal and 4 digits to the right.
If this query had returned data, we could strip off all of the XML data returned in the query in a simple text editor. We want only the XDR schema. Depending on how we executed the query, the results might not be formatted very well. I executed this query by URL encoding it and sending it in a URL. Internet Explorer returned the results and formatted them nicely. If I had executed the query in the SQL Server Query Analyzer tool, the results would have been returned as a string with no linefeeds or indentation.
We can clean up the formatting in several ways. We can do it by hand, although this method is time-consuming and about as interesting as mowing the lawn by plucking out each blade of grass one by one. Another way is to write a small program or script, using a language such as Visual Basic or JavaScript, that inserts line feeds and white space in certain places to format the document. This method can also be reused when you generate other schemas. Though by far the simplest method I've found is to save the schema to a text file and then open that file in an XML editor, such as XML Spy. Then, simply save the file from the XML editor back into the same file, and the editor rearranges the document into a nice, readable format with line feeds and white space in the appropriate spots. Choose whichever method you feel more comfortable with.
Note
I want to bring to your attention one important note about the XMLDATA argument. Microsoft warns that generating XDR schemas with XMLDATA uses more resources on the server than simply returning data in XML format. So you probably should avoid using it to generate XDR schemas at runtime in your applications. But, when used during development as a way to save time in defining XDR schemas, it works great and shouldn't impact the performance of your server at all.
Now that you have an XDR schema, the third and final step in the process for creating an XML view is to annotate the XDR schema and map the schema to relational database elements. Again, you can do this by hand, or you can do it the easy way. Microsoft provides a tool, called XML View Mapper, that lets you graphically map an XDR schema that has not been annotated to elements in a relational database schema. The output is an annotated version of the XDR schema. At the time of this writing, this tool did not ship with SQL Server 2000, but it was available as a free download from Microsoft's Web site.
Note
You can find XML View Mapper on Microsoft's MSDN Web site at http://msdn.microsoft.com/
As you can see, the SQL Server XML View Mapper is a helpful little tool. It's also multifunctional. Not only can it help you define your annotated XDR schemas, but it also has utilities you can use to derive an XDR schema from an XML document, convert a DTD to an XDR schema, and do a few other useful functions. Figure 8-4 shows a screen shot of the XML View Mapper at work mapping the Orders table in the Northwind database to the XDR schema that was generated previously.
Figure 8-4. The SQL Server XML View Mapper
After I'm finished mapping, I can save the result to a text file and generate the annotated XDR schema.
<?xml version="1.0" ?>
<!-- Generated by XMLMapper.exe XDR Publisher -->
<Schema name="Schema1"
xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="Orders"
model="closed"
content="empty"
order="many" >
<AttributeType name="OrderID"
dt:type="i4" >
</AttributeType>
<AttributeType name="CustomerID"
dt:type="string" >
</AttributeType>
<AttributeType name="OrderDate"
dt:type="dateTime" >
</AttributeType>
<AttributeType name="ShipCity"
dt:type="string" >
</AttributeType>
<AttributeType name="Freight"
dt:type="fixed.14.4" >
</AttributeType>
<attribute type="OrderID"
required="no" >
</attribute>
<attribute type="CustomerID"
required="no" >
</attribute>
<attribute type="OrderDate"
required="no" >
</attribute>
<attribute type="ShipCity"
required="no" >
</attribute>
<attribute type="Freight"
required="no" >
</attribute>
</ElementType>
</Schema>
Aside from the slightly different format of the document, there aren't too many changes from the original XDR Schema. The first significant change is the addition of an attribute of order with a value of many to the ElementType definition of the Orders element type. The order attribute defines in what order the elements of this element type may appear in a document based on this schema. A value of many indicates that the elements can occur in any order. Also notice that each of the attributes has a value of no for the require attributes. This means that these attributes do not have to be specified for any given Order element. However, this value has also been set for the OrderID column. I don't want this value to be optional, nor do I want the CustomerID value to be optional, so I'll change them to be required.
<attribute type="OrderID"
required="yes" >
</attribute>
<attribute type="CustomerID"
required="yes" >
</attribute>
While the process of defining XDR Schemas and mapping them to a relational database using the XML View Mapper tool is straightforward, it is still somewhat clunky. I hope that in the future Microsoft will make the process of mapping database schemas to XML documents an even simpler, more integrated process.