Advanced Annotations

The relation, field, and relationship annotations allow you to map data in an XML schema to data in your SQL Server database. However, you can use advanced annotations such as is-constant, map-field, and target-namespace in a schema to help further define the relationship between the schema and the database. These annotations can be particularly useful when you need to map an existing XML schema to data in your database. In this section, we’ll examine these advanced annotations.

Defining Constants in a Schema

One of the common issues that developers face when trying to map XML schemas to data in a database is what to do about XML elements or attributes that have no corresponding table or column in the database. For example, suppose Northwind Traders and other suppliers decided to agree on a standard XML schema representing an invoice document. This document could be sent to customers as a request for payment when an order has been shipped. The suppliers could use the following XML schema to define an invoice:

 <?xml version="1.0" ?> <Schema name="NWInvoiceSchema"     xmlns="urn:schemas-microsoft-com:xml-data">     <ElementType name="Item">         <AttributeType name="ProductID"/>         <AttributeType name="Quantity"/>         <AttributeType name="Price"/>         <AttributeType name="Discount"/>         <attribute type="ProductID" required="yes"/>         <attribute type="Quantity" required="yes"/>         <attribute type="Price" required="yes"/>         <attribute type="Discount" required="no"/>     </ElementType>     <ElementType name="LineItems">         <element type="Item" minOccurs="1" maxOccurs="*"/>     </ElementType>     <ElementType name="InvoiceNo"/>     <ElementType name="InvoiceDate"/>     <ElementType name="ShippingCharge"/>     <ElementType name="CustomerID"/>     <ElementType name="Invoice">         <element type="InvoiceNo" minOccurs="1" maxOccurs="1"/>         <element type="InvoiceDate" minOccurs="1" maxOccurs="1"/>         <element type="CustomerID" minOccurs="1" maxOccurs="1"/>         <element type="LineItems" minOccurs="1" maxOccurs="1"/>         <element type="ShippingCharge" minOccurs="1" maxOccurs="1"/>     </ElementType> </Schema> 

(This code is also available in the Demos\Chapter6\Schemas folder on the companion CD in a file named InvoiceSchema.xml.) This schema contains elements and attributes that correspond to data in the Northwind database, but it also contains some data that can’t be mapped. The following XML document shows what Northwind Traders invoices should look like based on this schema:

 <?xml version="1.0"?> <Invoice xmlns="x-schema:InvoiceSchema.xml">     <InvoiceNo>10952</InvoiceNo>     <InvoiceDate>16/03/1998</InvoiceDate>     <CustomerID>ALFKI</CustomerID>      <LineItems>         <Item Product             Quantity="16"             Price="25"             Discount="5"/>         <Item Product             Quantity="2"             Price="45.60"/>     </LineItems>     <ShippingCharge>40.42</ShippingCharge> </Invoice> 

Mapping data in the Northwind database using the schema above presents a challenge. It is clear that the Invoice element can be mapped to the Orders table and that the Items element can be mapped to the Order Details table. However, there’s no matching database entity for the LineItems element used to contain the items in the invoice.

To handle this situation, you can specify LineItems as a constantelement. Constant elements appear in the XML fragments retrieved using the schema but don’t map to data in the database. You can declare a constant element in a schema using the is-constant annotation. This annotation takes a Boolean value. The invoice schema with the necessary annotations to map data to the Northwind database is shown here:

 <?xml version="1.0" ?> <Schema name="NWInvoiceSchemaAnn"     xmlns="urn:schemas-microsoft-com:xml-data"      xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <ElementType name="Item" sql:relation="[Order Details]">         <AttributeType name="ProductID"/>         <AttributeType name="Quantity"/>         <AttributeType name="Price"/>         <AttributeType name="Discount"/>         <attribute type="ProductID" required="yes" sql:field="ProductID"/>         <attribute type="Quantity" required="yes" sql:field="Quantity"/>         <attribute type="Price" required="yes" sql:field="UnitPrice"/>         <attribute type="Discount" required="no" sql:field="Discount"/>     </ElementType>     <ElementType name="LineItems" sql:is-constant="1">         <element type="Item" minOccurs="1" maxOccurs="*">             <sql:relationship key-relation="Orders"                 key="OrderID"                 foreign-relation="[Order Details]"                 foreign-key="OrderID"/>         </element>     </ElementType>     <ElementType name="InvoiceNo"/>     <ElementType name="InvoiceDate"/>     <ElementType name="ShippingCharge"/>     <ElementType name="CustomerID"/>     <ElementType name="Invoice" sql:relation="Orders">         <element type="InvoiceNo" minOccurs="1" maxOccurs="1"             sql:field="OrderID"/>         <element type="InvoiceDate" minOccurs="1" maxOccurs="1"             sql:field="OrderDate"/>         <element type="CustomerID" minOccurs="1" maxOccurs="1"             sql:field="CustomerID"/>         <element type="LineItems" minOccurs="1" maxOccurs="1"/>         <element type="ShippingCharge" minOccurs="1" maxOccurs="1"             sql:field="Freight"/>     </ElementType> </Schema> 

