Executing SQL Statements Using HTTP

XML and Internet Support

previous page next page
XML and Internet Support

Executing SQL Statements Using HTTP

Microsoft® SQL Server™ 2000 can be accessed directly by queries executed at the URL (if the allow URL queries option was selected when the virtual root was registered). Clients can make requests using HTTP methods GET and POST. For more information about the URL syntax that is support by the SQL ISAPI extension, see URL Access.

Examples

In the following examples, nwind is a virtual directory used to access the Northwind database. For more information about creating the nwind virtual directory, see Creating the nwind Virtual Directory.

In the example queries, if a query returns more than one element at the root of the document, the root element can be added by including either of these:

  • A SELECT <ROOT> in the query.

  • Passing a root keyword as a parameter to the query with a value ROOT (this value can be anything).
A. Specify a simple query

The following statement returns all of the customer data in the Customers table in the Northwind database. In this query, the XML mode is set to RAW.

http://IISServer/Nwind?sql=SELECT+top+2+CustomerID,+ContactName+FROM+Customers+FOR+XML+RAW&root=ROOT

Here is the result set:

<ROOT>
   <Customers CustomerID="ALFKI" ContactName="Maria Andears" /> 
   <Customers CustomerID="ANATR" ContactName="Ana Trujillo" /> 
</ROOT>
B. Specify a query on multiple tables

In this example, the SELECT statement returns information from the Customers and Orders tables in the Northwind database. The XML mode is set to AUTO.

http://IISServer/nwind?sql=SELECT+top+2+Customers.CustomerID,OrderID,OrderDate+FROM+Customers,+Orders+WHERE+Customers.CustomerID=Orders.CustomerID+Order+by+Customers.CustomerID,OrderID+FOR+XML+AUTO&root=ROOT

This is the partial result:

<ROOT>
   <Customers CustomerID="ALFKI">
     <Orders OrderID="10643" OrderDate="1997-08-25T00:00:00" /> 
     <Orders OrderID="10692" OrderDate="1997-10-03T00:00:00" /> 
   </Customers>
   <Customers CustomerID="ANATR">
     <Orders OrderID="10308" OrderDate="1996-09-18T00:00:00" /> 
   </Customers>
</ROOT>
C. Specify special characters in the query

The following query returns all distinct contact titles starting with Sa from the Customers table in the Northwind database. The example uses the LIKE clause and the special character % to search for the contact titles. In the LIKE clause, the special character % is specified as %25.

http://IISServer/nwind?sql=SELECT+DISTINCT+ContactTitle+FROM+Customers+WHERE+ContactTitle+LIKE+'Sa%25'+ORDER+BY+ContactTitle+FOR+XML+AUTO&root=root

Here is the result set:

<ROOT>
   <Customers ContactTitle="Sales Agent" /> 
   <Customers ContactTitle="Sales Associate" /> 
   <Customers ContactTitle="Sales Manager" /> 
   <Customers ContactTitle="Sales Representative" /> 
</ROOT>

In the following example, order and order detail information is retrieved from the Orders and Order Details tables. 

http://IISServer/nwind?sql=SELECT+'<ROOT>'+SELECT+Orders.OrderID,+[Order+Details].OrderID,[Order+Details].ProductID,[Order+Details].UnitPrice+FROM+Orders,+[Order+Details]+WHERE+Orders.OrderID=[Order+Details].OrderID+ORDER+BY+Orders.OrderID+FOR+XML+AUTO;SELECT+'</ROOT>'

This is the partial result:

<ROOT>
<Orders OrderID="10248">
  <Order_x0020_Details OrderID="10248" ProductID="11" UnitPrice="14.00" /> 
  <Order_x0020_Details OrderID="10248" ProductID="42" UnitPrice="9.80" /> 
</Orders>
<Orders OrderID="10249">
  <Order_x0020_Details OrderID="10249" ProductID="14" UnitPrice="18.60" /> 
</Orders>
</ROOT>
D. Specify a query without the FOR XML clause

You can specify SQL queries without the FOR XML clause. The result is returned as a stream. In the query, you can specify only one column because streaming is not supported over multiple column results. In this example, the query returns the first name of employees from the Employees table in the Northwind database. The result is returned as a concatenated string of first names.

http://IISServer/nwind?sql=SELECT+FirstName+FROM+Employees
E. Specify the contenttype keyword

