Default Mapping of XDR Elements and Attributes to Tables and Columns

XML and Internet Support

previous page next page
XML and Internet Support

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

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

  2. 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"
  3. This URL executes the template: 
    http://IISServer/VirtualRoot/template/MySchemaT.xml
  4. 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.

Testing a sample XPath query against the schema

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

  2. 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 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"
  3. This URL executes the template: 
    http://IISServer/VirtualRoot/template/MySchemaT.xml
  4. Here is the partial result set: 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Employees EmployeeID="1">
    <FirstName>Nancy</FirstName>
    <LastName>Davolio</LastName>
  </Employees>
</ROOT>

See Also

IIS Virtual Directory Management for SQL Server

Using XPath Queries

Accessing SQL Server Using HTTP

Executing Template Files Using HTTP

previous page start next page