Passing Parameters to Templates

SQL queries requiring parameter values can be specified in templates. The <sql:header> tag is specified to define parameters. The parameters can be assigned default values. The default parameter values are used when a template is executed without specifying parameter values.

Templates can also be used to specify XPath queries against annotated XDR (XML-Data Reduced) schemas. The <sql:xpath-query> tag is used to specify the XPath query. For more information and an example, see Using XPath Queries.

Examples

In the following examples, nwind is a virtual directory created using the IIS Virtual Directory Management for SQL Server utility, and template is the virtual name of template type defined when the virtual directory is created (any name can be given to a virtual name when it is created). For more information, see Using IIS Virtual Directory Management for SQL Server Utility.

A. Specify default parameters in a template

Parameter values can be assigned default values in a template. The stored procedure in this example requires one input parameter (@CategoryName).

IF EXISTS (SELECT name FROM sysobjects
   WHERE name = 'CategoryInfoWithInputParam' AND type = 'P')
   DROP PROCEDURE CategoryInfoWithInputParam
GO
CREATE PROCEDURE CategoryInfoWithInputParam
                    @CategoryName varchar(35)
AS
    SELECT     CategoryName, Description
    FROM     Categories
    WHERE    Categories.CategoryName = @CategoryName for xml auto

A template with a call to execute the stored procedure can be created as shown in the following example. The template specifies a default value (Condiments) for the parameter @CategoryName.

<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
  <sql:header>
     <sql:param name='CategoryName'>Condiments</sql:param>  
  </sql:header>                                          
  <sql:query >                                              
      exec CategoryInfoWithInputParam @CategoryName
  </sql:query>
</ROOT>

This template is saved in a file (File1.xml) and executed using a URL:

http://IISServer/nwind/template/File1.xml

Because no parameters are passed to the file, the default value (Condiments) is used.

This is the result:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Categories CategoryName="Condiments" Description="Sweet and savory 
       sauces, relishes, spreads, and seasonings" /> 
</ROOT>

The template can be also be executed directly at the URL:

http://IISServer/nwind?template=<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:header><sql:param name='CategoryName'>Condiments</sql:param></sql:header><sql:query >exec CategoryInfoWithInputParam @CategoryName</sql:query></ROOT>

Caution  Specifying queries or templates directly in the URL is not recommended for security reasons.

B. Pass a parameter value to a template

Parameters can be passed to template files. In this example, the stored procedure requires one input parameter (@CategoryName).

IF EXISTS (SELECT name FROM sysobjects
   WHERE name = 'CategoryInfoWithInputParam' AND type = 'P')
   DROP PROCEDURE CategoryInfoWithInputParam
GO
CREATE PROCEDURE CategoryInfoWithInputParam
                    @CategoryName varchar(35)
AS
    SELECT     CategoryName, Description
    FROM     Categories
    WHERE    Categories.CategoryName = @CategoryName for xml auto

The stored procedure is called in the template as shown in the example that follows. The template specifies a default value for the parameter @CategoryName.

<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
  <sql:header>
       <sql:param name='CategoryName'>Condiments</sql:param>  
  </sql:header>                                          
  <sql:query >                                              
      exec CategoryInfoWithInputParam @CategoryName
  </sql:query>
</ROOT>

This template is stored in a file (File1.xml) and executed using a URL:

http://IISServer/nwind/template/File1.xml?CategoryName=Beverages

If a parameter value is passed to the file at run time, the specified value is used instead of the default value. In the following call to execute a template file, the value Beverages is passed to the file.

This is the result:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
    <Categories CategoryName="Beverages" Description="Soft drinks, 
           coffees, teas, beers, and ales" /> 
</ROOT>

The template can be executed directly using a URL:

http://IISServer/nwind?template=<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:header><sql:param name='CategoryName'>Condiments</sql:param></sql:header><sql:query >exec CategoryInfoWithInputParam @CategoryName</sql:query></ROOT>&CategoryName=Beverages

Caution  Specifying queries or templates directly in the URL is not recommended for security reasons.

C. Pass multiple parameters to a template

Multiple parameters can be passed to a template. In this example, two parameters with default values are specified in <sql:header>. The template also specifies two queries that require parameter values.

This template consists of two SQL queries, each of which takes one parameter:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:header>
       <sql:param name='CustomerID'>ALFKI</sql:param>
     <sql:param name='EmployeeID'>1</sql:param> 
  </sql:header>
  <sql:query>
     SELECT  CustomerID,CompanyName 
     FROM    Customers 
     WHERE   CustomerID=@CustomerID 
     FOR XML AUTO
  </sql:query>
  <sql:query>
     SELECT EmployeeID,LastName,FirstName 
     FROM Employees 
     WHERE EmployeeID=@EmployeeID 
     FOR XML AUTO
  </sql:query>
</ROOT>

This template is stored in a file (File1.xml) and executed using a URL:

http://IISServer/nwind/template/template5.xml

This is the result:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" /> 
  <Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy" /> 
</ROOT>

In the following example, only the CustomerID parameter value is provided. Therefore, the default customer ID value ALFKI is ignored. Because no value is provided for EmployeeID parameter, the default value is used.

http://IISServer/nwind/template/template5.xml?CustomerID=BERGS

This is the result:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customers CustomerID="BERGS" CompanyName="Berglunds snabbköp" /> 
  <Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy" /> 
</ROOT>

The template is executed by passing both parameter values (default values are ignored).

http://IISServer/nwind/template/template5.xml?CustomerID=BERGS&EmployeeID=2

See Also

Using IIS Virtual Directory Management for SQL Server Utility

Accessing SQL Server Using HTTP

Retrieving XML Documents Using FOR XML