Specifying Relationships Using <sql:relationship>

XML and Internet Support

previous page next page
XML and Internet Support

Specifying Relationships Using <sql:relationship>

The elements in an XML document can be related. The elements can be nested hierarchically, and ID, IDREF, or IDREFS relationships can be specified between the elements.

For example, in an XDR schema, a <customer> element contains <order> subelements. The <customer> element maps to Customers table and <order> element maps to Orders table in the database. These underlying tables, Customers and Orders are related because customers place orders. The CustomerID in Orders table is a foreign key referring to CustomerID primary key in Customers table. You can establish these relationships among mapping schema elements using the <sql:relationship> annotation.

In the annotated XDR schema, the <sql:relationship> annotation is used to nest the schema elements hierarchically based on the primary key and foreign key relationships among the underlying tables to which the elements map. In specifying the <sql:relationship> annotation, you must identify:

  • The primary table (Customers) and the foreign table (Orders) and

  • The necessary join condition (CustomerID in Orders is a foreign key referring to CustomerID primary key in Customers table).

This information is used in generating the proper hierarchy (for each <customer> element, the related <order> elements appear as subelements).

To provide the table names and the necessary join information, the following attributes are specified with the <sql:relationship> annotation. These attributes are valid only with the <sql:relationship> element:

  • key-relation

    Specifies the primary relation (table).

  • key

    Specifies the primary key of the key-relation. If the primary key is composed of multiple columns, values are specified with a space between them. There is positional mapping between the values specified for the multicolumn key and the corresponding foreign key.

  • foreign-relation

    Specifies the foreign relation (table).

  • foreign-key

    Specifies the foreign key in the foreign-relation referring to key in key-relation. If the foreign key is composed of multiple attributes (columns), the foreign key values are specified with a space between them. There is positional mapping between the values specified for the multicolumn key and the corresponding foreign key.

Note  You must ensure that the Microsoft® SQL Server™ data types of the key and foreign-key are such that they can be implicitly converted if necessary.

The <sql:relationship> tag can be added only to <element> or <attribute> elements in an annotated schema. When <sql:relationship> is specified on an <attribute>, there should be a sql:relation and sql:field specified for the attribute to ensure that a single value is retrieved (multiple attributes of the same name are invalid in XML). When <sql:relationship> is specified on an <element>, the relationship may result in a single value or a set of values.

The <sql:relationship> tag is used to specify a single logical relationship between two entities. The attributes define the relations and fields used to define the logical relationship. Multiple instances of <sql:relationship> may be specified within an <element> or <attribute> in the annotated schema, which indicates a complex relationship between the <element> or <attribute> and its contained element. All instances of <sql:relationship> are used together to define the complex relationship.

When multiple instances of <sql:relationship> tag are specified within an <element> or <attribute>, the order in which they appear is significant.

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. For more information, see Identifying Key Columns Using sql:key-fields. To produce proper nesting in the result, it is recommended that sql:key-fields be specified in all schemas.

Note  In the mapping schema, relational values such as table name and column name are case-sensitive.

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. Specify <sql:relationship> on an <element>

This annotated XDR schema includes <Customer> and <Order> elements. The <Order> element is a subelement of <Customer> element.

In the schema, the <sql:relationship> annotation is specified on the <Order> subelement. The annotation identifies CustomerID in the Orders table as a foreign key referring to the CustomerID primary key in the Customers table. Therefore, orders belonging to a customer appear as a subelement of that <Customer> element.

<?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="Order" sql:relation="Orders" >
    <AttributeType name="CustomerID" />
    <AttributeType name="OrderID" />
    <AttributeType name="OrderDate" />

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

Note  In the mapping schema, the relational values such as the table name and column name are case-sensitive. In the previous example, Customers is the value of sql:relation attribute. The corresponding key-relation attribute value must also be Customers.

Testing a sample XPath query against the 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 query in the template selects a customer with the CustomerID of ALFKI. 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="MySchema.xml">
    Customer[@CustomerID="ALFKI"]
  </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
  4. Here is the partial result set: 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customer CustomerID="ALFKI"> 
    <Order CustomerID="ALFKI" OrderID="10643" 
           OrderDate="1997-08-25T00:00:00" /> 
    <Order CustomerID="ALFKI" OrderID="10692" 
           OrderDate="1997-10-03T00:00:00" /> 
     ...
  </Customer> 
</ROOT>
B. Specify <sql:relationship> on an <attribute> and create document references using ID and IDREFS.

In this example, local document references are specified using ID and IDREFS. The sample XDR schema consists of <Customer> element that maps to the Customers table. This element consists of an <Order> subelement that maps to the Orders table.