This schema is also available in the Demos\Chapter6\Schemas folder on the companion CD in a file named InvoiceSchemaAnn.xml. You can view the data it returns by opening the shortcut named Invoice Schema in the Demos\Chapter 6 folder. Notice in this example that the is-constant annotation is used to denote the LineItems element as a constant, thus allowing it to appear in the XML document without mapping it to data in the database. You could now use this schema to retrieve an invoice for a specific order by including the order ID in the XPath expression used to extract the data. For example, you could use the XPath Invoice[InvoiceNo=‘10952’] to generate the invoice for order number 10952.

An interesting side-effect of this approach is that since only a single invoice is returned, the XML fragment returned is in fact a well-formed XML document and no root element needs to be specified by the client application. Of course, the root element for most business documents is actually defined in the schema. For example, the CatalogSchema.xml schema described earlier in the chapter contained this declaration:

 <ElementType name="Catalog" model="closed">     <element type="Category" minOccurs="1" maxOccurs="1"/> </ElementType> 

You can use the is-constant annotation to return the root element declared in the schema with the XML fragment, as shown here:

 <ElementType name="Catalog" sql:is-constant="1">     <element type="Category" minOccurs="1" maxOccurs="1"/> </ElementType> 

You can find this schema in the Demos\Chapter6\Schemas folder on the companion CD in a file named NWCatalogZncRoot.xml. This schema would allow the entire catalog document to be returned to a client application if you use the XPath expression Catalog in your query. No root element would need to be supplied by the client application because the root is returned with the data.

Excluding Fields

Sometimes an element exists in the schema that you don’t want to appear in the resulting XML document. For example, suppose the suppliers agree to include an optional field in the invoice schema to specify the particular branch an item was bought from. This might be a perfectly sensible piece of information for suppliers who have branches in multiple locations, but Northwind Traders doesn’t need it.

You can use the map-field annotation with a value of 0 to indicate that an optional element or attribute shouldn’t be retrieved. This strategy allows you to continue to use the same schema as your trading partners without having to map irrelevant data. The map-field annotation can be used with any attribute, or any element declared with a content attribute with the value of textOnly. For example, you could make the following modifications to the invoice schema, allowing suppliers with branches to specify a branch but also allowing Northwind invoices to omit the branch data:

 <ElementType name="Branch" content="textOnly"/> <ElementType name="Invoice" sql:relation="Orders">     <element type="InvoiceNo" minOccurs="1" maxOccurs="1"         sql:field="OrderID"/>     <element type="InvoiceDate" minOccurs="1" maxOccurs="1"         sql:field="OrderDate"/>     <element type="Branch" minOccurs="0" maxOccurs="1"         sql:map-field="0"/>     <element type="CustomerID" minOccurs="1" maxOccurs="1"         sql:field="CustomerID"/>     <element type="LineItems" minOccurs="1" maxOccurs="1"/>     <element type="ShippingCharge" minOccurs="1" maxOccurs="1"         sql:field="Freight"/> </ElementType> 

This schema is saved as InvoiceSchemaUnmapped.xml in the Demos\Chapter6\ Schemas folder. You can view the data it returns by opening the shortcut named Invoice With Unmapped Field in the Demos\Chapter6 folder on the companion CD.

Specifying Key Fields

So far, we’ve used the relationship annotation to produce nested XML hierarchies that represent the relationships between primary keys and foreign keys. We’ve assumed in all of the examples so far that the element on the primary key side of the relationship will contain the elements on the foreign key side of the relationship. For example, the Category element contains the Product element.

While this arrangement is certainly the more usual way to represent data, we sometimes might want to show a foreign key element that contains a primary key element. For example, suppose we wanted to create a product list schema in which each product is listed with a subelement showing the category that the product belongs to. In this case, the relationship is reversed and the foreign key element (Product) contains the primary key element (Category).

