Filtering Values Using sql:limit-field and sql:limit-value

XML and Internet Support

previous page next page
XML and Internet Support

Filtering Values Using sql:limit-field and sql:limit-value

You can limit rows returned from a database query based on some limiting value. These annotations are used to identify the database column that contains the limiting values and to specify a specific limiting value to be used to filter the data returned.

The sql:limit-field annotation is used to identify a column that contains a limiting value. sql:limit-field is used to qualify the join relationship specified using <sql:relationship>. sql:limit-field must be used on an element or attribute that has <sql:relationship> specified.

The sql:limit-value annotation is used to specify the limited value in the column specified in a sql:limit-field annotation. This annotation is optional. If sql:limit-value is not specified, a null value is assumed.

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. Limit the customer addresses returned to a specific address type

In this example, a database contains two tables:

  • Customer (CustomerID, CompanyName)

  • Addresses (CustomerID, AddressType, StreetAddress)

A customer can have a shipping and/or a billing address (the AddressType column values are Shipping and Billing).

This is the mapping schema in which the ShipTo schema attribute maps to StreetAddress column in the Addresses relation. The values returned for this attribute are limited to only Shipping addresses by specifying the sql:limit-field and sql:limit-value annotations. Similarly, the BillTo schema attribute returns only the Billing address of a customer.

This is the schema:

<?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="Customer" >
    <AttributeType name="CustomerID" />
    <AttributeType name="CompanyName" />
    <AttributeType name="BillTo" />
    <AttributeType name="ShipTo" />

    <attribute type="CustomerID" />
    <attribute type="CompanyName" />
    <attribute type="BillTo" 
                sql:limit-field="AddressType"
                sql:limit-value="billing"
                sql:field="StreetAddress"
                sql:relation="Addresses" >
                <sql:relationship 
                        key="CustomerID"
                        key-relation="Customer"
                       foreign-relation="Addresses"
                       foreign-key="CustomerID" />
    </attribute>
    <attribute type="ShipTo" 
                sql:limit-field="AddressType"
                sql:limit-value="shipping"
                sql:field="StreetAddress"
                sql:relation="Addresses" >
                <sql:relationship 
                     key="CustomerID"
                     key-relation="Customer"
                     foreign-relation="Addresses"
                     foreign-key="CustomerID" />
    </attribute>
</ElementType>
</Schema>

Testing a sample XPath query against the schema

  1. Create two tables: 
    CREATE TABLE Customer (CustomerID int primary key, 
                       CompanyName varchar(30))
CREATE TABLE Addresses(CustomerID int, 
                       StreetAddress varchar(50),
                       AddressType varchar(10))
  2. Add the sample data: 
    INSERT INTO Customer values (1, 'Company A')
INSERT INTO Customer values (2, 'Company B')

INSERT INTO Addresses values
            (1, 'Obere Str. 57 Berlin', 'billing')
INSERT INTO Addresses values
            (1, 'Avda. de la Constitución 2222México D.F.', 'shipping')
INSERT INTO Addresses values
            (2, '120 Hanover Sq., London', 'billing')
INSERT INTO Addresses values
            (2, 'Forsterstr. 57, Mannheim', 'shipping')
  3. 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).

  4. 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="MySchemaT.xml">
    /Customer
  </sql:xpath-query>
</ROOT>

    The directory path specified for the mapping schema is relative to the directory associated with the template virtual name. An absolute path can also be specified, for example:

    mapping-schema="C:\MyDir\MySchema.xml"
  5. Execute the following URL: 
    http://IISServer/VirtualRoot/TemplateVirtualName/MySchemaT.xml

This is the result:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customer CustomerID="1" CompanyName="Company A" 
          BillTo="Obere Str. 57 Berlin" 
          ShipTo="Avda. de la Constitución 2222México D.F." /> 
  <Customer CustomerID="2" CompanyName="Company B" 
          BillTo="120 Hanover Sq., London" 
          ShipTo="Forsterstr. 57, Mannheim" /> 
</ROOT>

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