Article
12/05/2014

Using sql:field (XDR Schema)

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

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.Person table.

Because the attribute name BusinessEntityID in the XDR schema is the same as the BusinessEntityID column in the Person.Person 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.Person" >
    <AttributeType name="BusinessEntityID" />
    <AttributeType name="Email" />
    <attribute type="BusinessEntityID" />
    <attribute type="Email" sql:field="EmailAddress" />
</ElementType>
</Schema>

To test a sample XPath query against the schema

Copy the schema code above and paste it into a text file. Save the file as sqlFieldXdr.xml.
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 BusinessEntityID of 1.
```
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="sqlFieldXdr.xml">
    /Contacts[@BusinessEntityID="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"
```
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 BusinessEntityID="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.Person" >
    <AttributeType name="BusinessEntityID" />
    <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.Person 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.Person 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.Person" >
    <AttributeType name="BusinessEntityID" />

    <attribute type="BusinessEntityID" />
    <element type="Email" sql:field="EmailAddress" />
  </ElementType>
</Schema>

To test a sample XPath query against the schema

Copy the schema code above and paste it into a text file. Save the file as sqlFieldElementXdr.xml.
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 BusinessEntityID of 1.
```
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="sqlFieldElementXdr.xml">
    /Contacts[@BusinessEntityID="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"
```
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 BusinessEntityID="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.Person 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.Person" >
    <AttributeType name="BusinessEntityID" />

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

Share via

Using sql:field (XDR Schema)

Examples

A. Specify sql:field for an <attribute> of the XDR schema

To test a sample XPath query against the schema

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

To test a sample XPath query against the schema

See Also

Reference

Additional resources