Accessing Database Objects Using HTTP
The database objects, such as tables and views, can be accessed directly using a URL. In this case, the XPath query is specified directly against the database object to obtain the result (one row/one column value). For more information about the URL syntax that is supported by the SQL ISAPI extension, see URL Access.
In the URL, the virtual name of dbobject type is specified when accessing database objects directly.
The FOR XML queries can return references to binary data. You can retrieve the binary data associated with the reference by sending another URL request with the dbobject reference in it. This is the primary purpose for the dbobject virtual name type. For more information about queries that use FOR XML, see Retrieving XML Documents Using FOR XML.
Examples
In the following examples, nwind is a virtual directory created using the IIS Virtual Directory Management for SQL Server utility. The dbobject is the virtual name of dbobject type and template is a 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. Retrieve an employee's photo using the virtual name of dbobject type in the URL
The XPath query specified retrieves Photo column from Employees table. In the URL, Employees[@EmployeeID='1']/@Photo is the XPath query. In the query, Employees is the table name and @EmployeeID='1' is the predicate that finds an employee with an ID value of 1. @Photo is the column from which to retrieve the value.
http://IISServer/nwind/dbobject/Employees[@EmployeeID='1']/@Photo
The query is translated into the following SELECT statement:
SELECT Photo
FROM Employees
WHERE EmployeeID='1'
Note The XPath query must identify a single row and a single column.
B. Execute a query to obtain references to image data and to apply an XSL style sheet to process the references
In this example, a SELECT statement is specified to retrieve the employee ID and photo. The query returns references to the image data. These references are used in the Extensible Stylesheet Language (XSL) file to retrieve the employee photos and to display them in the browser.
The query is specified in a template. For illustration purposes, the template file is saved as TemplateWithAnXSL.xml file in the template subdirectory of the virtual root (assuming this is the directory specified when the virtual name of template type is created).
<?xml version ='1.0' encoding='UTF-8'?>
<root xmlns:sql='urn:schemas-microsoft-com:xml-sql' sql:xsl='photo.xsl'>
<sql:query >
SELECT employeeID, photo FROM employees FOR XML AUTO
</sql:query>
</root>
This is the XSL file (Photo.xsl) to process the result set. For illustration purposes, this file is stored in the virtual root directory.
<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet xmlns:xsl='http://www.w3.org/TR/WD-xsl' >
<xsl:template match = '*'>
<xsl:apply-templates />
</xsl:template>
<xsl:template match = 'employees'>
<TR>
<TD><xsl:value-of select = '@employeeID' /></TD>
<TD><B> <IMG><xsl:attribute name='src'>
<xsl:value-of select = '@photo'/>
</xsl:attribute>
</IMG>
</B></TD>
</TR>
</xsl:template>
<xsl:template match = '/'>
<HTML>
<HEAD>
<STYLE>th { background-color: #CCCCCC }</STYLE>
<!-- <BASE href='http://IISServer/nwind/'></BASE> -->
</HEAD>
<BODY>
<TABLE border='1' style='width:300;'>
<TR><TH colspan='2'>Employees</TH></TR>
<TR><TH >EmployeeID</TH><TH>Photo</TH></TR>
<xsl:apply-templates select = 'root' />
</TABLE>
</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>
This URL executes the template:
http://IISServer/nwind/template/TemplatewithAnXSL.xml?contenttype=text/html
After applying the XSL file, the query result is displayed as a two-column table (EmployeeID and Photo).
C. Specify special characters in the query
In a URL, the question mark (?) separates the URL and the parameters being passed to the URL. Any special characters, such as the plus sign (+) to the right of the ?, are escaped by the browser (that is, a + to the right of a ? is converted to %20).
The URL in this example produces an error because the + in the predicate expression is interpreted as an addition operator (because there is no ? in the URL). You must specify %20 in place of + in the URL.
http://IISServer/nwind/dbobject/Orders[@OrderID=10248%20and%20@EmployeeID=5]/@CustomerID
See Also
Using IIS Virtual Directory Management for SQL Server Utility