You might imagine that the following schema would produce the list we’re looking for:

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

This code is also available in the Demos\Chapter6\Schemas folder on the companion CD in a file named CatalogSchemaAnnRev.xml. If you use this schema to retrieve the product data, the XML fragment returned from the schema looks like this:

 <Product Product ProductName="Chai"/> <Product Product ProductName="Chang"/> <Product Product ProductName="Guaraná Fantástica"/> <Product Product ProductName="Sasquatch Ale"/> <Product Product ProductName="Steeleye Stout"/> <Product Product ProductName="Côte de Blaye"/> <Product Product ProductName="Chartreuse verte"/> <Product Product ProductName="Ipoh Coffee"/> <Product Product ProductName="Laughing Lumberjack Lager"/> <Product Product ProductName="Outback Lager"/> <Product Product ProductName="Rhönbräu Klosterbier"/> <Product Product ProductName="Lakkalikööri">     <Category CategoryName="Beverages"/>     <Category CategoryName="Beverages"/>     <Category CategoryName="Beverages"/>     <Category CategoryName="Beverages"/>     <Category CategoryName="Beverages"/>     <Category CategoryName="Beverages"/>     <Category CategoryName="Beverages"/>     <Category CategoryName="Beverages"/>     <Category CategoryName="Beverages"/>     <Category CategoryName="Beverages"/>     <Category CategoryName="Beverages"/>     <Category CategoryName="Beverages"/> </Product>  

As you can see, the nesting in the resulting XML fragment is incorrect. We have the wrong result because the CategoryID field has been interpreted as being the primary key of the Products table due to the relationship annotation in the Product element. To fix this problem, you must use the key-fields annotation to explicitly specify the primary key field of the Products table. For tables with composite primary keys, you list and separate with a space each field in the key. The key-fields annotation can be used to produce proper nesting in the results. You could make the following modification to the preceding schema to specify the key-fields annotation:

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

This schema is saved as ProductListSchema.xml in the Demos\Chapter6\Schemas folder on the companion CD. You can view the data it returns by opening the shortcut named Product List in the Demos\Chapter6 folder. This schema returns an XML document with the correct nesting, as you see here:

 <Product Product ProductName="Chai">     <Category CategoryName="Beverages"/> </Product> <Product Product ProductName="Chang">     <Category CategoryName="Beverages"/> </Product> <Product Product ProductName="Aniseed Syrup">     <Category CategoryName="Condiments"/> </Product>  

Although you don’t always need the key-fields annotation, it’s good practice to specify the primary key fields explicitly whenever you create schemas that contain relationships. Keeping to this discipline can help prevent hours of frustration trying to explain some unexpected results.

Filtering Values

You can use the limit-field and limit-value annotations to filter your results based on a particular column value in a related table. You might find this tactic useful for generating documents containing a subset of the data in your database tables. You must use limit-field on an element or attribute that has relationship specified. For example, suppose you required a product list that contained the category name and product name of each product. You could use the following schema:

 <?xml version="1.0" ?> <Schema xmlns="urn:schemas-microsoft-com:xml-data"     xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <ElementType name="StockItem" sql:relation="Categories"          sql:key-fields="CategoryID">         <AttributeType name="CategoryName"/>         <AttributeType name="ProductName"/>         <attribute type="CategoryName" sql:field="CategoryName"/>         <attribute type="ProductName" sql:relation="Products"              sql:field="ProductName">               <sql:relationship key-relation="Categories"                   key="CategoryID"                   foreign-relation="Products"                   foreign-key="CategoryID"/>          </attribute>     </ElementType> </Schema> 

This schema returns a list of all the products in the database. However, suppose you wanted to list only the products that haven’t been discontinued. To do this, you need to filter the result set to show only products with a Discontinued value of 0. You can achieve your properly filtered result set by adding the limit-field and limit-value annotations, as shown in this example:

 <?xml version="1.0" ?> <Schema xmlns="urn:schemas-microsoft-com:xml-data"     xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <ElementType name="StockItem" sql:relation="Categories"          sql:key-fields="CategoryID">         <AttributeType name="CategoryName"/>         <AttributeType name="ProductName"/>         <attribute type="CategoryName" sql:field="CategoryName"/>         <attribute type="ProductName" sql:relation="Products"             sql:field="ProductName"             sql:limit-field="Discontinued"             sql:limit-value="0">             <sql:relationship key-relation="Categories"                 key="CategoryID"                 foreign-relation="Products"                 foreign-key="CategoryID"/>         </attribute>     </ElementType> </Schema> 

