Executing SQL Queries Using Templates

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.

The <sql:query> tag is used to specify SQL statements.

Examples

A. Create a template file with a simple SELECT statement

This template specifies a simple SELECT statement.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:query>
    SELECT top 2 CustomerID, CompanyName 
    FROM   Customers 
    FOR XML AUTO
  </sql:query>
</ROOT>

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

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

The query specified in the template is replaced by its result. Therefore, the XML document returned has the same structure as the template itself, including the <ROOT> tag that is added.

This is the result:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" /> 
  <Customers CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados" /> 
</ROOT>

B. Execute a stored procedure in a template file

A stored procedure can also be executed in a template. The stored procedure is also specified in the <sql:query> tag.

Consider this stored procedure:

IF EXISTS (SELECT name FROM sysobjects
   WHERE name = 'CategoryInfo' AND type = 'P')
   DROP PROCEDURE CategoryInfo
GO
CREATE PROCEDURE CategoryInfo
AS
    SELECT   CategoryName
    FROM     Categories
    WHERE    Categories.CategoryID = 2
    FOR XML AUTO

The stored procedure can be executed in a template:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:query>
    exec CategoryInfo
  </sql:query>
</ROOT>

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

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

This is the result:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Categories CategoryName="Condiments" /> 
</ROOT>

C. Use entity references in a template

Because a template is an XML document, entity references must be used for special characters. This example uses the entity reference (&gt;) for the special markup character (>).

Consider this template:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:query>
      SELECT top 2 * 
      FROM     [Order Details] 
      WHERE     UnitPrice &gt; 10 
      FOR XML AUTO
  </sql:query>
</ROOT>

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

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

This is the result:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Order_x0020_Details OrderID="10248" ProductID="11" UnitPrice="14" 
                       Quantity="12" Discount="0" /> 
  <Order_x0020_Details OrderID="10248" ProductID="72" UnitPrice="34.8" 
                       Quantity="5" Discount="0" /> 
</ROOT>

If this template is specified directly in the URL, additional encoding is needed. Because the > character is a special character in XML and because it is specified in a template (an XML document), it is encoded as &gt;. And because the & character is a special character in this URL, & must be encoded as %26 when this template is specified in the URL.

The template is then specified in the URL as:

http://IISServer/nwind?template=<ROOT%20xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>SELECT%20top%202%20*%20FROM%20[Order%20Details]%20WHERE%20UnitPrice%20%26gt;%2010%20FOR%20XML%20AUTO</sql:query></ROOT>

D. Specify templates directly in the URL

Templates can be specified directly in the URL. In this example, a template containing a simple SELECT statement is specified in the URL:

http://IISServer/nwind?template=<ROOT+xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>SELECT+*+FROM+Customers+FOR+XML+AUTO</sql:query></ROOT>

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

See Also

Using IIS Virtual Directory Management for SQL Server Utility

Accessing SQL Server Using HTTP

Retrieving XML Documents Using FOR XML