In the example, <sql:relationship> is specified twice:

  • <sql:relationship> is specified on the <Order> subelement. Therefore, orders belonging to a customer will appear as subelement of that <Customer> element.

  • <sql:relationship> is also specified on the OrderIDList attribute of the <Customer> element. This attribute is defined as IDREFS type referring to the OrderID attribute (an ID type attribute) of the <Order> element. Therefore, <sql:relationship> is required. In this case, the <sql:relationship> annotation allows a list of orders belonging to a customer to appear with that <Customer> element.

    Attributes specified as IDREFS can be used to refer to ID type attributes, thus enabling intradocument links.

Because numbers are not valid ID values (must be name tokens), sql:id-prefix has been used to make the Order ID a string value. For more information, see Using sql:id-prefix.

<?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="Order" sql:relation="Orders" >
    <AttributeType name="OrderID" dt:type="id" sql:id-prefix="Ord-" />
    <AttributeType name="OrderDate" />
 
    <attribute type="OrderID" />
    <attribute type="OrderDate" />
  </ElementType>

  <ElementType name="Customer" sql:relation="Customers">
    <AttributeType name="CustomerID"  />
     <AttributeType name="ContactName" />

    <attribute type="CustomerID" />
    <attribute type="ContactName" />
    <AttributeType name="OrderIDList" dt:type="idrefs" 
                                      sql:id-prefix="Ord-"/>
    <attribute type="OrderIDList" sql:relation="Orders" 
                                  sql:field="OrderID">
                 <sql:relationship
                      key-relation="Customers"
                      key="CustomerID"
                      foreign-relation="Orders"
                      foreign-key="CustomerID" />
    </attribute>
    <element type="Order">
                 <sql:relationship key-relation="Customers"
                      key="CustomerID"
                      foreign-relation="Orders"
                      foreign-key="CustomerID" />
    </element>
  </ElementType>
</Schema>

Testing a sample XPath query against the 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 query in the template selects a customer with the CustomerID of ALFKI. 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="schema/MySchema.xml">
    Customer[@CustomerID="ALFKI"]
  </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
  4. Here is the partial result set: 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customer CustomerID="ALFKI" ContactName="Maria Anders" 
            OrderIDList="Ord-10643 Ord-10692 Ord-10702 
                         Ord-10835 Ord-10952 Ord-11011"> 
    <Order OrderID="Ord-10643" OrderDate="1997-08-25T00:00:00" /> 
    <Order OrderID="Ord-10692" OrderDate="1997-10-03T00:00:00" /> 
    <Order OrderID="Ord-10702" OrderDate="1997-10-13T00:00:00" /> 
    <Order OrderID="Ord-10835" OrderDate="1998-01-15T00:00:00" /> 
    <Order OrderID="Ord-10952" OrderDate="1998-03-16T00:00:00" /> 
    <Order OrderID="Ord-11011" OrderDate="1998-04-09T00:00:00" /> 
  </Customer> 
</ROOT>
C. Specify <sql:relationship> on multiple <element>s

In this example, the annotated XDR schema consists of the <Customer>, <Order>, and <OD> elements.

The <Order> element is a subelement of <Customer> element. <sql:relationship> is specified on the <Order> subelement so that orders belonging to a customer appear as subelements of <Customer>.

The <Order> element includes <OD> subelement. <sql:relationship> is specified on <OD> subelement so that the order details belonging to an order appear as subelements of that <Order> element.

<?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="OD" sql:relation="[Order Details]" >
    <AttributeType name="OrderID" />
    <AttributeType name="ProductID" />

    <attribute type="OrderID" />
    <attribute type="ProductID" />
</ElementType>

<ElementType name="Order" sql:relation="Orders" >
    <AttributeType name="CustomerID" />
    <AttributeType name="OrderID" />
    <AttributeType name="OrderDate" />

    <attribute type="CustomerID" />
    <attribute type="OrderID" />
    <attribute type="OrderDate" />
    <element type="OD" >
             <sql:relationship 
                   key-relation="Orders"
                   key="OrderID"
                   foreign-key="OrderID"
                   foreign-relation="[Order Details]" />
    </element>
</ElementType>

<ElementType name="Customer" sql:relation="Customers" >
    <AttributeType name="CustomerID" />

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

Testing a sample XPath query against the 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 query in the template returns order information for a customer whose CustomerID is ALFKI and OrderID is 10643. 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="MySchema.xml">
    /Customer[@CustomerID="ALFKI"]/Order[@OrderID=10643]
  </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
  4. Here is the partial result set: 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
