Mapping Schemas

In most organizations, the business data that needs to be included in documents such as catalogs, invoices, or purchase orders is stored in a database. One of the challenges in building an integrated system that uses XML to exchange business data is extracting the data from the database in the required XML format. Of course, we’ve already seen that SQL Server 2000 gives you a number of ways to extract data as XML, and we could use any of these ways to create an XML document. However, if we have an XML schema already defined for our business document, wouldn’t it be simpler just to map the elements and attributes in the schema to tables and columns in the database?

To help you accomplish this task, SQL Server supports the use of mapping schemas. A mapping schema is simply an XML schema with which you retrieve an XML fragment containing the appropriate data from the database. You can make use of default mapping, in which elements in your XDR schema map to same-name tables and attributes map to same-name columns, or you can add annotations to your schema to indicate the data in the database that each element or attribute in the schema corresponds to.

Using Default Mappings

The simplest way to use a schema to retrieve XML data is to use the default mapping assumed by SQL Server. When you specify a mapping schema from a client application, SQL Server assumes that each element maps to a table of the same name and that each attribute or subelement maps to a column of that name in the table. For example, consider the Products table in the Northwind database. The table contains several columns, including ProductID, ProductName , and UnitPrice. You could use the following schema to retrieve an XML fragment representing the data in those columns. (For simplicity, no data types are defined in this schema.) Later in this chapter, I’ll explain how to use a schema to retrieve XML data from a database.

 <?xml version="1.0"?> <Schema name="NWProductSchema"     xmlns="urn:schemas-microsoft-com:xml-data">     <ElementType name="Products">         <AttributeType name="ProductID"/>         <AttributeType name="ProductName"/>         <AttributeType name="UnitPrice"/>         <attribute type="ProductID"/>         <attribute type="ProductName"/>         <attribute type="UnitPrice"/>     </ElementType> </Schema> 

This schema is saved as NWProductSchema.xml in the Demos\Chaper6\Schemas folder on the companion CD. The XML fragment returned using this schema would resemble the following XML:

 <Products Product ProductName="Chai" UnitPrice="18"/> <Products Product ProductName="Chang" UnitPrice="19"/> <Products Product ProductName="Aniseed Syrup" UnitPrice="10"/>  

As you can see, each column is mapped in an attribute-centric fashion. You can use an element-centric mapping for one or more columns by declaring the content attribute for an element in the schema with the textOnly value, as shown in this example:

 <?xml version="1.0"?> <Schema name="NWProductSchemaElts"     xmlns="urn:schemas-microsoft-com:xml-data">     <ElementType name="ProductName" content="textOnly"/>     <ElementType name="UnitPrice" content="textOnly"/>     <ElementType name="Products">         <AttributeType name="ProductID"/>         <attribute type="ProductID"/>         <element type="ProductName"/>         <element type="UnitPrice"/>     </ElementType> </Schema> 

This schema is saved as NWProductSchemaElts in the Demos\Chapter6\Schemas folder on the companion CD. This schema would produce an XML fragment with the following format:

 <Products Product>     <ProductName>Chai</ProductName>     <UnitPrice>18</UnitPrice> </Products> <Products Product>     <ProductName>Chang</ProductName>     <UnitPrice>19</UnitPrice> </Products> <Products Product>     <ProductName>Aniseed Syrup</ProductName>     <UnitPrice>10</UnitPrice> </Products>  

The default mapping requires no special changes to XDR schemas, but it can be used only when all the elements and attributes in a schema map exactly to tables and columns in the database. In most real business scenarios, you won’t find this precision. To map more complex business documents to database entities, you need to explicitly define a custom mapping.

Using Annotations to Map Data

To create custom mappings between elements or attributes in a schema and tables or columns in a database, you must add annotations to the schema. The annotations themselves are defined in the XML-SQL namespace, and so a reference to that namespace must be included in the schema.

Mapping an XML Document to a Single Table

Once you’ve referenced the namespace, you can use the relation attribute to map an element to a table and the field attribute to map an attribute or element to a column. For example, you could use the following schema to create custom mappings to the Products table in the Northwind database:

 <?xml version="1.0"?> <Schema name="NWProductSchemaAnn"     xmlns="urn:schemas-microsoft-com:xml-data"     xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <ElementType name="Description"/>     <ElementType name="Price"/>     <ElementType name="Product" sql:relation="Products">         <AttributeType name="ProductCode"/>         <attribute type="ProductCode" sql:field="ProductID"/>         <element type="Description" sql:field="ProductName"/>         <element type="Price" sql:field="UnitPrice"/>     </ElementType> </Schema> 

