| Downloads |
|---|
| 22325.zip |
Editor's Note: Send your XML questions to Rich Rollman at [email protected].
Can I use the new XML schemas with SQL Server 2000 to retrieve data? If so, how can I migrate my existing XML-Data schema to the new XML schemas?
Microsoft's XML for SQL Server 2000 Web Release 2 (WR2) Beta 2 at http://www.microsoft.com/sql/downloads/default.asp can help you solve your problem. An important feature of WR2 is its support for XML schemas through the XML Schema Definition (XSD) language. Let's look at an example that uses an XML Data Reduced (XDR) schema, Microsoft's original XML-based schema language, to query SQL Server 2000, then modify the example so that it uses XSD instead.
Listing 2 shows the XDR mapping schema that maps an XML vocabulary to the Pubs database. (The vocabulary represents a set of authors and the titles of their publications.) The XDR schema uses sql:relation annotations to define the mapping between the author and title elements to rows in the authors and titles tables in the database. Default mappings exist between the attributes defined in the schema and the table columns. The sql:relationship annotations specify how the titles table relates to the authors table. (You can think of the sql:relationship annotation as the outer join that retrieves all authors with their associated titles.) By using this XDR mapping schema in conjunction with an XPath query, you can obtain an XML result from SQL Server 2000. For example, the XPath query '/author' returns all authors in the database, and each author includes a list of the author's publications. Listing 3 shows an XML template that you can use with the SQL XML Internet Server API (ISAPI) DLL to execute the query. Listing 4 shows the truncated result of Listing 3's query.
The process of converting an XDR schema to an XSD schema varies according to the complexity of the XDR schema. Microsoft has attempted to ease this potentially difficult process by providing an Extensible Style Language-Transformations (XSLT) style sheet to automatically convert an XDR schema to XSD. At press time, a beta version of the style sheet was available for download at http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/539/msdncompositedoc.xml. This style sheet is particularly useful when the XDR is large and contains many element and attribute definitions. However, for this article's simple example, I performed a basic manual transformation. Listing 5 shows the XSD schema that describes one possible XML vocabulary equivalent to the vocabulary that the XDR schema in Listing 2 describes. (Note that you can construct many different schemas to describe the same XML vocabulary.)
Listing 5 shows the XSD mapping schema that contains annotations to map the elements and attributes to columns in the database. The annotations that the XSD mapping schema uses are identical to those that the XDR mapping schema uses with one exception: Because XSD schemas don't allow arbitrary elements within an xsd:element, you must wrap the sql:relationship tag inside an appinfo tag nested within an annotation tag. Containment inside an annotation tag is the established location for additional data in XSD schemas. The sql:relationship tag also uses different attribute names within an XSD schema. In XDR schemas, sql:relationship uses the key, key-relation, foreign key, and foreign-relation attributes to specify a relationship between two database tables. Compare XSD schemas, where the names of the attributes on the sql:relationship tag are changed to parent-key, parent, child-key, and child attributes, respectively. Although the attribute names differ, the functionality that sql:relationship provides is identical in both schemas. Listings 2, 3, 5, and 6 illustrate the common functionality. The XSD mapping schema in Listing 5 provides an identical mapping to the XDR schema that Listing 2 shows. You can query the XSD mapping schema by using the identical XPath query, '/author', from the XDR example and achieve Listing 3's result. Listing 6 shows the modified template, including a reference to the XSD mapping schema, that you use to execute the query. Note that in Listing 6, the only change from the schema in Listing 3 is the value of the mapping-schema attribute.
Just as you can represent one XML document with many different XML schemas, you can also specify a mapping schema's annotations in several different ways. You can find details about the alternative mapping schema constructs in WR2.