Mapping Schemas

for RuBoard

We discussed XML schemas in Chapter 12, so I won't go back into them here. Suffice it to say that XML schemas are XML documents that define the type of data that other XML documents may contain. They are a replacement for the old DTD technology originally used for that purpose, and are easier to use and more flexible because they consist of XML themselves .

By their very nature, schemas also define document exchange formats. Because they define what a document may and may not contain, companies wishing to exchange XML data need to agree on a common schema definition to do so. XML schemas allow companies with disparate business needs and cultures to exchange data seamlessly.

The syntax for XML Schemas is still in the process of being approved as I write this (January 2001). The W3C is currently considering input from several sources and will likely publish a standard later this year. For now, Microsoft, along with several other companies, has proposed that a subset of the W3C XML-Data syntax be used to define schemas for document interchange. XML-Data Reduced (XDR) is an XML-Data subset that can be used to define schemas. SQL Server and Microsoft's other XML-enabled products support XDR schemas.

It's likely that the final syntax for XML Schemas will differ semantically from XDR. Microsoft has announced that it will support whatever the final syntax is, so you should keep your eyes open for changes in the technology.

Listing 14-1 presents an example of an XDR schema:

Listing 14-1 ProductsCat.xdr.

 <?xml version="1.0"?> <Schema name="NorthwindProducts"     xmlns="urn:schemas-microsoft-com:xml-data"     xmlns:dt="  urn:schemas-microsoft-com:datatypes  ">     <ElementType name="Description" dt:type="string"/>     <ElementType name="Price" dt:type="fixed.19.4"/>     <ElementType name="Product" model="closed">         <AttributeType name="ProductCode" dt:type="string"/>         <attribute type="ProductCode" required="yes"/>         <element type="Description" minOccurs="1" maxOccurs="1"/>         <element type="Price" minOccurs="1" maxOccurs="1"/>     </ElementType>     <ElementType name="Category" model="closed">         <AttributeType name="CategoryID" dt:type="string"/>         <AttributeType name="CategoryName" dt:type="string"/>         <attribute type="CategoryID" required="yes"/>         <attribute type="CategoryName" required="yes"/>         <element type="Product" minOccurs="1" maxOccurs="*"/>     </ElementType>     <ElementType name="Catalog" model="closed">         <element type="Category" minOccurs="1" maxOccurs="1"/>     </ElementType> </Schema> 

This schema defines what a product catalog might look like (we're using the sample tables and data from the Northwind database). It uses the datatypes namespace (in bold type) to define the valid data types for elements and attributes in the document. Every place you see dt:, it is a reference to the datatypes namespace. The use of the closed model guarantees that only elements that exist in the schema can be used in a document based on it.

Listing 14-2 presents an XML document that uses ProductsCat.xdr:

Listing 14-2 ProductsCat.xml.

 <?xml version="1.0"?> <Catalog xmlns=     "x-schema:http://localhost/ProductsCat.xdr">     <Category CategoryID="1" CategoryName="Beverages">         <Product ProductCode="1">             <Description>Chai</Description>             <Price>18</Price>         </Product>         <Product ProductCode="2">             <Description>Chang</Description>             <Price>19</Price>         </Product>     </Category>     <Category CategoryID="2" CategoryName="Condiments">         <Product ProductCode="3">             <Description>Aniseed Syrup</Description>             <Price>10</Price>         </Product>     </Category> </Catalog> 

If you copy both of these files to the root folder of your Web server and type the following URL

http://localhost/ProductsCat.xml

into your browser, you should see this output:

 <?xml version="1.0" ?> - <Catalog xmlns="  x-schema:http://localhost/ProductsCat   .   xdr  "> - <Category CategoryID="  1  " CategoryName="  Beverages  ">          _ <Product ProductCode="  1  ">               <Description>  Chai  </Description>               <Price>  18  </Price>        </Product>        - <Product ProductCode="  2  ">               <Description>  Chang  </Description>               <Price>  19  </Price>        </Product> </Category> _ <Category CategoryID="  2  " CategoryName="  Condiments  ">        _ <Product ProductCode="  3  ">               <Description>  Aniseed Syrup  </Description>               <Price>  10  </Price>        </Product> </Category> </Catalog> 

You've already seen that XML data can be extracted and formatted in a variety of ways. One of the challenges in exchanging data using XML is this flexibility. However, mapping schemas help us overcome this challenge. They allow us to return data from a database in a particular format. They allow us to map columns and tables to attributes and elements.

The easiest way to use a schema to map data returned by SQL Server into XML entities is to assume the default mapping returned by SQL Server. That is, every table becomes an element, and every column becomes an attribute. Here's a schema that does this:

 <?xml version="1.0"?> <Schema name="customers"     xmlns="urn:schemas-microsoft-com:xml-data">     <ElementType name="Customers">         <AttributeType name="CustomerId"/>         <AttributeType name="CompanyName"/>     </ElementType> </Schema> 

Here we retrieve only two columns, each of them from the Customers table. If you store this schema under the schemas virtual name that we created earlier and retrieve it via a URL, you'll see a simple XML document with the data from the Customers table in an attribute-centric mapping, as we've seen several times throughout this chapter.

You use XML-Data's ElementType to map a column in a table to an element in the resulting XML document. Here's an example:

 <?xml version="1.0"?> <Schema name="customers"     xmlns="urn:schemas-microsoft-com:xml-data">     <ElementType name="Customers">         <ElementType name="CustomerId" content="textOnly"/>         <ElementType name="CompanyName" content="textOnly"/>     </ElementType> </Schema> 

Note the use of the content="textOnly" attribute with each element. In conjunction with the ElementType element, this maps a column to an element in the resulting XML document. Note that the elements corresponding to each column are actually empty. They contain attributes only; no data.

Annotated Schemas

An annotated schema is a mapping schema with special annotations (from the XML-SQL namespace) that link elements and attributes with tables and columns. Here's some code that uses our familiar Customer list example:

 <?xml version="1.0"?> <Schema name="customers"     xmlns="urn:schemas-microsoft-com:xml-data">     xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <ElementType name="Customer" sql:relation="Customers">         <AttributeType name="CustomerNumber" sql:field="CustomerId"/>         <AttributeType name="Name" sql:field="CompanyName"/>     </ElementType> </Schema> 

First, note the reference to the XML-SQL namespace at the top of the schema. Because we'll be referencing it later in the schema, we begin with a reference to XML-SQL so that we can use the sql: namespace shorthand for it later. Next, notice the sql:relation attribute of the first ElementType element. It establishes that the Customer element in the resulting document relates to the Customers table in the database referenced by the virtual directory. This allows you to call the element whatever you want. Last, notice the sql:field references. They establish, for example, that the CustomerNumber element refers to the CustomerId column in the referenced table. Things get more complicated when multiple tables are involved, but you get the picturean annotated schema allows you to establish granular mappings between document entities and database entities.

for RuBoard