• Article

Specifying Relationships Using sql:relationship (XDR Schema)

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).

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> child elements. The <Customer> element maps to a table that holds customer data (such as Sales.Customer in the AdventureWorks2008R2 database). The <Order> element maps to a table that holds order data in the same database (such as Sales.SalesORderHeader). These underlying tables, Sales.Customer and Sales.SalesOrderHeader are related because customers place orders. The CustomerID in the Sales.SalesOrderHeader table is a foreign key referring to CustomerID primary key in Sales.Customer 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 (Sales.Customer) and the foreign table (Sales.SalesOrderHeader) and

  • The necessary join condition (CustomerID in Sales.SalesOrderHeader is a foreign key referring to CustomerID primary key in Sales.Customer table).

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

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 can 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 can 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.

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. For more information, see Identifying Key Columns Using sql:key-fields (SQLXML 4.0). 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 the following examples, you must meet certain requirements. For more information, see Requirements for Running SQLXML Examples.

A. Specify sql:relationship on an <element>

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

In the schema, the sql:relationship annotation is specified on the <Order> child element. The annotation identifies CustomerID in the Sales.SalesOrderHeader table as a foreign key referring to the CustomerID primary key in the Sales.Customer table. Therefore, orders belonging to a customer appear as a child element 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="Sales.SalesOrderHeader" >
    <AttributeType name="CustomerID" />
    <AttributeType name="SalesOrderID" />
    <AttributeType name="OrderDate" />

    <attribute type="CustomerID" />
    <attribute type="SalesOrderID" />
    <attribute type="OrderDate" />
</ElementType>
<ElementType name="Customer" sql:relation="Sales.Customer" >
    <AttributeType name="CustomerID" />
    <attribute type="CustomerID" />
    <element type="Order" >
             <sql:relationship 
                     key-relation="Sales.Customer"
                    key="CustomerID"
                    foreign-key="CustomerID"
                    foreign-relation="Sales.SalesOrderHeader" />
    </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.

To test a sample XPath query against the schema

  1. Copy the schema code above and paste it into a text file. Save the file as sql-relationship-xdr.xml.

  2. Copy the following template and paste it into a text file. Save the file as sql-relationship-xdrT.xml in the same directory where you saved sql-relationship-xdr.xml. The query in the template selects a customer with the CustomerID of 1.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="sql-relationship-xdr.xml">
    Customer[@CustomerID="1"]
  </sql:xpath-query>
</ROOT>

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

    mapping-schema="C:\SqlXmlTest\sql-relationship-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 partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Customer CustomerID="1">
    <Order CustomerID="1" SalesOrderID="43860" OrderDate="2005-08-01T00:00:00" /> 
    <Order CustomerID="1" SalesOrderID="44501" OrderDate="2005-11-01T00:00:00" /> 
    <Order CustomerID="1" SalesOrderID="45283" OrderDate="2006-02-01T00:00:00" /> 
    <Order CustomerID="1" SalesOrderID="46042" OrderDate="2006-05-01T00: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 a <Customer> element that maps to the Sales.Customer table. This element consists of an <Order> child element that maps to the Sales.SalesOrderHeader table.