The contenttype keyword specifies the content-type of the document returned. text/XML is the default content-type of the document except when xsl is specified in the URL. When xsl is specified in the URL and contenttype is not specified, then contenttype defaults to text/html. 

In this example, the query returns a picture of an employee from the Employees table in the Northwind database. FOR XML mode is not specified because the returned data is compatible with the receiving application (that is, the browser can handle the returned data).

http://IISServer/nwind?sql=SELECT+Photo+FROM+Employees+WHERE+EmployeeID=1

In retrieving images, contenttype is generally specified. If contenttype is specified, the ISAPI extension does not search for and remove any Access header information. Therefore, to retrieve any images that have the Access header information, contenttype should not be specified as shown in the previous example. In all other cases, contenttype should be specified as shown in this example:

http://iisserver/virtualroot?sql=SELECT+Picture+FROM+TableName+WHERE+SomeID=1&contenttype=image/jpeg

The images can also be brought into an HTML document. In the following example, an .htm file (File1.htm) is created with these contents:

<img src="http://IISServer/nwind?sql=select photo from Employees where EmployeeID=1">

When this file is opened in the browser, an employee photo is displayed.

F. Specify the xsl keyword

In this example, the query returns the first and last name of all employees in the Employee table in the Northwind database. employee.xsl processes the result set.

When xsl is specified in the URL but contenttype is not specified in the URL and there is no content-type defined in the XSL style sheet, contenttype defaults to text/html. Therefore, the result is displayed in the form of a table with two columns (firstname, lastname).

http://IISServer/nwind?sql=SELECT+FirstName,LastName+FROM+Employees+FOR+XML+AUTO&xsl=employee.xsl&root=root

The .xsl file is provided here. This file must exist in the virtual root directory or one of its subdirectories (in which case the file path specified is relative to the virtual root directory). In this example, the .xsl file is stored in the virtual root directory.

<?xml version='1.0' encoding='UTF-8'?>          
 <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> 
    <xsl:template match = '*'>                                
        <xsl:apply-templates />                               
    </xsl:template>                                           
    <xsl:template match = 'Employees'>                        
       <TR>                                                   
         <TD><xsl:value-of select = '@FirstName' /></TD>      
         <TD><B><xsl:value-of select = '@LastName' /></B></TD>
       </TR>                                                  
    </xsl:template>
    <xsl:template match = '/'>                               
      <HTML>                                                  
        <HEAD>                                                
           <STYLE>th { background-color: #CCCCCC }</STYLE>    
        </HEAD>                                               
        <BODY>                                                
         <TABLE border='1' style='width:300;'>                
           <TR><TH colspan='2'>Employees</TH></TR>            
           <TR><TH >First name</TH><TH>Last name</TH></TR>    
           <xsl:apply-templates select = 'root' />            
         </TABLE>                                             
        </BODY>                                               
      </HTML>                                                 
    </xsl:template>                                           
</xsl:stylesheet>

Instead of specifying the contenttype in the URL, contenttype can also be specified as the value of the media-type attribute of the <xsl:output> element. For example, <xsl:output media-type="text/html" /> can be added after the namespace declaration in the preceding XSL file.

G. Pass parameters to SQL statements

Parameters can be passed to SQL queries. In this example, employee information for a given employee ID is returned from the Employees table in the Northwind database. The value of EmployeeID is provided as input to the query. Note that the ? character, used for a parameter marker in the URL, is a special character and is encoded as %3F. For more information about special characters, see Special Characters.

http://IISServer/nwind?sql=SELECT+FirstName,LastName+FROM+Employees+WHERE+EmployeeID=%3F+FOR+XML+AUTO&EmployeeID=1&root=ROOT

Here is the result set:

<ROOT>
   <Employees FirstName="Nancy" LastName="Davolio" /> 
</ROOT>

In this query, two parameter values are passed to the query:

http://IISServer/nwind?sql=SELECT+'<ROOT>';SELECT+EmployeeID,Title+FROM+Employees+WHERE+LastName=%3F+and+FirstName=%3F+FOR+XML+AUTO;SELECT+'</ROOT>'&LastName=Davolio&FirstName=Nancy

Here is the result set:

<ROOT>
   <Employees EmployeeID="1" Title="Sales Representative" /> 
</ROOT>

See Also

Accessing SQL Server Using HTTP

Retrieving XML Documents Using FOR XML

Using IIS Virtual Directory Management for SQL Server Utility

previous page start next page