XslGenerator: Event Calendar and Detail using FOR XML PATH

This is a XslGenerator case study

Overview

This case study builds on what was developed in XWeb:ExecuteMethod Case Study1 using SQL Server 2005's FOR XML PATH command. This feature can only be used in 2007.01 and newer builds.

In this case study, we use the XslGenerator to output the data from the stored procedures developed in the case study.

Web Page

First add a Web Page

Page Detail

Add a Page Detail using the XslGenerator.

The XslGenerator code pictured is below. Note that the SP named av_EventCalendar_ws uses FOR XML PATH syntax and therefore you must invoke it as shown, between <xml> tags. You must name the tags exactly <xml> as shown below. It really does not matter what you call your Xml elements inside the SP itself. The use of the actual word xml is really just to instruct netFORUM to use XML SQL execution instead of using a conventional recordset. 

{BeginXmlBuilderXml}
<xml>execute dbo.av_EventCalendar_ws</xml>
{EndXmlBuilderXml}
 
{BeginXsl}
<?xml   version="1.0"   encoding="UTF-8"   ?>
<xsl:stylesheet   version="1.0"   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:template   match="Events">
		<DIV>
			<xsl:apply-templates   select="Event"   />
		</DIV>
	</xsl:template>
 
	<xsl:template   match="Event">
		<DIV>
			<span   class="tinyTXT">
				<strong>
                                    <a   href="DynamicPage.aspx?WebCode=EventDetail&amp;Key={evt_key}">
                                        <xsl:value-of   select="evt_title"   /> 
                                    </a>
				</strong>
			</span>
 
 		</DIV>
	</xsl:template>
</xsl:stylesheet>
{EndXsl}
   

Drill-Down Page Detail

The page above has a drill down to a page detail that will contain a parent event with nested sessions.

Add a second Web Page and Page Detail as shown nearby.

The code in this page detail is below. Note that the page detail must contain a Form (any form) in order to parse values. Notice how we pass in the parameter. 

{BeginXmlBuilderXml}
<xml>execute dbo.av_EventDetail_ws @evt_key = {evtkey}</xml>
{EndXmlBuilderXml}
{BeginXmlBuilderParams}evtkey={Key}{EndXmlBuilderParams}
 
{BeginXsl}
<?xml   version="1.0"   encoding="UTF-8"   ?>
<xsl:stylesheet   version="1.0"   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:template   match="Events">
		<DIV>
			<xsl:apply-templates   select="Event"   />
		</DIV>
	</xsl:template>
 
	<xsl:template   match="Event">
		<DIV>
			<span   class="tinyTXT">
				<strong>
					<xsl:value-of   select="evt_title"   />
				</strong>
			</span>
 
			<span   class="tinyTXT"   style="PADDING-LEFT: 10px; WIDTH: 98%; PADDING-TOP: 4px">
				<i>Sessions</i>
			</span>
 
			<xsl:apply-templates   select="Sessions/Session"   />
 
			<p   />
 
		</DIV>
	</xsl:template>
 
	<xsl:template   match="Session">
		<DIV>
			<span   class="tinyTXT"   style="PADDING-LEFT: 20px; WIDTH: 98%; PADDING-TOP: 4px">
				<xsl:value-of   select="ses_title"   />
			</span>
		</DIV>
	</xsl:template>
</xsl:stylesheet>
{EndXsl}
   


Summary

If the netFORUM site is on 2007.01 then all uses of XslGenerator should employ SQL 2005's FOR XML PATH mode syntax. The benefits are:

  • Dramatically faster processing with parent/child results. Suppose you have a recordset that returns five records, and each of these five parent records may contain child records. Using standard SQL, the XslGenerator will need to run six SQL commands: one for the parent, and then one for each child. Using FOR XML PATH SQL command, the XslGenerator can return all of this data in a single SQL command. So not only are you reducing the SQL commands, but you are also eliminating five server round-trips to send the command from the web server to the SQL server, and send the data from the SQL server back to the web server.
  • FOR XML PATH SQL is built directly into SQL Server 2005 and contains a richer and more powerful feature set than what Avectra could develop in the DynamicXmlBuilder, particularly in how SQL Server 2005 can handle XML attributes and other advanced XML formatting options.
  • By letting SQL Server generate the XML structure, the XslGenerator no longer has to do this internally. If you use ordinary SQL commands, then the XslGenerator code internally has to convert this SQL recordset into XML which calls for more unnecessary internal processing.
  • Even if your XlsGenerator returns only a flat recordset, you are still better off using SQL XML instead of ordinary SQL, although any performance gains will be minor compared to the gains you would see if you have hierarchical sql.

Converting Old XslGenerator SQL to FOR XML PATH SQL

Main Article: Converting Old XslGenerator SQL to FOR XML PATH SQL (Cart View)

See main articles for two case studies on converting "old" XslGenerator SQL to the new FOR XML PATH mode.

See Also