<Order CustomerID="ALFKI" OrderID="10643" 
                          OrderDate="1997-08-25T00:00:00"> 
  <OD OrderID="10643" ProductID="28" /> 
  <OD OrderID="10643" ProductID="39" /> 
  <OD OrderID="10643" ProductID="46" /> 
</Order> 
</ROOT>
D. Specify indirect relationships

In this example, the annotated XDR schema consists of the <Customer>, <OD> elements. The relationship between these elements is indirect (Customers table is related to Order Details table through the Orders table). To relate a customer to the order details, first the relationship between the Customer table and the Orders table is specified. Then, the relationship between the Orders and Order Details tables is specified.

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="OD" sql:relation="[Order Details]" >
    <AttributeType name="OrderID" />
    <AttributeType name="ProductID" />
    <AttributeType name="UnitPrice" />

    <attribute type="OrderID" />
    <attribute type="ProductID" />
    <attribute type="UnitPrice" />
</ElementType>
<ElementType name="Customer" sql:relation="Customers" >
    <AttributeType name="CustomerID" />
    <attribute type="CustomerID" />
    <element type="OD" >
             <sql:relationship 
                    key-relation="Customers"
                    key="CustomerID"
                    foreign-relation="Orders"
                    foreign-key="CustomerID"/>
             <sql:relationship 
                    key-relation="Orders"
                    key="OrderID"
                    foreign-relation="[Order Details]" 
                    foreign-key="OrderID" />
    </element>
</ElementType>
</Schema>

Testing a sample XPath query against the 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 query in the template returns order information for a customer whose CustomerID is ALFKI and OrderID is 10643. 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<sql:xpath-query mapping-schema="TestSchema1.xml" >
/Customer[@CustomerID="ALFKI"]
</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
  4. Here is the partial result: 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customer CustomerID="ALFKI"> 
    <OD OrderID="10643" ProductID="28" UnitPrice="45.6" /> 
    <OD OrderID="10643" ProductID="39" UnitPrice="18" /> 
    <OD OrderID="10643" ProductID="46" UnitPrice="12" /> 
    ...
  </Customer> 
</ROOT>
E. Specify multikey join relationships

In specifying a join using <sql:relationship>, you can specify a join involving two or more columns. In this case, the column names for key and foreign-key are listed using a space.

This example assumes these two tables exist:

  • Cust(fname, lname)

  • Ord(OrderID, fname, lname)

The fname and lname columns form the primary key of the Cust table. The OrderID is the primary key of the Ord table. The fname and lname in Ord table are foreign keys referring to fname and lname primary key of the Cust table.

This schema consists of <Cust> and <Ord> elements. <sql:relationship> is used to join them.

<?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="Ord" sql:relation="Ord" >
    <AttributeType name="OrderID" />

    <attribute type="OrderID" />
</ElementType>

<ElementType name="Cust" sql:relation="Cust" >
    <AttributeType name="fname" />
    <AttributeType name="lname" />
    <attribute type="fname" />
    <attribute type="lname" />
    <element type="Ord" >
             <sql:relationship 
                    key-relation="Cust"
                    key="fname lname"
                    foreign-relation="Ord"
                    foreign-key="fname lname"/>
    </element>
</ElementType>
</Schema>

Testing a sample XPath query against the schema

  1. Create the two tables: Cust and Ord.

  2. Add this sample data: 
    INSERT INTO Cust values ('Nancy', 'Davolio')
INSERT INTO Cust values('Andrew', 'Fuller')
INSERT INTO Ord values (1,'Nancy', 'Davolio')
INSERT INTO Ord values (2,'Nancy', 'Davolio')
INSERT INTO Ord values (3,'Andrew', 'Fuller')
  3. 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).

  4. Create this template (MySchemaT.xml) and save it in the directory associated with the template virtual name. The query in the template returns customer information. 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
  <sql:xpath-query mapping-schema="TestSchema1.xml" >
    /Cust
  </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"
  5. This URL executes the template: 
    http://IISServer/VirtualRoot/template/MySchemaT.xml
  6. Here is the partial result: 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Cust fname="Andrew" lname="Fuller"> 
    <Ord OrderID="3" /> 
  </Cust> 
  <Cust fname="Nancy" lname="Davolio"> 
    <Ord OrderID="1" /> 
    <Ord OrderID="2" /> 
  </Cust> 
</ROOT>

See Also

IIS Virtual Directory Management for SQL Server

Using XPath Queries

Accessing SQL Server Using HTTP

Executing Template Files Using HTTP

previous page start next page