Using sql:field (XDR Schema)

The sql:field annotation specifies the mapping between an element or attribute in an annotated schema to a column in a database, and can be added to an element or attribute. The sql:field annotation is ignored on <AttributeType> elements of the annotated schema. The sql:field attribute specifies the name of the mapped column in a table or view.

For example, sql:field can be used to specify the name of column when that name does not match with the field in schema specified in XDR. The value of sql:field must be a column name. Four-part column names such as database.owner.table.columnname are not allowed. This is true for all annotations that take a column name as its value.

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 sql:field for an <attribute> of the XDR schema

In this annotated schema, the sql:field annotation is specified on the <attribute> element of the schema. The sql:field attribute maps the Email attribute in the schema to the EmailAddress column in the Person.Contact table.

Because the attribute name ContactID in the XDR schema is the same as the ContactID column in the Person.Contact table, sql:field is not specified. The mapping is by default.

<?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="Contacts" sql:relation="Person.Contact" >
    <AttributeType name="ContactID" />
    <AttributeType name="Email" />
    <attribute type="ContactID" />
    <attribute type="Email" sql:field="EmailAddress" />
</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 sqlFieldXdr.xml.

2. Copy the following template and paste it into a text file. Save the file as sqlFieldXdrT.xml in the same directory where you saved sqlFieldXdr.xml. The query in the template selects a customer with the ContactID of 1.

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

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

   mapping-schema="C:\SqlXmlTest\sqlFieldXdr.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 partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Contacts ContactID="1" Email="gustavo0@adventure-works.com" /> 
</ROOT>

In a mapping schema, attributes can be globally declared (for example, <AttributeType...>, declared outside the scope of the <ElementType>), and then referenced in <attribute type=...>, as shown in this schema.

In this schema, the LastName attribute is declared globally and referenced in the scope of the Customer <ElementType>.

<?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">
<AttributeType name="LastName" />
<ElementType name="Contacts" sql:relation="Person.Contact" >
    <AttributeType name="ContactID" />
    <AttributeType name="FName" />
    <AttributeType name="LName" />
    
    <attribute type="CustomerID" />
    <attribute type="FName" sql:field="FirstName" />
    <attribute type="LName" sql:field="LastName" />
</ElementType>
</Schema>

B. Specify sql:field for an <element> in the XDR schema

In this annotated schema, the sql:field annotation is specified on <element> in the schema. The sql:field annotation maps the <Email> child element in the schema to the EmailAddress column in the Person.Contact table.

Without the explicit annotation, the <Email> child element of the <Contacts> element in the schema will not map to the EmailAddress column of the Person.Contact table because the default mapping of elements is to a relation, not to a field (the exception to this occurs when the <ElementType> contains a textOnly 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="Email" />
  <ElementType name="Contacts" sql:relation="Person.Contact" >
    <AttributeType name="ContactID" />

    <attribute type="ContactID" />
    <element type="Email" sql:field="EmailAddress" />
  </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 sqlFieldElementXdr.xml.

2. Copy the following template and paste it into a text file. Save the file as sqlFieldElementXdrT.xml in the same directory where you saved sqlFieldElementXdr.xml. The query in the template selects a customer with the ContactID of 1.

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

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

   mapping-schema="C:\SqlXmlTest\sqlFieldElementXdr.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 partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Contacts ContactID="1"> 
    <Email>gustavo0@adventure-works.com</Email> 
  </Contacts> 
</ROOT>

If content="textOnly" is specified on the email address <ElementType> and the default name of the SQL column is used ("EmailAddress"), the sql:field annotation is not required on the child element. In this case, the <EmailAddress> child element will map to the EmailAddress column in the Person.Contact 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="EmailAddress" content="textOnly" />
  <ElementType name="Contacts" sql:relation="Person.Contact" >
    <AttributeType name="ContactID" />

    <attribute type="ContactID" />
    <element type="EmailAddress" />
  </ElementType>
</Schema>

See Also

Reference

Using XPath Queries in SQLXML 4.0

Help and Information

Getting SQL Server 2005 Assistance