Accessing Database Objects via HTTP | XML and SQL Server 2000

We've covered template files and schema files extensively up to this point but haven't mentioned dbobject at all. There is a reason for this. We know that dbobject allows direct access to database tables and views. That much we have mentioned. What we didn't know was that its use is almost exclusive to binary data database columns . This isn't nearly as prevalent as the type of usage we've gotten from templates and schemas. We have a couple of examples of this usage, first in a short discussion of URL usage and then in Listing 6.27.

We'll first retrieve an employee's photo from the Employees table via this URL:

 http://IISServer/Nwind/dbobjects/Employees[@EmployeeID='6']/@Photo

Employees specifies the Employees table, @EmployeeID is the predicate that identifies the requested information as belonging to employee 6, and @Photo identifies the column to access to obtain the data.

In this case, the XPath query must refer to a single row and single column. Listing 6.27 shows another way to get at the binary data utilizing a template file, and Listing 6.28 shows the result.

Listing 6.27 Retrieving Binary Data from a Template File

 <?xml version ='1.0' encoding='UTF-8'?>  <root xmlns:sql='urn:schemas-microsoft-com:xml-sql'>    <sql:query >       SELECT EmployeeID, Photo FROM Employees FOR XML AUTO    </sql:query>  </root>

Listing 6.28 Binary Data Results

 <?xml version="1.0" encoding="UTF-8" ?>  <root xmlns:sql="urn:schemas-microsoft-com:xml-sql">    <Employees EmployeeID="1"  Photo="dbobject/Employees[@EmployeeID='1']/@Photo" />    <Employees EmployeeID="2"  Photo="dbobject/Employees[@EmployeeID='2']/@Photo" />    <Employees EmployeeID="3"  Photo="dbobject/Employees[@EmployeeID='3']/@Photo" />    <Employees EmployeeID="4"  Photo="dbobject/Employees[@EmployeeID='4']/@Photo" />    <Employees EmployeeID="5"  Photo="dbobject/Employees[@EmployeeID='5']/@Photo" />    <Employees EmployeeID="6"  Photo="dbobject/Employees[@EmployeeID='6']/@Photo" />    <Employees EmployeeID="7"  Photo="dbobject/Employees[@EmployeeID='7']/@Photo" />    <Employees EmployeeID="8"  Photo="dbobject/Employees[@EmployeeID='8']/@Photo" />    <Employees EmployeeID="9"  Photo="dbobject/Employees[@EmployeeID='9']/@Photo" />  </root>

Hmmm, I don't think Listing 6.28 is quite the result we were looking for. What we need to do is send the results of the template file through an XSL stylesheet. This should give us what we were looking for. Add sql:xsl='photo.xsl' to the opening ROOT tag. The XSL stylesheet itself is given in Listing 6.29.

Listing 6.29 XSL Stylesheet for Employees' Photos

 <?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>

The <BASE> tag references the virtual server root that enables the page to properly present the photos. If you look at the generated source code of the HTML page given in Listing 6.30, you'll see how the photo references are generated.

Listing 6.30 Partial Listing of Generated HTML Code

 <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>        <TR>          <TD>1</TD>          <TD><B>            <IMG src="dbobject/Employees[@EmployeeID='1']/@Photo" />          </B></TD>        </TR>        <TR>          <TD>2</TD>          <TD><B>            <IMG src="dbobject/Employees[@EmployeeID='2']/@Photo" />          </B></TD>        </TR>        <TR>          <TD>3</TD>          <TD><B>            <IMG src="dbobject/Employees[@EmployeeID='3']/@Photo" />            </B></TD>        </TR>    </BODY>  </HTML>