Identifying Key Columns Using sql:key-fields (XDR Schema)

  • Article

Important

This topic is included as a reference for legacy applications. No future development work will be done on this feature. Avoid using this feature in new development work. Instead, use annotated XSD schemas to create XML views. For more information, see Introduction to Annotated XSD Schemas (SQLXML 4.0). You can convert existing annotated XDR schemas to XSD schemas. For more information, see Converting Annotated XDR Schemas to Equivalent XSD Schemas (SQLXML 4.0).

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.

The sql:key-fields annotation 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 the following examples, you must meet certain requirements. For more information, see Requirements for Running SQLXML Examples.

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. The <sql:relationship> tag identifies CustomerID as foreign-key in the Sales.SalesOrderHeader table, referring to the CustomerID key in the Sales.Customer table. This information provided in <sql:relationship> is not sufficient to uniquely identify rows the parent table (Sales.SalesOrderHeader). 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 (Sales.SalesOrderHeader 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="Sales.Customer">
    <AttributeType name="CustomerID" />
    <attribute type="CustomerID" />
  </ElementType>

  <ElementType name="Order" sql:relation="Sales.SalesOrderHeader" 
                            sql:key-fields="SalesOrderID" >
    <AttributeType name="SalesOrderID" />
    <AttributeType name="CustomerID" />
 
    <attribute type="SalesOrderID" />
    <attribute type="CustomerID" />
    <element type="Customer" >
             <sql:relationship
                         key-relation="Sales.SalesOrderHeader"
                         key="CustomerID"
                         foreign-relation="Sales.Customer"
                         foreign-key="CustomerID" />
    </element>
     </ElementType>
</Schema>

To test a working sample of this schema

  1. Copy the schema code above and paste it into a text file. Save the file as KeyFieldsA-Xdr.xml.

  2. Copy the following template and paste it into a text file. Save the file as KeyFieldsA-XdrT.xml in the same directory where you saved KeyFieldsA-Xdr.xml.

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

    The directory path specified for the mapping schema (KeyFieldsA-Xdr.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:

    mapping-schema="C:\MyDir\KeyFieldsA-Xdr.xml"

  3. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.

    For more information, see Using ADO to Execute SQLXML 4.0 Queries.

Here is the result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Order SalesOrderID="43659" CustomerID="676">
    <Customer CustomerID="676" /> 
  </Order>
  <Order SalesOrderID="43660" CustomerID="117">
    <Customer CustomerID="117" /> 
  </Order>
  <Order SalesOrderID="43661" CustomerID="442">
    <Customer CustomerID="442" /> 
  </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 HumanResources.Employee 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="Title" content="textOnly"  >
      <AttributeType name="EmployeeID" />
      <attribute type="EmployeeID" />
   </ElementType>

   <ElementType name="HumanResources.Employee" sql:key-fields="EmployeeID" >
      <element type="Title" />
   </ElementType>
</Schema>

To test a working sample of this schema

  1. Copy the schema code above and paste it into a text file. Save the file as KeyFieldsB-Xdr.xml.

  2. Copy the following template and paste it into a text file. Save the file as KeyFieldsB-XdrT.xml in the same directory where you saved KeyFieldsB-Xdr.xml. 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="KeyFieldsB-Xdr.xml">
    /HumanResources.Employee
  </sql:xpath-query>
</ROOT>

    The directory path specified for the mapping schema (KeyFieldsB-Xdr.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:

    mapping-schema="C:\MyDir\KeyFieldsB-Xdr.xml"

  3. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.

    For more information, see Using ADO to Execute SQLXML 4.0 Queries.

Here is part of the result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <HumanResources.Employee>
    <Title EmployeeID="1">Production Technician - WC60</Title> 
  </HumanResources.Employee>
  <HumanResources.Employee>
    <Title EmployeeID="2">Marketing Assistant</Title> 
  </HumanResources.Employee>
  <HumanResources.Employee>
    <Title EmployeeID="3">Engineering Manager</Title> 
  </HumanResources.Employee>
   ...
</ROOT>

See Also

Reference

Using XPath Queries in SQLXML 4.0

Help and Information

Getting SQL Server 2005 Assistance