This schema is saved as NonDiscontinued.xml in the Demos\Chaper6\Schemas folder on the companion CD. In this example, you use the limit-field annotation to define the name of the column in the foreign key table that you’re using to filter the results. The limit-value annotation defines the required value. The preceding schema will return only products with a Discontinued value of 0.

Using ID, IDREF, and IDREFS Annotations

You can use the XDR data types ID, IDREF, and IDREFS to create a relational schema using XML. For example, suppose the warehouse employees at Northwind Traders require a picking list document. This document should list an order with details of the products that the supplier needs to dispatch. It’s probably easier to retrieve the items in order if the stock in the warehouse is arranged physically by category, so the picking list needs to contain category information for each product.

Rather than repeat category information for each product in the picking list, the application developer decides that it’s more efficient to list all the categories, assign a unique ID to each one, and then use the ID to reference the appropriate category for each product. This procedure resembles the way you use primary and foreign keys in a relational database with one (fairly major) deviation: ID values in an XML document must be completely unique within the document. Unlike a database in which two primary key fields (for example ProductID and OrderID) potentially have the same value, you can’t have two identical ID fields in an XML document, even if they’re associated with different attributes. To help solve this problem, you can employ the id-prefix annotation to add a unique prefix to an ID field. By this means, you can use ID fields even in a document containing two or more elements with identical values.

For example, you could use the following schema to define the picking list document:

 <?xml version="1.0" ?> <Schema xmlns="urn:schemas-microsoft-com:xml-data"     xmlns:dt="urn:schemas-microsoft-com:datatypes"     xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <ElementType name="Category" sql:relation="Categories">         <AttributeType name="CategoryID" dt:type="id"              sql:id-prefix="Ctgy-"/>         <AttributeType name="CategoryName"/>         <attribute type="CategoryID" sql:field="CategoryID"/>         <attribute type="CategoryName" sql:field="CategoryName"/>     </ElementType>     <ElementType name="CategoryList" sql:is-constant="1">         <element type="Category"/>     </ElementType>     <ElementType name="Item" sql:relation="[Order Details]"         sql:key-fields="OrderID ProductID">         <AttributeType name="OrderID"/>         <AttributeType name="ProductID"/>         <AttributeType name="Quantity"/>         <AttributeType name="CategoryID" dt:type="idref"             sql:id-prefix="Ctgy-"/>         <attribute type="OrderID" sql:field="OrderID"/>         <attribute type="ProductID" sql:field="ProductID"/>         <attribute type="Quantity" sql:field="Quantity"/>         <attribute type="CategoryID" sql:relation="Products"             sql:field="CategoryID">             <sql:relationship key-relation="[Order Details]"                 key="ProductID"                 foreign-relation="Products"                 foreign-key="ProductID"/>         </attribute>     </ElementType>     <ElementType name="ItemList" sql:is-constant="1">         <element type="Item"/>     </ElementType>     <ElementType name="PickList" sql:is-constant="1">         <element type="CategoryList"/>         <element type="ItemList"/>     </ElementType> </Schema> 

This schema is also available in the Demos\Chapter6\Schemas folder on the companion CD in a file named PickListSchema.xml. It returns an XML document such as this one:

 <PickList>     <CategoryList>         <Category Category CategoryName="Beverages"/>         <Category Category CategoryName="Condiments"/>         <Category Category CategoryName="Confections"/>         <Category Category CategoryName="Dairy Products"/>         <Category Category CategoryName="Grains/Cereals"/>         <Category Category CategoryName="Meat/Poultry"/>         <Category Category CategoryName="Produce"/>         <Category Category CategoryName="Seafood"/>     </CategoryList>     <ItemList>         <Item Order Product Quantity="12"              Category/>         <Item Order Product Quantity="10"              Category/>         <Item Order Product Quantity="5"              Category/>         <Item Order Product Quantity="9"              Category/>         <Item Order Product Quantity="40"              Category/>              </ItemList> </PickList> 

You can view this data by opening the shortcut named Pick List in the Demos\Chapter6 folder on the companion CD. You can find the category information for a particular item in this XML document by locating the Category element with the corresponding CategoryID attribute.

