Excluding Schema Elements from the Resulting XML Document Using sql:map-field (XDR Schema)

Because of the default mapping, every element and attribute in the XDR schema maps to a database table and column. At times, you may want create an element in the XDR schema that does not map to any database table or column and does not appear in the XML. This is done by specifying the sql:map-field annotation.

The sql:map-field annotation differs from sql:is-constant in that the unmapped elements and attributes do not appear in the XML document. sql:map-field is especially useful if the schema cannot be modified or is used to validate XML from other sources yet contains data that is not stored in your database.

sql:map-field takes a Boolean value (0 = FALSE, 1 = TRUE). The sql:map-field annotation is valid only on an <attribute>, <element>, or <ElementTypes> with text-only content (content=textOnly). The annotation is not valid on an <element> or <ElementTypes> that maps to a table.

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 the sql:map-field annotation

Assume you have an XDR schema from some other source. This XDR schema consists of <Person.Contact> element with ContactID, FirstName, LastName, and attributes.

In mapping this XDR schema to the Person.Contact table in the database, sql:map-field is specified on the HomeAddress attribute because the Person.Contact table does not store home addresses of employees. As a result, this attribute is not returned in the resulting XML document when an XPath query is specified against the mapping schema.

Default mapping takes place for the rest of the schema. The <Person.Contact> element maps to the Person.Contact table, and all the attributes map to the columns with the same name in the Person.Contact table. For more information about default mapping, see Default Mapping of XDR Elements and Attributes to Tables and Columns.

<?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" />
    <AttributeType name="HomeAddress" />

    <attribute type="ContactID" />
    <attribute type="FirstName" />
    <attribute type="LastName" />
    <attribute type="HomeAddress" sql:map-field="0" />
</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 mapField-xdr.xml.

2. Copy the following template and paste it into a text file. Save the file as mapField-xdrT.xml in the same directory where you saved mapField-xdr.xml. The query in the template selects the Person.Contact record with ContactID equal to 1.

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

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

   mapping-schema="C:\MyDir\mapField-xdr.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" FirstName="Gustavo" LastName = "Achong"/> 
</ROOT>

Note that the EmployeeID, FirstName, and LastName are present, but HomeAddress is not because the mapping schema specified a value of 0 for its sql:map-field attribute.

See Also

Reference

Using XPath Queries in SQLXML 4.0

Help and Information

Getting SQL Server 2005 Assistance