Using sql:relation

XML and Internet Support

Using sql:relation

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 subelement 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>. sql:relation is ignored on <AttributeType>.

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

In this example, the XDR schema consists of <Customer> element with CustomerID, ContactName, and Phone attributes. The sql:relation annotation is specified on the <ElementType>, mapping Customer element to the Customers table. The scope of this mapping applies to all the attributes in the <ElementType>. Therefore, all the attributes map to columns in the Customers table.

The default mapping takes places for the attributes, for example, the attributes map to same name columns in the Customers 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="Customer" sql:relation="Customers" >
    <AttributeType name="CustomerID" />
    <AttributeType name="ContactName" />
    <AttributeType name="Phone" />

    <attribute type="CustomerID" />
    <attribute type="ContactName" />
    <attribute type="Phone" />
  </ElementType>
</Schema>

Testing a sample XPath query against the schema

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

This URL executes the template:

http://IISServer/VirtualRoot/template/MySchemaT.xml

Here is the partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
    <Customer CustomerID="ALFKI" ContactName="Maria Anders" 
              Phone="030-0074321" /> 
</ROOT>

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

In this example, the XDR schema consists of <Customer> element with CustomerID, ContactName attributes and <Address> subelement. The sql:relation annotation is specified on the <ElementType>, mapping Customer element to the Customers table. The scope of this mapping applies to all the attributes in the <ElementType>. Therefore, all the attributes map to columns in the Customers table.

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

In this example, the content attribute is specified on the <Address> subelement. Without the content=textOnly attribute, the <Address> element does not map to the address column in the Customers 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="Address" content="textOnly"  />
  <ElementType name="Customer" sql:relation="Customers" >
    <AttributeType name="CustomerID" />
    <AttributeType name="ContactName" />

    <attribute type="CustomerID" />
    <attribute type="ContactName" />
    <element type="Address"  />
  </ElementType>
</Schema>

As an alternative, instead of specifying content=textOnly attribute, you can specify sql:field annotation to map the <Address> subelement to the Address column:

<element type="Address" sql:field="Address" >

Testing a sample XPath query against the schema

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

This URL executes the template:

http://IISServer/VirtualRoot/template/MySchemaT.xml

Here is the partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customer CustomerID="ALFKI" ContactName="Maria Anders"> 
    <Address>Obere Str. 57</Address> 
  </Customer> 
</ROOT>

Using sql:relation

XML and Internet Support