You can find this schema file in the Demos\Chapter6\Schemas folder on the companion CD in a file named ProductsSchemaAnn.xml. In this schema, the Product element is mapped to the Products table. The ProductCode attribute, the Description element, and the Price element are mapped to the ProductID, ProductName, and UnitPrice columns, respectively. This schema would return the following XML fragment:

 <Product ProductCode="1">     <Description>Chai</Description>     <Price>18</Price> </Product> <Product ProductCode="2">     <Description>Chang</Description>     <Price>19</Price> </Product> <Product ProductCode="3">     <Description>Aniseed Syrup</Description>     <Price>10</Price> </Product>  

Mapping XML Data to Multiple Tables

In reality, most schemas need to be more complex than the examples we’ve seen so far because most business data is stored in multiple tables. So the mappings in our schema need to reflect that. For example, the Northwind catalog schema needs to include data from the Categories table as well as the Products table.

You could use a SQL Server view to consolidate the data from multiple tables and map the schema to the view. However, this approach adds an unnecessary layer of abstraction and, more seriously, could prevent us from using the schema with tools such as the Bulk Load component or updategrams to insert or update data.

You can use a relationship element to create a mapping schema that references data in multiple tables. This annotation can be used to return elements containing attributes from multiple tables or to create a hierarchical representation of the relationships in the database. The relationship element contains four attributes with which you create a link between database tables; this mechanism works in a similar way to a JOIN clause in a Transact-SQL SELECT statement. You use the key-relation attribute to identify the table in the relationship in which the primary key used to join the tables is defined. The key attribute identifies the actual primary key field. You use the foreign-relation attribute to identify the table in the relationship containing the foreign key, and you use the foreign-key attribute to identify the foreign key field.

In the case of the Northwind catalog, the Categories and Products tables can be joined using the CategoryID field. Each row in the Categories table has a unique CategoryID field that is defined as a foreign key in the Products table to identify the category of each product. You could use the following annotated schema to define an XML catalog document in which the products are arranged hierarchically by category:

 <?xml version="1.0"?> <Schema name="NWCatalogSchemaAnn"     xmlns="urn:schemas-microsoft-com:xml-data"      xmlns:sql="urn:schemas-microsoft-com:xml-sql">        <ElementType name="Description"/>     <ElementType name="Price"/>     <ElementType name="Product" sql:relation="Products">         <AttributeType name="ProductCode"/>         <attribute type="ProductCode" sql:field="ProductID"/>         <element type="Description" sql:field="ProductName"/>         <element type="Price" sql:field="UnitPrice"/>     </ElementType>     <ElementType name="Category" sql:relation="Categories">         <AttributeType name="CategoryID"/>         <AttributeType name="CategoryName"/>         <attribute type="CategoryID" sql:field="CategoryID"/>         <attribute type="CategoryName" sql:field="CategoryName"/>         <element type="Product">             <sql:relationship key-relation="Categories"                 key="CategoryID"                 foreign-relation="Products"                 foreign-key="CategoryID"/>         </element>     </ElementType> </Schema> 

This schema file is also available in the Demos\Chapter6\Schemas folder on the companion CD in a file named CatalogSchemaAnn.xml. In this schema, the relationship element joins the Categories and Products tables, creating an XML hierarchy of Category elements containing Product elements, as shown in the following XML fragment:

 <Category Category CategoryName="Beverages">     <Product ProductCode="1">         <Description>Chai</Description>         <Price>18</Price>     </Product>     <Product ProductCode="2">         <Description>Chang</Description>         <Price>19</Price>     </Product>      </Category> <Category Category CategoryName="Condiments">     <Product ProductCode="3">         <Description>Aniseed Syrup</Description>         <Price>10</Price>     </Product>      </Category>  

As you can see, this code is much closer to the catalog schema we defined at the beginning of this chapter. The only difference is that the root element (Catalog) hasn’t been defined in the annotated schema because it doesn’t map to any fields or tables in the database. We’ll see how this problem can be overcome later in the chapter, but now we need to see how we might use a mapping schema from a client application.