Customizing URL Queries

In the earlier article, we saw how to configure SQL Server to accept URL queries. Often, the output from URL queries, when viewed in Internet Explorer does not convey much interactivity. In order to format the output in a more meaningful manner, we need to play around a bit with SQLXML. More specifically, we need to use template queries and an associated stylesheet to ensure that the output is in the way that we want.
 
In this example, we will see how to get the list of titles from the pubs database and display the same in a table format. We will also make each title a URL that points to a fictitious location as identified by the title_id. Here are the steps that we need to follow for this exercise:
 
In the template folder that we created in the earlier article, create the following XML file (called GetTitles.xml).

<?xml version ='1.0' encoding='UTF-8'?>
<root xmlns:sql='urn:schemas-microsoft-com:xml-sql' sql:xsl='GetTitles.xsl'>
    <sql:query>
        SELECT title_id, title, Url='http://www.titles.com/' + title_id + '.asp' FROM titles FOR XML AUTO, ELEMENTS
    </sql:query>
</root>
 
In the above XML file, we get the title_id, title and a pseudo-column that contains the URL for each title. Also note that we have specified the XSL that needs to be used for formatting the content of this XML in the sql:xsl directive.
 
Now, in the same template folder, create the following XSL file (called GetTitles.xsl).

<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
	<xsl:template match="titles">
		<TR>
			<TD><xsl:value-of select='title_id'/></TD>
			<TD>
				<a>
					<xsl:attribute name="href"><xsl:value-of select="Url"/></xsl:attribute>
					<xsl:value-of select='title'/>
				</a>
			</TD>
		</TR>
	</xsl:template>
	<xsl:template match="/">
		<HTML>
			<BODY>
				<TABLE BORDER="1">
					<TR>
						<TH>Title ID</TH>
						<TH>Name</TH>
					</TR>
					<xsl:apply-templates select='root'/>
				</TABLE>
			</BODY>
		</HTML>
	</xsl:template>
</xsl:stylesheet>
 
In the above XSL, what we are doing is, formatting the output of the XML into a format that we want (which is a table). The XSL is not complex, but you need to be familiar with XSL syntax to generate cool HTML!! Once, we have both these files in place, you can use the following URL in Internet Explorer:

http://localhost/pubs/template/GetTitles.xml?contenttype=text/html
 
When you execute this URL, the output is the following:

Home