Default Mapping of XDR Elements and Attributes to Tables and Columns

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

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

You can map the noncomplex child elements 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 Explicit Mapping of XDR Elements and Attributes to Tables and Columns.

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

In this example, the <Person.Contact> element maps to the Person.Contact table in the AdventureWorks database, and all the attributes map to same name columns in the Person.Contact 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="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 DefaultXdr.xml.

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

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:xpath-query mapping-schema="DefaultXdr.xml">
        /Person.Contact[@ContactID &lt; 3]
    </sql:xpath-query>
</ROOT>

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

    mapping-schema="C:\MyDir\DefaultXdr.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 result set:

<?xml version="1.0" encoding="UTF-8" ?>
<ROOT>
  <Person.Contact ContactID="1" FirstName="Gustavo" LastName="Achong"/>
  <Person.Contact ContactID="2" FirstName="Catherine" LastName="Abel">
</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, the content attribute is specified with a value of textOnly.

This XDR schema consists of the same <Person.Contact> element as in the previous example, but the SQL fields (ContactID, FirstName, LastName) mapped to child elements instead of attributes.

<?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="ContactID" content="textOnly" />
<ElementType name="FirstName" content="textOnly" />
<ElementType name="LastName"  content="textOnly" />

<ElementType name="Person.Contact" >
    <element type="ContactID" />
    <element type="FirstName" />
    <element type="LastName"  />
</ElementType>
</Schema>

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

Note

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

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

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

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:xpath-query mapping-schema="ElementXdr.xml">
        /Person.Contact[ContactID &lt; 3]
    </sql:xpath-query>
</ROOT>

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

    mapping-schema="C:\MyDir\ElementXdr.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 result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Person.Contact>
    <ContactID>1</ContactID> 
    <FirstName>Gustavo</FirstName> 
    <LastName>Achong</LastName> 
  </Person.Contact>
  <Person.Contact>
    <ContactID>2</ContactID> 
    <FirstName>Catherine</FirstName> 
    <LastName>Abel</LastName> 
  </Person.Contact>
</ROOT>

See Also

Reference

Using XPath Queries in SQLXML 4.0

Help and Information

Getting SQL Server 2005 Assistance