Using sql:relation (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).

The sql:relation annotation is added to map an XML node in the XDR schema to a database table. A table/view name is specified as the value of sql:relation annotation.

The sql:relation annotation can be added to an <ElementType>, <element>, or <attribute> node in the XDR schema. sql:relation specifies the mapping between <ElementType>, <element>, or <attribute> in the schema to a table/view in a database.

When sql:relation is specified on <ElementType>, the scope of this annotation applies to all the attribute and child element specifications in that <ElementType>. Therefore, it provides a shortcut in writing annotations. When sql:relation is specified directly on the <element>, there is also scoping introduced to attributes specified within an <ElementType>. The sql:relation annotation is ignored on <AttributeType>.

The sql:relation annotation is useful in cases in which identifiers that are valid in Microsoft SQL Server are invalid in XML. For example, "Order Details" is a valid table name in SQL Server but invalid in XML. In such cases, the sql:relation annotation can be used to specify the mapping, for example:

<ElementType name="OD" sql:relation="[Order Details]">

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:relation on <ElementType> containing attributes

In this example, the XDR schema consists of a <Contacts> element with ContactID, FirstName, and LastName attributes. The sql:relation annotation is specified on the <ElementType>, mapping the <Contacts> element to the Person.Contact table. The scope of this mapping applies to all the attributes in the <ElementType>. Therefore, all the attributes map to columns in the Person.Contact table.

The default mapping takes places for the attributes; for example, the attributes map to columns with the same names in the Person.Contact 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="Contacts" sql:relation="Person.Contact" >
    <AttributeType name="ContactID" />
    <AttributeType name="FirstName" />
    <AttributeType name="LastName" />

    <attribute type="ContactID" />
    <attribute type="FirstName" />
    <attribute type="LastName" />
  </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 sqlRelationXdr.xml.

  2. Copy the following template and paste it into a text file. Save the file as sqlRelationXdrT.xml in the same directory where you saved sqlRelationXdr.xml. The query in the template selects a contact with the ContactID of 1.

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

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

    mapping-schema="C:\MyDir\sqlRelationXdr.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"> 
    <Contacts ContactID="1" FirstName="Gustavo" 
              LastName="Achong" /> 
</ROOT>

B. Specify sql:relation on <ElementType> containing subelements and attributes

In this example, the XDR schema consists of <Contacts> element with the ContactID attribute and <FirstName> and <LastName> child elements. The sql:relation annotation is specified on the <ElementType>, mapping the <Contacts> element to the Person.Contact table. The scope of this mapping applies to all the attributes in the <ElementType>. Therefore, all the attributes map to columns in the Person.Contact table.

The default mapping takes places for the attributes. The attributes map to columns with the same name in the Person.Contact table.

In this example, the content attribute is specified on the <FirstName> and <LastName> child elements. Without the content=textOnly attribute, the child elements would not map to the respective FirstName and LastName columns in the Person.Contact table because, by default, elements map to a table and not to a field.

<?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="FirstName" content="textOnly"/>
  <ElementType name="LastName" content="textOnly"/>
  <ElementType name="Contacts" sql:relation="Person.Contact" >
    <AttributeType name="ContactID" />

    <attribute type="ContactID" />
    <element type="FirstName" />
    <element type="LastName"  />
  </ElementType>
</Schema>

As an alternative, instead of specifying content=textOnly attribute, you can specify sql:field annotation in the element definition to map the child elements (FirstName, LastName) to the corresponding column names, such as the following alternate examples:

<element type="FirstName" sql:field="FirstName" />
<element type="LastName" sql:field="LastName" />

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 UsingSqlRelationXdr.xml.

  2. Copy the following template and paste it into a text file. Save the file as UsingSqlRelationXdrT.xml in the same directory where you saved UsingSqlRelationXdr.xml. The query in the template selects a contact with the ContactID of 1.

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

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

    mapping-schema="C:\MyDir\UsingSqlRelationXdr.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"> 
  <Contacts ContactID="1"> 
    <FirstName>Gustavo</FirstName>
    <LastName>Achong</LastName>
  </Contacts>
</ROOT>

See Also

Reference

Using XPath Queries in SQLXML 4.0

Help and Information

Getting SQL Server 2005 Assistance