In the example, sql:relationship is specified twice:

  • sql:relationship is specified on the <Order> child element. Therefore, orders belonging to a customer will appear as child element 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 SalesOrderID 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 Creating Valid ID, IDREF, and IDREFS Type Attributes Using sql:id-prefix (XDR 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="Order" sql:relation="Sales.SalesOrderHeader" >
    <AttributeType name="SalesOrderID" dt:type="id" sql:id-prefix="Ord-" />
    <AttributeType name="OrderDate" />
 
    <attribute type="SalesOrderID" />
    <attribute type="OrderDate" />
  </ElementType>

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

    <attribute type="CustomerID" />
    <AttributeType name="OrderIDList" dt:type="idrefs" 
                                      sql:id-prefix="Ord-"/>
    <attribute type="OrderIDList" sql:relation="Sales.SalesOrderHeader" 
                                  sql:field="SalesOrderID">
                 <sql:relationship
                      key-relation="Sales.Customer"
                      key="CustomerID"
                      foreign-relation="Sales.SalesOrderHeader"
                      foreign-key="CustomerID" />
    </attribute>
    <element type="Order">
                 <sql:relationship key-relation="Sales.Customer"
                      key="CustomerID"
                      foreign-relation="Sales.SalesOrderHeader"
                      foreign-key="CustomerID" />
    </element>
  </ElementType>
</Schema>

To test a sample XPath query against the schema

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

  2. Copy the following template and paste it into a text file. Save the file as idIdref-xdrT.xml in the same directory where you saved idIdref-xdr.xml. The query in the template selects a customer with the CustomerID of 1.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="idIdref-xdr.xml">
    Customer[@CustomerID="1"]
  </sql:xpath-query>
</ROOT>

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

    mapping-schema="C:\SqlXmlTest\idIdref-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">
  <Customer CustomerID="1" 
            OrderIDList="Ord-43860 Ord-44501 Ord-45283 Ord-46042">
    <Order SalesOrderID="Ord-43860" OrderDate="2005-08-01T00:00:00" /> 
    <Order SalesOrderID="Ord-44501" OrderDate="2005-11-01T00:00:00" /> 
    <Order SalesOrderID="Ord-45283" OrderDate="2006-02-01T00:00:00" /> 
    <Order SalesOrderID="Ord-46042" OrderDate="2006-05-01T00:00:00" /> 
  </Customer>
</ROOT>

C. Specify sql:relationship on multiple elements

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

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

The <Order> element includes the <OD> child element. sql:relationship is specified on the <OD> child element so that the order details belonging to an order appear as child elements 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="Sales.SalesOrderDetail" >
    <AttributeType name="SalesOrderID" />
    <AttributeType name="ProductID" />

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

<ElementType name="Order" sql:relation="Sales.SalesOrderHeader" >
    <AttributeType name="CustomerID" />
    <AttributeType name="SalesOrderID" />
    <AttributeType name="OrderDate" />

    <attribute type="CustomerID" />
    <attribute type="SalesOrderID" />
    <attribute type="OrderDate" />
    <element type="OD" >
             <sql:relationship 
                   key-relation="Sales.SalesOrderHeader"
                   key="SalesOrderID"
                   foreign-key="SalesOrderID"
                   foreign-relation="Sales.SalesOrderDetail" />
    </element>
</ElementType>

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

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

To test a sample XPath query against the schema

  1. Copy the schema code above and paste it into a text file. Save the file as sql-relationship-multi-xdr.xml.

  2. Copy the following template and paste it into a text file. Save the file as sql-relationship-multi-xdrT.xml in the same directory where you saved sql-relationship-multi-xdr.xml. The query in the template returns order information for a customer whose CustomerID is 1 and SalesOrderID is 43860.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="sql-relationship-multi-xdr.xml">
    /Customer[@CustomerID="1"]/Order[@SalesOrderID=43860]
  </sql:xpath-query>
</ROOT>

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

    mapping-schema="C:\SqlXmlTest\sql-relationship-multi-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 CustomerID="1" SalesOrderID="43860" OrderDate="2005-08-01T00:00:00">
    <OD SalesOrderID="43860" ProductID="761" /> 
    <OD SalesOrderID="43860" ProductID="770" /> 
    <OD SalesOrderID="43860" ProductID="758" /> 
    <OD SalesOrderID="43860" ProductID="765" /> 
    <OD SalesOrderID="43860" ProductID="732" /> 
    <OD SalesOrderID="43860" ProductID="762" /> 
    <OD SalesOrderID="43860" ProductID="738" /> 
    <OD SalesOrderID="43860" ProductID="768" /> 
    <OD SalesOrderID="43860" ProductID="753" /> 
    <OD SalesOrderID="43860" ProductID="729" /> 
    <OD SalesOrderID="43860" ProductID="763" /> 
    <OD SalesOrderID="43860" ProductID="756" /> 
  </Order>
</ROOT>

D. Specify indirect relationships

In this example, the annotated XDR schema consists of the <Customer> and <OD> elements. The relationship between these elements is indirect (Sales.Customer table is related to the Sales.SalesOrderDetail table through the Sales.SalesOrderHeader table). To relate a customer to the order details, first the relationship between the Sales.Customer table and the Sales.SalesOrderHeader table is specified. Then, the relationship between the Sales.SalesOrderHeader and Sales.SalesOrderDetail 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="Sales.SalesOrderDetail" >
    <AttributeType name="SalesOrderID" />
    <AttributeType name="ProductID" />
    <AttributeType name="UnitPrice" />

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

To test a sample XPath query against the schema

  1. Copy the schema code above and paste it into a text file. Save the file as indirect-relationship-xdr.xml.

  2. Copy the following template and paste it into a text file. Save the file as indirect-relationship-xdrT.xml in the same directory where you saved indirect-relationship-xdr.xml. The query in the template returns order information for a customer whose CustomerID is 1.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<sql:xpath-query mapping-schema="indirect-relationship-xdr.xml" >
/Customer[@CustomerID="1"]
</sql:xpath-query>
</ROOT>

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

    mapping-schema="C:\SqlXmlTest\indirect-relationship-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 partial result:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Customer CustomerID="1">
    <OD SalesOrderID="43860" ProductID="761" UnitPrice="503.3507" /> 
    <OD SalesOrderID="43860" ProductID="770" UnitPrice="503.3507" /> 
    <OD SalesOrderID="43860" ProductID="758" UnitPrice="1049.7528" /> 
    <OD SalesOrderID="43860" ProductID="765" UnitPrice="503.3507" /> 
    ...
  </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 in a temporary database (such as tempdb):

  • dbo.Cust (fname, lname)

  • dbo.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>

To test a sample XPath query against the schema

  1. Create the two tables: Cust and Ord.

    USE tempdb
CREATE TABLE dbo.Cust(
       fname  varchar(20), 
       lname   varchar(20)
       )
CREATE TABLE dbo.Ord (
       OrderID int primary key, 
       fname  varchar(20), 
       lname   varchar(20)
       )
GO

  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. Copy the schema code above and paste it into a text file. Save the file as multikey-join-xdr.xml.

  4. Copy the following template and paste it into a text file. Save the file as multikey-join-xdrT.xml in the same directory where you saved multikey-join-xdr.xml. The query in the template returns customer information.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
  <sql:xpath-query mapping-schema="multikey-join-xdr.xml" >
    /Cust
  </sql:xpath-query>
</ROOT>

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

    mapping-schema="C:\SqlXmlTest\multikey-join-xdr.xml"

  5. 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 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>