Using XML, XSLT, and SQLXML to Create a Report | Database Access with Visual Basic .NET (3rd Edition)

It's time to put everything presented in this chapter together in a practical context. We do so by generating a list of customer addresses. As you will see in the code listings, changing the HTML content within the XSL file isn't difficult, and we could just as easily format this page to print in a special way or be used in conjunction with JavaScript/ASP.NET to provide robust functionality.

Basically, we need two things from SQL Server data and the data as XML format. For that, we use a template to store the query. The following code shows the contents of the noveltytemplate.xml file. The sole purpose of this file is to collect data and assign a style sheet.

 <?xml version = '1.0' encoding= 'UTF-8'?>    <root xmlns:sql='urn:schemas-microsoft-com:xml-sql'      sql:xsl='noveltyxsl.xsl'>      <sql:query>      SELECT FirstName, LastName, Address, City, State FROM tblCustomer FOR XML AUTO   </sql:query> </root>

The first line of code establishes a basic XML document. This line also shows a way of linking the style sheet to the XML document as with the xml:stylesheet element used in Listing 9.2. The next element, sql:query, is the container for the SQL command or query that we want to execute; note the use of FOR XML AUTO again. The FOR XML statement tells SQL Server to return the results as XML. In a template, we assume that the root element is called "root", so we don't need to specify that in the template query.

Note

If you want to assign a style sheet dynamically when using SQLXML, append "xsl=" to the URL as a query string and specify the file to use for example, http://<machinename>/<templates virtual directory>/<template filename.xml?>xsl=<xslfilename.xsl.>

Listing 9.7 shows the XSL style sheet applied to the resulting XML. We use XPATH to specify the attribute we want to collect. Using standard CSS syntax and HTML, we format the information to be viewed in a browser.

Listing 9.7 noveltyxsl.xsl complete

 <?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> <! Unless otherwise specified, the child elements will be the name of the table queried->   <xsl:template match = 'tblCustomer'>      <TR>         <! Notice the use of XPATH to collect the fields >         <TD><xsl:value-of select = '@FirstName' /></TD>         <TD><xsl:value-of select = '@LastName' /></TD>         <TD><xsl:value-of select = '@Address' /></TD>         <TD><xsl:value-of select = '@City' /></TD>         <TD><xsl:value-of select = '@State' /></TD>      </TR>   </xsl:template>   <xsl:template match = '/'>      <HTML>        <HEAD>          <STYLE>th { background-color: #00008C; color: #ffffff;} td {font-family: Arial}</STYLE>        </HEAD>        <BODY>        <TABLE border='1' style='width:600;'>          <TR><TH colspan='9'>Customers</TH></TR>          <TR>          <TH>First name</TH>          <TH>Last name</TH>          <TH>Address</TH>          <TH>City</TH>          <TH>State</TH>          </TR>          <xsl:apply-templates select = 'root' />        </TABLE>        </BODY>      </HTML> </xsl:template> </xsl:stylesheet>

Place the two files, noveltytemplate.xml and noveltyxsl.xsl, in the templates directory of the Novelty virtual Web created in the Installing and Configuring SQLXML 3.0 section earlier in this chapter. Once the files are in place, open Internet Explorer 6.0 or higher and navigate to the following URL, assuming of course that you are running everything locally.

http://localhost/novelty/templates/noveltytemplate.xml?contenttypeext/html

Note the contenttype parameter added to the URL, which specifies that the end result will be an HTML page. You should now have a page that looks like the one shown in Figure 9.14.

Figure 9.14. Results of XML template execution with XSL

graphics/09fig14.jpg