Identifying Key Columns Using sql:key-fields

XML and Internet Support

XML and Internet Support

Identifying Key Columns Using sql:key-fields

When an XPath query is specified against the XDR schema, key information is required in most cases to obtain proper nesting in the result. Specifying the sql:key-fields annotation is a way to ensure that the appropriate hierarchy is generated.

Note  To produce proper nesting in the result, it is recommended that sql:key-fields be specified in all schemas.

In many instances, it is necessary to understand how to uniquely identify the rows in a table to generate the appropriate XML hierarchy. The sql:key-fields annotation can be added to the <element> and <ElementType> to identify column(s) that uniquely identify rows in the table.

The value of sql:key-fields identifies the column(s) that uniquely identify the rows in the relation specified in the <ElementType>. If more than one column is required to uniquely identify a row, the column values are listed separated with a space.

sql:key-fields must be specified in an element containing a child element and a <sql:relationship>, defined between the element and the child, that does not provide the primary key of the table specified in the parent element.

Examples

To create working samples using these examples, you must create the nwind virtual directory (to access the Northwind database) and a virtual name of template type. For more information about creating the nwind virtual directory, see Creating the nwind Virtual Directory.

In creating working samples in each example, templates are used to specify XPath queries against the mapping XDR schema. There are different ways of using annotated XDR schemas in queries, for example, inline schemas and schemas in the URL. For more information, see Using Annotated XDR Schemas in Queries.

A. Produce the appropriate nesting when <sql:relationship> does not provide sufficient information

This example shows where sql:key-fields must be specified.

Consider the following schema. The schema specifies hierarchy between <Order> and <Customer> elements in which <Order> element is the parent and the <Customer> element is a child.

The <sql:relationship> tag is used to specify the parent-child relationship. <sql:relationship> identifies CustomerID as foreign-key in the Orders table referring to CustomerID key in the Customers table. This information provided in <sql:relationship> is not sufficient to uniquely identify rows the parent table (Orders). Therefore, without sql:key-fields, the hierarchy generated is inaccurate.

With sql:key-fields specified on <Order>, the annotation uniquely identifies the rows in the parent (Orders table) and its child elements appear below its parent.

This is the schema:

<?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="Customer" sql:relation="Customers">
    <AttributeType name="CustomerID" />
    <AttributeType name="ContactName" />

    <attribute type="CustomerID" />
    <attribute type="ContactName" />
  </ElementType>

  <ElementType name="Order" sql:relation="Orders" 
                            sql:key-fields="OrderID" >
    <AttributeType name="OrderID" />
    <AttributeType name="CustomerID" />
 
    <attribute type="OrderID" />
    <attribute type="CustomerID" />
    <element type="Customer" >
             <sql:relationship
                         key-relation="Orders"
                         key="CustomerID"
                         foreign-relation="Customers"
                         foreign-key="CustomerID" />
    </element>
     </ElementType>
</Schema>

Creating a working sample of this schema

  1. Save the schema (MySchema.xml) in the directory associated with the template virtual name that you have already created (or one of its subdirectories, in which case you must specify the relative path in the mapping-schema attribute).

  2. Create this template (MySchemaT.xml) and save it in the directory associated with the template virtual name. The XPath query in the template returns all the <Order> elements.
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:xpath-query mapping-schema="MySchema.xml">
        /Order
      </sql:xpath-query>
    </ROOT>
    

    The directory path specified for the mapping schema is relative to the directory associated with the template virtual name. An absolute path can also be specified, for example:

    mapping-schema="C:\MyDir\MySchema.xml"
    
  3. This URL executes the template:
    http://IISServer/VirtualRoot/template/MySchemaT.xml
    

Here is the partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Order OrderID="10248" CustomerID="VINET"> 
    <Customer CustomerID="VINET" ContactName="Paul Henriot" /> 
  </Order> 
  <Order OrderID="10249" CustomerID="TOMSP"> 
    <Customer CustomerID="TOMSP" ContactName="Karin Josephs" /> 
  </Order>
</ROOT>
B. Specify sql:key-fields to produce proper nesting in the result

In this schema, there is no hierarchy specified using <sql:relationship>. The schema still requires the sql:key-fields annotation specified to uniquely identify employees in the Employees table.

<?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="Region" content="textOnly"  >
      <AttributeType name="EmployeeID" />
      <attribute type="EmployeeID" />
   </ElementType>

   <ElementType name="Employees" sql:key-fields="EmployeeID" >
      <element type="Region" />
   </ElementType>   
</Schema>

Creating a working sample of this schema

  1. Save the schema (MySchema.xml) in the directory associated with the template virtual name that you have already created (or one of its subdirectories, in which case you must specify the relative path in the mapping-schema attribute).

  2. Create this template (MySchemaT.xml) and save it in the directory associated with the template virtual name. The XPath query in the template returns all the <Order> elements.
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:xpath-query mapping-schema="MySchema.xml">
        /Employees
      </sql:xpath-query>
    </ROOT>
    

    The directory path specified for the mapping schema is relative to the directory associated with the template virtual name. An absolute path can also be specified, for example:

    mapping-schema="C:\MyDir\MySchema.xml"
    
  3. This URL executes the template:
    http://IISServer/VirtualRoot/template/MySchemaT.xml
    This is the result:
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
        <Employees> 
            <Region EmployeeID="1">WA</Region> 
        </Employees> 
        <Employees> 
            <Region EmployeeID="2">WA</Region> 
        </Employees>
    </ROOT>
    

See Also

IIS Virtual Directory Management for SQL Server

Using XPath Queries

Accessing SQL Server Using HTTP

Executing Template Files Using HTTP