Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

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 employed 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. Since they define what a document may and may not contain, companies wishing to exchange XML data need to agree on a common schema definition in order to do so. XML schemas allow companies with disparate business needs and cultures to exchange data seamlessly.

A mapping schema is a special type of schema that maps data between an XML document and a relational table. A mapping schema can be used to create an XML view of a SQL Server table. In that sense, a mapping schema is similar to a SQL Server view object that returns an XML-centric view of the underlying SQL Server table or view object.

Work on the final XML Schema standard was still under way when SQL Server 2000 shipped. At that time, Microsoft, along with several other companies, proposed that a subset of the W3C XML-Data syntax be used to define schemas for document interchange. SQL Server's original XML schema support was based on XML-Data Reduced (XDR), an XML-Data subset that can be used to define schemas. Since then, the XML Schema standard has been finalized, and SQLXML has been enhanced to support it. XML Schema is now the preferred method of building schemas for use by SQLXML. It is more flexible and has more features than the original XDR schema support in SQLXML. I'll cover SQLXML's XDR and XML Schema support in the next two sections.

XDR Mapping Schemas

Let's begin our coverage of XDR mapping schemas with an example (Listing 18.45).

Listing 18.45

<?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 how a product catalog might look. (We're using the sample tables and data from the Northwind database.) It uses the datatypes namespace (bolded) to define the valid data types for elements and attributes in the document. Every place you see dt: in the listing 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 18.46 shows an XML document that uses ProductCat.xdr.

Listing 18.46

<?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. Mapping schemas help 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 an XDR 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. Listing 18.47 presents an XDR schema that does that.

Listing 18.47

<?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 XDR schema under a virtual directory on your Web server and retrieve it via a URL, you'll see a simple XML document with the data from the Northwind Customers table in an attribute-centric mapping.

You use XML-Data's ElementType to map a column in a table to an element in the resulting XML document, as demonstrated in Listing 18.48.

Listing 18.48

<?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 XDR 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. The code in Listing 18.49 uses our familiar Customer list example.

Listing 18.49

<?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. Since 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 picture—an annotated schema allows you to establish granular mappings between document entities and database entities.

XSD Mapping Schemas

Similarly to XDR, you can also construct XML views using annotated XML Schema Definition (XSD) language. This is, in fact, the preferable way to build annotated schemas because XDR was an interim technology that preceded the finalization of the XML Schema standard, as I mentioned earlier. In this section, we'll talk about the various ways to construct annotated XSD mapping schemas and walk through a few examples.

Just as we did with XDR, let's begin our discussion of XSD mapping schemas with an example (Listing 18.50).

Listing 18.50

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Customers" >
    <xsd:complexType>
      <xsd:attribute name="CustomerID" type="xsd:string" />
      <xsd:attribute name="CompanyName" type="xsd:string" />
      <xsd:attribute name="ContactName" type="xsd:string" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Note the reference to the XSD namespace, http://www.w3.org/2001/XMLSchema. We alias this to xsd (the alias name is arbitrary—it serves merely as shorthand to distinguish XSD elements and attributes from those of other namespaces), then prefix XSD elements/attributes in the schema with xsd:.

SQLXML's mapping schema namespace is defined at urn:schemas-microsoft-com:mapping-schema. We use this namespace to map elements and attributes in the schema to tables and columns in a database. We've defined this namespace with an alias of sql, so we'll use a prefix of sql: when referring to elements and attributes in SQLXML's mapping schema namespace.

Default Mapping

The schema above uses default mapping to associate complex XSD types with tables/views of the same name and attributes with same-named columns. Note the absence of any reference to the sql namespace (once it's defined). We're not using it because we're not explicitly mapping any elements or attributes to tables or columns. You can construct a template like the following to query this XML view using an XPath expression:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="Customers.xsd">
    /Customers
  </sql:xpath-query>
</ROOT>

Follow these steps to query the XML view in Listing 18.50 by using the above template from your browser.

  1. Save the XML view as Customers.XSD in the templates folder you created under the Northwind virtual directory earlier.

  2. Save the template above as CustomersT.XML in the same folder.

  3. Go to the following URL in your browser:

    http://localhost/Northwind/templates/CustomersT.XML

Explicit Mapping

A mapping schema can also specify explicit relationships between XSD elements and attributes and SQL Server tables and columns. This is done by using the SQLXML mapped schema namespace I mentioned above. Specifically, we'll make use of sql:field and sql:relation to establish these relationships, as shown in Listing 18.51.

Listing 18.51

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Cust" sql:relation="Customers" >
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CustNo"
                     sql:field="CustomerId"
                     type="xsd:integer" />
        <xsd:element name="Contact"
                     sql:field="ContactName"
                     type="xsd:string" />
        <xsd:element name="Company"
                     sql:field="CompanyName"
                     type="xsd:string" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Note the use of sql:relation to establish the mapping between the Cust document element and the Customers database table and the use of the sql:field notation to establish mappings between document elements and table columns. Because each table column is annotated as an element, each column in the Customers table will become a separate element in the resulting XML document. You can also map table columns to attributes, as demonstrated in Listing 18.52.

Listing 18.52

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Cust" sql:relation="Customers" >
    <xsd:complexType>
      <xsd:attribute name="CustNo" sql:field="CustomerId"
          type="xsd:integer" />
     <xsd:attribute name="Contact" sql:field="ContactName"
          type="xsd:string" />
     <xsd:attribute name="Company" sql:field="CompanyName"
          type="xsd:string" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Here, we leave out the complexType element (because we don't need it—we're not defining nested elements) and simply map each table column to an attribute in the XSD using sql:field.

Relationships

You can use the sql:relationship annotation to establish a relationship between two elements. You define an empty sql:relationship element and include parent, parent-key, child, and child-key attributes to define the relationship between the two elements. Relationships defined this way can be named or unnamed. For elements mapped to tables and columns in a SQL Server database, this is similar to joining the tables; the parent/child and parent-key/child-key matchups supply the join criteria. Listing 18.53 shows an example (from EmpOrders.XSD in the CH18 subfolder on the CD accompanying this book).

Listing 18.53

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

  <xsd:element name="Employee" sql:relation="Employees"
      type="EmployeeType" />
    <xsd:complexType name="EmployeeType" >
      <xsd:sequence>
        <xsd:element name="Order"
                     sql:relation="Orders">
          <xsd:annotation>
            <xsd:appinfo>
              <sql:relationship
                parent="Employees"
                parent-key="EmployeeID"
                child="Orders"
                child-key="EmployeeID" />
            </xsd:appinfo>
          </xsd:annotation>
          <xsd:complexType>
            <xsd:attribute name="OrderID" type="xsd:integer" />
            <xsd:attribute name="EmployeeID" type="xsd:integer" />
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
        <xsd:attribute name="EmployeeID"   type="xsd:integer" />
        <xsd:attribute name="LastName"  type="xsd:string" />
    </xsd:complexType>
</xsd:schema>

In this schema, we establish a relationship between the Employee and Order elements using the EmployeeID attribute. Again, this is accomplished via the notational attributes provided by Microsoft's mapping-schema namespace.

sql:inverse

You can use the sql:inverse annotation to invert a relationship established with sql:relationship. Why would you want to do that? SQLXML's updategram logic interprets the schema in order to determine the tables being updated by an updategram. (We'll cover updategrams in the next section.) The parent-child relationships established with sql:relationship determine the order in which row deletions and inserts occur. If you specify the sql:relationship notation such that the parent-child relationship between the tables is the inverse of the underlying primary key/foreign key relationship, the attempted insert or delete operation will fail due to key violations. You can set the sql:inverse attribute to 1 (or true) in the sql:relationship element in order to flip the relationship so that this doesn't happen.

The usefulness of the sql:inverse notation is limited to updategrams. There's no point in inversing a regular mapping schema. Listing 18.54 presents an example of a mapping schema that puts the sql:inverse annotation attribute to good use. (You can find this in OrderDetails.XSD in the CH18 folder on the CD accompanying this book.)

Listing 18.54

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

  <xsd:element name="OrderDetails" sql:relation="[Order Details]"
      type="OrderDetailsType" />
    <xsd:complexType name="OrderDetailsType" >
      <xsd:sequence>
        <xsd:element name="Order"
                     sql:relation="Orders">
          <xsd:annotation>
            <xsd:appinfo>
              <sql:relationship
                parent="[Order Details]"
                parent-key="OrderID"
                child="Orders"
                child-key="OrderID"
              inverse="true" />
            </xsd:appinfo>
          </xsd:annotation>
          <xsd:complexType>
            <xsd:attribute name="OrderID" type="xsd:integer" />
            <xsd:attribute name="EmployeeID" type="xsd:integer" />
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
      <xsd:attribute name="ProductID"   type="xsd:integer" />
      <xsd:attribute name="Qty" sql:field="Quantity" type="xsd:integer" />
    </xsd:complexType>
</xsd:schema>

Note the use of square brackets around the Order Details table name. These are required in the mapping schema for SQL Server table names that contain spaces.

sql:mapped

You can use the sql:mapped annotation to control whether an attribute or element is mapped to a database object. When the default mapping is used, every element and attribute in a mapping schema maps to a database object. If you have a schema in which you have elements or attributes that you do not want to map to database objects, you can set the sql:mapped annotation to 0 (or false) in an XSD element or attribute specification. The sql:mapped annotation is especially useful in situations where the schema can't be changed or is being used to validate other XML data and contains elements or attributes that do not have analogues in your database. Listing 18.55 uses sql:mapped to include an element in a mapping schema that is not mapped to a database object.

Listing 18.55

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

  <xsd:element name="Employee" sql:relation="Employees"
      type="EmployeeType" />
    <xsd:complexType name="EmployeeType" >
      <xsd:sequence>
        <xsd:element name="Order"
                     sql:relation="Orders">
          <xsd:annotation>
            <xsd:appinfo>
              <sql:relationship
                parent="Employees"
                parent-key="EmployeeID"
                child="Orders"
                child-key="EmployeeID" />
            </xsd:appinfo>
          </xsd:annotation>
          <xsd:complexType>
            <xsd:attribute name="OrderID" type="xsd:integer" />
            <xsd:attribute name="EmployeeID" type="xsd:integer" />
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
      <xsd:attribute name="EmployeeID"   type="xsd:integer" />
      <xsd:attribute name="LastName"  type="xsd:string" />
      <xsd:attribute name="Level" type="xsd:integer"
          sql:mapped="0" />
    </xsd:complexType>
</xsd:schema>

Note the inclusion of the Level attribute in the Employee element. Because it contains a sql:mapped annotation that is set to false, it is not mapped to a database object.

sql:limit-field and sql:limit-value

Similarly to the way you can filter XML views using XPath expressions, you can also filter them based on values returned from the database using the sql:limit-field and sql:limit-value annotations. The sql:limit-field annotation specifies the filter column from the database; sql:limit-value specifies the value to filter it by. Note that sql:limit-value is actually optional—if it isn't supplied, NULL is assumed. Listing 18.56 shows an example of a mapping schema that filters based on the value of a column in the database.

Listing 18.56

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

  <xsd:element name="Employee" sql:relation="Employees"
      type="EmployeeType" />
    <xsd:complexType name="EmployeeType" >
      <xsd:sequence>
        <xsd:element name="Order"
                     sql:relation="Orders">
          <xsd:annotation>
            <xsd:appinfo>
              <sql:relationship
                parent="Employees"
                parent-key="EmployeeID"
                child="Orders"
                child-key="EmployeeID" />
            </xsd:appinfo>
          </xsd:annotation>
          <xsd:complexType>
            <xsd:attribute name="OrderID" type="xsd:integer" />
            <xsd:attribute name="EmployeeID" type="xsd:integer" />
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
        <xsd:attribute name="EmployeeID"
          type="xsd:integer"
          sql:limit-field="EmployeeID"
          sql:limit-value="3"/>
        <xsd:attribute name="LastName"  type="xsd:string" />
    </xsd:complexType>

</xsd:schema>

This schema filters the XML document based on the EmployeeID column in the database. Only those rows with an EmployeeID of 3 are returned in the document. If you submit a URL query against this mapping schema using the following template:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="EmpOrders_Filtered.XSD">
    /Employee
  </sql:xpath-query>
</ROOT>

you'll see a document that looks something like this in your browser (results abridged):

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Employee EmployeeID="3" LastName="Leverling">
  <Order EmployeeID="3" OrderID="10251" />
  <Order EmployeeID="3" OrderID="10253" />
  <Order EmployeeID="3" OrderID="10256" />
  <Order EmployeeID="3" OrderID="10266" />
  <Order EmployeeID="3" OrderID="10273" />
  <Order EmployeeID="3" OrderID="10283" />
  <Order EmployeeID="3" OrderID="10309" />
  <Order EmployeeID="3" OrderID="10321" />
  <Order EmployeeID="3" OrderID="10330" />
  <Order EmployeeID="3" OrderID="10332" />
  <Order EmployeeID="3" OrderID="10346" />
  <Order EmployeeID="3" OrderID="10352" />
...
</ROOT>

sql:key-fields

You use the sql:key-fields annotation to identify the key columns in a table to which an XML view is mapped. The sql:key-fields annotation is usually required in mapping schemas in order to ensure that proper nesting occurs in the resulting XML document. This is because the key columns of the underlying table are used to nest the document. This makes the XML that's produced sensitive to the order of the underlying data. If the key columns of the underlying data can't be determined, the generated XML might be formed incorrectly. You should always specify either sql:key-fields or elements that map directly to tables in the database. Listing 18.57 offers an example of a mapping schema that uses sql:key-fields (from EmpOrders_ KeyFields.XSD in the CH18 folder on the CD accompanying this book).

Listing 18.57

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

  <xsd:element name="Employee"
  sql:relation="Employees"
  type="EmployeeType"
  sql:key-fields="EmployeeID"/>
    <xsd:complexType name="EmployeeType" >
      <xsd:sequence>
        <xsd:element name="Order"
                     sql:relation="Orders">
          <xsd:annotation>
            <xsd:appinfo>
              <sql:relationship
                parent="Employees"
                parent-key="EmployeeID"
                child="Orders"
                child-key="EmployeeID" />
            </xsd:appinfo>
          </xsd:annotation>
          <xsd:complexType>
            <xsd:attribute name="OrderID" type="xsd:integer" />
            <xsd:attribute name="EmployeeID" type="xsd:integer" />
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
        <xsd:attribute name="LastName"  type="xsd:string" />
        <xsd:attribute name="FirstName"  type="xsd:string" />
    </xsd:complexType>
</xsd:schema>

Note that we haven't mapped the EmployeeID column in the Employees table. Without this column, we don't have a column with which we can join the Orders table. Including it in the sql:key-fields annotation allows us to leave it unmapped but still establish the relationship between the two tables.

  • + Share This
  • 🔖 Save To Your Account