The primary reason for using ID, IDREF, and IDREFS attributes to create a relational schema is to allow programmatic navigation of the document. For example, the Microsoft implementation of the XML Document Object Model (DOM) provides the nodeFromID method, which you can use to retrieve data from the appropriate ID element based on an IDREF attribute in the current element. The use of ID, IDREF, and IDREFS attributes to navigate a document requires an XML parser that supports XDR schemas. If your parser doesn’t support XDR, you might want to consider an alternative approach, such as XSLT key attributes. For more information about XSLT, refer to the XSLT Reference in the MSDN library at msdn.microsoft.com/library.

Specifying a Target Namespace

You might occasionally want elements in your XML document to be in a specific namespace. Perhaps you want to include a namespace that specifically identifies your XML schema file so that client applications can validate the document against the published schema. To accomplish this aim, you can include the target-namespace annotation in the Schema element of your schema, which causes each element or attribute retrieved by the schema to be assigned the namespace specified in the target-namespace annotation.

Consider the following schema in which a target namespace is specified:

 <?xml version="1.0"?> <Schema xmlns="urn:schemas-microsoft-com:xml-data"     xmlns:dt="urn:schemas-microsoft-com:datatypes"     xmlns:sql="urn:schemas-microsoft-com:xml-sql"     sql:target-namespace=        "x-schema:http://www.northwindtraders.com/schemas/Products.xml">     <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="ProductList" sql:is-constant="1">         <element type="Product"/>     </ElementType> </Schema> 

The schema is saved as Products.xml in the Demos\Chapter6\Schemas folder on the companion CD. In this example, the schema is published in an Internet site, allowing users to access it and validate XML documents based on it. The XML document produced by this mapping schema contains a reference to the namespace with an arbitrarily assigned prefix, as shown in this XML document:

 <y0:ProductList      xmlns:y0=     "x-schema:http://www.northwindtraders.com/schemas/Products.xml">     <y0:Product ProductCode="1">         <y0:Description>Chai</y0:Description>         <y0:Price>18</y0:Price>     </y0:Product>     <y0:Product ProductCode="2">         <y0:Description>Chang</y0:Description>         <y0:Price>19</y0:Price>     </y0:Product>      </y0:ProductList> 

I’ll explain how to get this XML document later in this section. One major issue needs to be addressed when you’re building a client application that uses a mapping schema with target-namespace annotation. Because SQL Server generates the namespace prefix automatically, you’ll find it difficult to specify an appropriate XPath expression. The only way around this problem is to use a template to access the schema and include a reference to the same namespace in the template. This way, you can use the prefix declared in the template to specify the XPath. For example, you could use the following template file to access the product list schema:

 <?xml version="1.0"?> <ProductData xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <sql:xpath-query       xmlns:prd=      "x-schema:http://www.northwindtraders.com/schemas/Products.xml"         mapping-schema = "..\Schemas\Products.xml">         prd:ProductList     </sql:xpath-query> </ProductData> 

This template is saved as ProductList.xml in the Demos\Chapter6\Templates folder on the companion CD. It will return a product list document based on the Products.xml schema namespace. The document will be contained within a ProductData root element, as shown here:

   <?xml version="1.0"?>   <ProductData xmlns:sql="urn:schemas-microsoft-com:xml-sql">         <y0:ProductList xmlns:y0=           "x-schema:http://www.northwindtraders.com/schemas/Products.xml">           <y0:Product ProductCode="1">               <y0:Description>Chai</y0:Description>               <y0:Price>18</y0:Price>           </y0:Product>           <y0:Product ProductCode="2">               <y0:Description>Chang</y0:Description>               <y0:Price>19</y0:Price>           </y0:Product>                 </y0:ProductList> </ProductData> 

You can view this data by opening the shortcut named ProductListNS in the Demos\Chapter6 folder.

The namespace prefix used in the resulting XML is still generated by SQL Server and isn’t related to the prefix used in the template. This means that the client application must be able either to process the document without requiring the namespace prefix or to read from within the document itself the namespace prefix used. You can use the XML DOM to read the document and ascertain the prefix used.

Retrieving Binary Data

You can retrieve binary data, such as images or text, by using a mapping schema. The XML bin.base64 data type maps to such SQL Server data types as binary, image, and varbinary. To map the bin.base64 data type to a specific SQL Server data type, you should use XML-SQL datatype annotation. This annotation can be used in a schema to declare an element or attribute that maps to a text, ntext, image, or binary column. The following example shows how to use the datatype annotation in a schema:

 <?xml version="1.0"?> <Schema name="NWCatalog"     xmlns="urn:schemas-microsoft-com:xml-data"      xmlns:sql="urn:schemas-microsoft-com:xml-sql">    <ElementType name="Category" sql:relation="Categories">        <AttributeType name="CategoryID"/>        <AttributeType name="CategoryName"/>        <AttributeType name="Picture"/>        <attribute type="CategoryID" sql:field="CategoryID"/>        <attribute type="CategoryName" sql:field="CategoryName"/>        <attribute type="Picture" sql:field="Picture"             sql:datatype="image"/>     </ElementType> </Schema> 

