Condividi tramite

Specifying Relationships Using sql:relationship [SQLXML 4.0]

  • Articolo

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 XSD schema, a <Customer> element contains <Order> child elements. When the schema is mapped to the AdventureWorks database, the <Customer> element maps to the Sales.Customer table and the <Order> element maps to the Sales.SalesOrderHeader table. 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 the CustomerID primary key in the Sales.Customer table. You can establish these relationships among mapping schema elements by using the sql:relationship annotation.

In the annotated XSD schema, the sql:relationship annotation is used to nest the schema elements hierarchically, on the basis of 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 following:

  • The parent table (Sales.Customer) and the child table (Sales.SalesOrderHeader).
  • The column or columns that compose the relationship between the parent and child tables. For example, the CustomerID column, which appears in both the parent and child tables.

This information is used to generate the proper hierarchy.

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

  • Name
    Specifies the unique name of the relationship.
  • Parent
    Specifies the parent relation (table). This is an optional attribute; if the attribute is not specified, the parent table name is obtained from information in the child hierarchy in the document. If the schema specifies two parent-child hierarchies that use the same <sql:relationship> but different parent elements, you do not specify the parent attribute in <sql:relationship>. This information is obtained from the hierarchy in the schema.
  • parent-key
    Specifies the parent key of the parent. If the parent key is composed of multiple columns, values are specified with a space between them. There is a positional mapping between the values that are specified for the multicolumn key and for the corresponding child key.
  • Child
    Specifies the child relation (table).
  • child-key
    Specifies the child key in the child referring to parent-key in parent. If the child key is composed of multiple attributes (columns), the child-key values are specified with a space between them. There is a positional mapping between the values that are specified for the multicolumn key and for the corresponding parent key.

The sql:key-fields annotation must be specified in an element that contains a child element, that has a <sql:relationship> defined between the element and the child, and that does not provide the primary key of the table specified in the parent element. Even if the schema does not specify <sql:relationship>, you must specify sql:key-fields to produce the proper hierarchy. For more information, see Identifying Key Columns by Using sql:key-fields.

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

Examples

To create working samples using the following examples, you must meet certain requirements. For more information, see Requirements for Running SQLXML Examples.

A. Specifying the sql:relationship annotation on an element

The following annotated XSD 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 relationship itself is defined in the <xsd:appinfo> element.

The <relationship> element identifies CustomerID in the Sales.SalesOrderHeader table as a foreign key that refers to the CustomerID primary key in the Sales.Customer table. Therefore, orders that belong to a customer appear as a child element of that <Customer> element.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
  <xsd:appinfo>
    <sql:relationship name="CustOrders"
          parent="Sales.Customer"
          parent-key="CustomerID"
          child="Sales.SalesOrderHeader"
          child-key="CustomerID" />
  </xsd:appinfo>
</xsd:annotation>

  <xsd:element name="Customer" sql:relation="Sales.Customer" type="CustomerType" />
   <xsd:complexType name="CustomerType" >
     <xsd:sequence>
        <xsd:element name="Order" 
                     sql:relation="Sales.SalesOrderHeader"
                    sql:relationship="CustOrders" >
           <xsd:complexType>
              <xsd:attribute name="SalesOrderID" type="xsd:integer" />
              <xsd:attribute name="CustomerID" type="xsd:string" />
           </xsd:complexType>
        </xsd:element>
     </xsd:sequence>
        <xsd:attribute name="CustomerID"   type="xsd:string" /> 
    </xsd:complexType>

</xsd:schema>

The previous schema uses a named relationship. You can also specify an unnamed relationship. The results are same.

This is the revised schema in which an unnamed relationship is specified:

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

  <xsd:element name="Customer" sql:relation="Sales.Customer"  type="CustomerType" />
   <xsd:complexType name="CustomerType" >
     <xsd:sequence>
        <xsd:element name="Order" 
                     sql:relation="Sales.SalesOrderHeader">
           <xsd:annotation>
            <xsd:appinfo>
              <sql:relationship 
                parent="Sales.Customer"
                parent-key="CustomerID"
                child="Sales.SalesOrderHeader"
                child-key="CustomerID" />
            </xsd:appinfo>
           </xsd:annotation>
           <xsd:complexType>
              <xsd:attribute name="SalesOrderID" type="xsd:integer" />
              <xsd:attribute name="CustomerID" type="xsd:string" />
           </xsd:complexType>
        </xsd:element>
     </xsd:sequence>
        <xsd:attribute name="CustomerID"   type="xsd:string" /> 
    </xsd:complexType>

