Using sql:field

XML and Internet Support

previous page next page
XML and Internet Support

Using sql:field

The sql:field annotation specifies the mapping between element or attribute in an annotated schema to a column in a database. sql:field can be added to an element or attribute. sql:field 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 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 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 Company attribute in the schema to the CompanyName column in the Customers table.

Because the attribute name CustomerID in the XDR schema is the same as the CustomerID column in the Customers 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="Customer" sql:relation="Customers" >
    <AttributeType name="CustomerID" />
    <AttributeType name="Company" />
    
    <attribute type="CustomerID" />
    <attribute type="Company" sql:field="CompanyName" />
</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. The query in the template selects a customer with the CustomerID of ALFKI. 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="MySchema.xml">
    /Customer[@CustomerID="ALFKI"]
  </sql:xpath-query>
</ROOT>
  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"> 
  <Customer CustomerID="ALFKI" Company="Alfreds Futterkiste" /> 
</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 Contact 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="Contact" />

<ElementType name="Customer" sql:relation="Customers" >
    <AttributeType name="CustomerID" />
    <AttributeType name="Company" />
    
    <attribute type="CustomerID" />
    <attribute type="Company" sql:field="CompanyName" />
    <attribute type="Contact" sql:field="ContactName" />
</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 <CompanyName> subelement in the schema to the CompanyName column in the Customers table.

Without the explicit annotation, the <CompanyName> subelement of the <Customer> element in the schema will not map to the CompanyName column of the Customers 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="CompanyName" />
  <ElementType name="Customer" sql:relation="Customers" >
    <AttributeType name="CustomerID" />

    <attribute type="CustomerID" />
    <element type="CompanyName" sql:field="CompanyName" />
  </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 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. The query in the template selects a customer with the CustomerID of ALFKI. 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="MySchema.xml">
    /Customer[@CustomerID="ALFKI"]
  </sql:xpath-query>
</ROOT>
  3. This URL executes the template: 
    http://IISServer/VirtualRoot/template/Schema6T.xml
  4. Here is the partial result set: 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customer CustomerID="ALFKI"> 
    <CompanyName>Alfreds Futterkiste</CompanyName> 
  </Customer> 
</ROOT>



If content="textOnly" is specified on CompanyName <ElementType>, the sql:field annotation is not required on the <CompanyName> subelement. The CompanyName subelement will map to the CompanyName column in the Customer 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="CompanyName" content="textOnly" />
  <ElementType name="Customer" sql:relation="Customers" >
    <AttributeType name="CustomerID" />

    <attribute type="CustomerID" />
    <element type="CompanyName" />
  </ElementType>
</Schema>



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