This schema is saved as CatalogImagesBin.xml in the Demos\Chapter6\Schemas folder on the companion CD. It returns the Picture attribute as a binary BASE64-encoded string. If you would prefer to receive a URL that could be used to retrieve the image through a dbobject virtual name, you can use the url-encode annotation. This annotation takes a Boolean type value. When you’re using the url-encode annotation, you must provide a way to uniquely identify each row returned by the query, either by specifying a relationship or by including the key-fields annotation. The following modified example shows how a URL for binary data can be retrieved:

 <?xml version="1.0"?> <Schema xmlns="urn:schemas-microsoft-com:xml-data"      xmlns:sql="urn:schemas-microsoft-com:xml-sql">              <ElementType name="Category" sql:relation="Categories"          sql:key-fields="CategoryID">         <AttributeType name="CategoryID"/>         <AttributeType name="CategoryName"/>         <AttributeType name="Picture"/>         <attribute type="CategoryID" sql:field="CategoryID"/>         <attribute type="CategoryName" sql:field="CategoryName"/>         <attribute type="Picture" sql:field="Picture" sql:url-encode="1"/>     </ElementType> </Schema> 

This schema is saved as CatalogImagesURL.xml in the Demos\Chapter6\Schemas folder on the companion CD. It returns each picture attribute as a URL:

 Picture="dbobject/Categories[@CategoryID=‘1’]/@Picture" 

Retrieving CDATA Sections

Because database fields can often contain data including markup characters, you can specify that the field mapped by an element or attribute in a schema should be retrieved as a CDATA section by using the use-cdata annotation. This annotation takes a Boolean type value. You can use this annotation with any element node other than elements that have the ID, IDREF, IDREFS, NMTOKEN, NMTOKENS, or url-encode annotation specified.

The following example shows a schema that maps a product name to a CDATA section:

 <?xml version="1.0" ?> <Schema xmlns="urn:schemas-microsoft-com:xml-data"     xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <ElementType name="ProductName"/>     <ElementType name="Product" sql:relation="Products">         <AttributeType name="ProductID"/>         <attribute type="ProductID" sql:field="ProductID"/>         <element type="ProductName" sql:field="ProductName"             sql:use-cdata="1"/>     </ElementType> </Schema> 

This schema is saved as ProductCData.xml in the Demos\Chapter6\Schemas folder on the companion CD. It returns Product elements containing CDATA sections, as shown here:

 <Product Product>     <ProductName><![CDATA[Chai]]></ProductName> </Product> <Product Product>     <ProductName><![CDATA[Chang]]></ProductName> </Product> <Product Product>     <ProductName><![CDATA[Chartreuse verte]]></ProductName> </Product>  

You can view this data by opening the shortcut named ProductsCData in the Demos\Chapter6 folder on the companion CD.

Retrieving Overflow Columns

As I explained in Chapter 3, some database solutions use an overflow column for unconsumed XML data. You can use the overflow-field annotation with any element mapped to a table to retrieve the unconsumed XML from the overflow column in the table. You can assume that the data in the overflow column consists of a well-formed XML document, and any attributes of the root element of the XML document stored in the overflow column are returned as attributes of the element mapped to the table. Any subelements stored in the overflow column are returned as subelements of the element mapped to the table.

For example, suppose an overflow column named extradata was added to the Products table in the Northwind Traders database. The extradata fields for product number 1 might contain the XML string <extradata imported="yes"> <Origin>China</Origin></extradata>. You can use the following schema to retrieve product data:

 <?xml version="1.0" ?> <Schema xmlns="urn:schemas-microsoft-com:xml-data"     xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <ElementType name="Product" sql:relation="Products"          sql:overflow-field="extradata">         <AttributeType name="ProductID"/>         <AttributeType name="ProductName"/>         <attribute type="ProductID" sql:field="ProductID"/>         <attribute type="ProductName" sql:field="ProductName"/>     </ElementType> </Schema> 

The XML for product number 1 returned by this schema would look like this:

 <Product Product      ProductName="Chai"         imported="yes">     <Origin>China</Origin> </Product>