</xsd: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.xml.

  2. Copy the following template below and paste it into a text file. Save the file as sql-relationshipT.xml in the same directory where you saved sql-relationship.xml.

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

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

    mapping-schema="C:\MyDir\sql-relationship.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 Queries.

Here is the result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customer CustomerID="1"> 
    <Order OrderID="43860" CustomerID="1" /> 
    <Order OrderID="44501" CustomerID="1" /> 
    <Order OrderID="45283" CustomerID="1" /> 
    <Order OrderID="46042" CustomerID="1" /> 
  </Customer> 
</ROOT>

B. Specifying a relationship chain

For this example, assume that you want the following XML document using data obtained from the AdventureWorks database:

<Order SalesOrderID="43659">
  <Product Name="Mountain Bike Socks, M"/> 
  <Product Name="Sport-100 Helmet, Blue"/>
  ...
</Order>
...

For each order in the Sales.SalesOrderHeader table, the XML document has one <Order> element. And each <Order> element has a list of <Product> child elements, one for each product requested in the order.

To specify an XSD schema that will produce this hierarchy, you must specify two relationships: OrderOD and ODProduct. The OrderOD relationship specifies the parent-child relationship between the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables. The ODProduct relationship specifies the relationship between the Sales.SalesOrderDetail and Production.Product tables.

In the following schema, the msdata:relationship annotation on the <Product> element specifies two values: OrderOD and ODProduct. The order in which these values are specified is important.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:msdata="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
  <xsd:appinfo>
    <msdata:relationship name="OrderOD"
          parent="Sales.SalesOrderHeader"
          parent-key="SalesOrderID"
          child="Sales.SalesOrderDetail"
          child-key="SalesOrderID" />

    <msdata:relationship name="ODProduct"
          parent="Sales.SalesOrderDetail"
          parent-key="ProductID"
          child="Production.Product"
          child-key="ProductID" />
  </xsd:appinfo>
</xsd:annotation>

  <xsd:element name="Order" msdata:relation="Sales.SalesOrderHeader" 
               msdata:key-fields="SalesOrderID" type="OrderType" />
   <xsd:complexType name="OrderType" >
     <xsd:sequence>
        <xsd:element name="Product" msdata:relation="Production.Product" 
                     msdata:key-fields="ProductID"
                     msdata:relationship="OrderOD ODProduct">
          <xsd:complexType>
             <xsd:attribute name="Name" type="xsd:string" />
          </xsd:complexType>
        </xsd:element>
     </xsd:sequence>
        <xsd:attribute name="SalesOrderID"   type="xsd:integer" /> 
    </xsd:complexType>
</xsd:schema>

Instead of specifying a named relationship, you can specify an anonymous relationship. In this case, the entire contents of <annotation>...</annotation>, which describes the two relationships, appear as a child element of <Product>.

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

  <xsd:element name="Order" msdata:relation="Sales.SalesOrderHeader" 
               msdata:key-fields="SalesOrderID" type="OrderType" />

   <xsd:complexType name="OrderType" >
     <xsd:sequence>
        <xsd:element name="Product" msdata:relation="Production.Product" 
                     msdata:key-fields="ProductID" >
         <xsd:annotation>
          <xsd:appinfo>
           <msdata:relationship 
               parent="Sales.SalesOrderHeader"
               parent-key="SalesOrderID"
               child="Sales.SalesOrderDetail"
               child-key="SalesOrderID" />

           <msdata:relationship 
               parent="Sales.SalesOrderDetail"
               parent-key="ProductID"
               child="Production.Product"
               child-key="ProductID" />
         </xsd:appinfo>
       </xsd:annotation>
       <xsd:complexType>
          <xsd:attribute name="Name" type="xsd:string" />
       </xsd:complexType>
     </xsd:element>
   </xsd:sequence>
   <xsd:attribute name="SalesOrderID"   type="xsd:integer" /> 
  </xsd:complexType>
 </xsd: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 relationshipChain.xml.

  2. Copy the following template below and paste it into a text file. Save the file as relationshipChainT.xml in the same directory where you saved relationshipChain.xml.

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

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

    mapping-schema="C:\MyDir\relationshipChain.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 Queries.

Here is the result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Order SalesOrderID="43659">
    <Product Name="Mountain Bike Socks, M" /> 
    <Product Name="Sport-100 Helmet, Blue" /> 
    <Product Name="AWC Logo Cap" /> 
    <Product Name="Long-Sleeve Logo Jersey, M" /> 
    <Product Name="Long-Sleeve Logo Jersey, XL" /> 
    ...
  </Order>
  ...
</ROOT>

C. Specifying the relationship annotation on an attribute

