Default Mapping of XDR Elements and Attributes to Tables and Columns

In an annotated XDR schema, an <element>, by default, maps to the same name table/view, and an attribute maps to the same name column.

You can map the noncomplex subelements in the schema to the database columns. To map an <element> to a column in the database, the content attribute is specified for that element with the textOnly value. If content=textOnly is not specified in mapping an <element> to a database column, the sql:field annotation must be explicitly specified to map the <element> to a database column. For more information, see Using sql:field.

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 default mapping

In this example, the <Employees> element maps to the Employees table in the Northwind database, and all the attributes map to same name columns in the Employees table. In this XDR schema, no annotations are specified.

<?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="Employees" >
    <AttributeType name="EmployeeID" />
    <AttributeType name="FirstName" />
    <AttributeType name="LastName" />

    <attribute type="EmployeeID" />
    <attribute type="FirstName" />
    <attribute type="LastName" />
</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:
```
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:xpath-query mapping-schema="MySchema.xml">
        /Employees
    </sql:xpath-query>
</ROOT>
```
The directory path specified for the mapping schema (MySchema.xml) 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:

<?xml version="1.0" encoding="UTF-8" ?>
<ROOT>
  <Employees EID="1" FirstName="Nancy" LastName="Davolio"></Employee>
  <Employees EID="2" FirstName="Andrew" LastName="Fuller"></Employee>
</ROOT>

B. Map an XML <element> to a database column

By default, an XML <element> maps to a database table, and an <attribute> maps to database column. To map an <element> to a database column, content attribute is specified with textOnly value.

This XDR schema consists of <Employees> element with <FirstName> and <LastName> subelements and an EmployeeID attribute.

<?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="Employees" >
    <AttributeType name="EmployeeID" />
    
    <attribute type="EmployeeID" />
    <element type="FirstName" />
    <element type="LastName"  />
</ElementType>
</Schema>

By default the <Employees> element in the XDR schema maps to the Employees table in the database. The content attribute is specified on <FirstName> and <LastName> subelements. Therefore, these subelements will map to the same name columns in the Employees table.

Note Mixed content (elements with both text and subelements) is not supported.