The schema in this example includes a <Customer> element with a <CustomerID> child element and an OrderIDList attribute of IDREFS type. The <Customer> element maps to the Sales.Customer table in the AdventureWorks database. By default, the scope of this mapping applies to all the child elements or attributes unless sql:relation is specified on the child element or attribute, in which case, the appropriate primary-key/foreign-key relationship must be defined using the <relationship> element. And the child element or attribute, which specifies the different table using the relation annotation, must also specify the relationship annotation.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
  <xsd:appinfo>
    <sql:relationship name="CustOrders"
          parent="Sales.Customer"
          parent-key="CustomerID"
          child="Sales.SalesOrderHeader"
          child-key="CustomerID" />
     </xsd:appinfo>
</xsd:annotation>

  <xsd:element name="Customer" sql:relation="Sales.Customer" type="CustomerType" />
   <xsd:complexType name="CustomerType" >
     <xsd:sequence>
        <xsd:element name="CustomerID"   type="xsd:string" /> 
     </xsd:sequence>
     <xsd:attribute name="OrderIDList" 
                     type="xsd:IDREFS" 
                     sql:relation="Sales.SalesOrderHeader" 
                     sql:field="SalesOrderID"
                     sql:relationship="CustOrders" >
        </xsd:attribute>
    </xsd:complexType>
</xsd: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 relationship-on-attribute.xml.

  2. Copy the following template and paste it into a file. Save the file as relationship-on-attributeT.xml in the same directory where you saved relationship-on-attribute.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="relationship-on-attribute.xml">
    /Customer[CustomerID=1]
  </sql:xpath-query>
</ROOT>

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

    mapping-schema="C:\MyDir\relationship-on-attribute.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 Queries.

Here is the result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customer OrderIDList="43860 44501 45283 46042">
    <CustomerID>1</CustomerID> 
  </Customer>
</ROOT>

D. Specifying sql:relationship on multiple elements

In this example, the annotated XSD schema contains the <Customer>, <Order>, and <OrderDetail> elements.

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

The <Order> element includes the <OrderDetail> child element. <sql:relationship> is specified on <OrderDetail> child element, so the order details that pertain to an order appear as child elements of that <Order> element.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
  <xsd:appinfo>
    <sql:relationship name="CustOrders"
        parent="Sales.Customer"
        parent-key="CustomerID"
        child="Sales.SalesOrderHeader"
        child-key="CustomerID" />

    <sql:relationship name="OrderOrderDetail"
        parent="Sales.SalesOrderHeader"
        parent-key="SalesOrderID"
        child="Sales.SalesOrderDetail"
        child-key="SalesOrderID" />
  </xsd:appinfo>
</xsd:annotation>

  <xsd:element name="Customer" sql:relation="Sales.Customer" >
   <xsd:complexType>
     <xsd:sequence>
        <xsd:element name="Order" sql:relation="Sales.SalesOrderHeader"  
              sql:relationship="CustOrders" maxOccurs="unbounded" >
          <xsd:complexType>
              <xsd:sequence>
                <xsd:element name="OrderDetail" 
                             sql:relation="Sales.SalesOrderDetail" 
                             sql:relationship="OrderOrderDetail" 
                             maxOccurs="unbounded" >
                  <xsd:complexType>
                    <xsd:attribute name="SalesOrderID" type="xsd:integer" />
                    <xsd:attribute name="ProductID" type="xsd:string" />
                    <xsd:attribute name="OrderQty" type="xsd:integer" />
                  </xsd:complexType>
                </xsd:element>
              </xsd:sequence>
              <xsd:attribute name="SalesOrderID" type="xsd:integer" />
              <xsd:attribute name="OrderDate" type="xsd:date" />
              <xsd:attribute name="CustomerID" type="xsd:string" />
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
      <xsd:attribute name="CustomerID" type="xsd:string" />
     </xsd:complexType>
  </xsd:element>
</xsd: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 relationship-multiple-elements.xml.

  2. Copy the following template and paste it into a text file. Save the file as relationship-multiple-elementsT.xml in the same directory where you saved relationship-multiple-elements.xml. The query in the template returns order information for a customer with the CustomerID of 1 and SalesOrderID of 43860.

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

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

    mapping-schema="C:\MyDir\relationship-multiple-elements.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 Queries.

Here is the result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Order SalesOrderID="43860" OrderDate="2001-08-01" CustomerID="1">
     <OrderDetail SalesOrderID="43860" ProductID="761" OrderQty="2" /> 
     <OrderDetail SalesOrderID="43860" ProductID="770" OrderQty="1" /> 
     <OrderDetail SalesOrderID="43860" ProductID="758" OrderQty="2" /> 
     <OrderDetail SalesOrderID="43860" ProductID="765" OrderQty="2" /> 
     <OrderDetail SalesOrderID="43860" ProductID="732" OrderQty="1" /> 
     <OrderDetail SalesOrderID="43860" ProductID="762" OrderQty="1" /> 
     <OrderDetail SalesOrderID="43860" ProductID="738" OrderQty="1" /> 
     <OrderDetail SalesOrderID="43860" ProductID="768" OrderQty="1" /> 
     <OrderDetail SalesOrderID="43860" ProductID="753" OrderQty="2" /> 
     <OrderDetail SalesOrderID="43860" ProductID="729" OrderQty="1" /> 
     <OrderDetail SalesOrderID="43860" ProductID="763" OrderQty="1" /> 
     <OrderDetail SalesOrderID="43860" ProductID="756" OrderQty="1" /> 
  </Order>
</ROOT>

E. Specifying the <sql:relationship> without the parent attribute

This example illustrates specifying the <sql:relationship> without the parent attribute. For example, assume you have the following employee tables:

Emp1(SalesPersonID, FirstName, LastName, ReportsTo)
Emp2(SalesPersonID, FirstName, LastName, ReportsTo)

The following XML view has the <Emp1> and <Emp2> elements mapping to the Sales.Emp1 and Sales.Emp2 tables:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
  <xsd:appinfo>
    <sql:relationship name="EmpOrders"
          parent-key="SalesPersonID"
          child="Sales.SalesOrderHeader"
          child-key="SalesPersonID" />
     </xsd:appinfo>
</xsd:annotation>

  <xsd:element name="Emp1" sql:relation="Sales.Emp1" type="EmpType" />
  <xsd:element name="Emp2" sql:relation="Sales.Emp2" type="EmpType" />
   <xsd:complexType name="EmpType" >
     <xsd:sequence>
        <xsd:element name="Order" 
                     sql:relation="Sales.SalesOrderHeader" 
                     sql:relationship="EmpOrders" >
          <xsd:complexType>
             <xsd:attribute name="SalesOrderID" type="xsd:integer" />
             <xsd:attribute name="CustomerID" type="xsd:string" />
          </xsd:complexType>
        </xsd:element>
     </xsd:sequence>
        <xsd:attribute name="SalesPersonID"   type="xsd:integer" /> 
        <xsd:attribute name="LastName"   type="xsd:string" /> 
    </xsd:complexType>

</xsd:schema>

In the schema, both the <Emp1> element and <Emp2> element are of type EmpType. The type EmpType describes an <Order> child element and the corresponding <sql:relationship>. In this case, there is no single parent that can be identified in <sql:relationship> by using the parent attribute. In this situation, you don't specify the parent attribute in <sql:relationship>; the parent attribute information is obtained from the hierarchy in the schema.

To test a sample XPath query against the schema

  1. Create these tables in the AdventureWorks database:

    USE AdventureWorks
CREATE TABLE Sales.Emp1 (
       SalesPersonID int primary key, 
       FirstName  varchar(20), 
       LastName   varchar(20), 
       ReportsTo int)
Go
CREATE TABLE Sales.Emp2 (
       SalesPersonID int primary key, 
       FirstName  varchar(20), 
       LastName   varchar(20), 
       ReportsTo int)
Go

  2. Add this sample data in the tables:

    INSERT INTO Sales.Emp1 values (279, 'Nancy', 'Devolio',NULL)
INSERT INTO Sales.Emp1 values (282, 'Andrew', 'Fuller',1)
INSERT INTO Sales.Emp1 values (276, 'Janet', 'Leverling',1)
INSERT INTO Sales.Emp2 values (277, 'Margaret', 'Peacock',3)
INSERT INTO Sales.Emp2 values (283, 'Steven', 'Devolio',4)
INSERT INTO Sales.Emp2 values (275, 'Nancy', 'Buchanan',5)
INSERT INTO Sales.Emp2 values (281, 'Michael', 'Suyama',6)

  3. Copy the schema code above and paste it into a text file. Save the file as relationship-noparent.xml.

  4. Copy the following template and paste it into a text file. Save the file as relationship-noparentT.xml in the same directory where you saved relationship-noparent.xml. The query in the template selects all the <Emp1> elements (therefore, the parent is Emp1).

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

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

    mapping-schema="C:\MyDir\relationship-noparent.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 Queries.

Here is a partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<Emp1 SalesPersonID="276" LastName="Leverling">
  <Order SalesOrderID="43663" CustomerID="510" /> 
  <Order SalesOrderID="43666" CustomerID="511" /> 
  <Order SalesOrderID="43859" CustomerID="259" />
  ...
</Emp1>