Hey folks,

After enjoying the usage of the XML module with some dynamic data I decided to post my experience and the code behind the idea.

On a project I am working on I needed a way to display employment availability data (jobs and the like) and at the time we used coldfusion to do the displaying of the data and edititing of it. I'll leave the editing to coldfusion and just have the XML module do the displaying of the data. I know I could use dnn to do everything but at the moment I needed a sort of quick solution and the XML module provided it.

Before dropping an XML module on to a page I first needed to create a connection to the MSSQL database and then collect the data and then present it as XML data for the module to use. I'm sure there's a better solution to the problem as it stands with just the way I am using it.

First the collection of data, I am using PHP to handle this as the XML module seems to have some sort of issue with .cfm connectivity.

This file might be called myJobs2xml.php

Any require_once that try to use Connections/<filename>.php need to be a connect setup to what ever sort of database you are using.

Also take notice of pageNum_MyEmploymentListings, it is used to handle pagination if there are more pages to be displayed. The pageNum_MyEmploymentListings is passed to the XSL parser within the XML module.

<?php require_once('Connections/MyEmploymentListings.php'); ?>
<?php header("Content-Type: text/xml");
echo "<?xml version=\"1.0\" encoding=\"US-ASCII\"?>";?>
<?php
$maxRows_MyEmploymentListings = 55;
$pageNum_MyEmploymentListings = 0;
if (isset($_GET['pageNum_MyEmploymentListings'])) {
  $pageNum_MyEmploymentListings = $_GET['pageNum_MyEmploymentListings'];
}
if ($pageNum_MyEmploymentListings <0)
{
    $pageNum_MyEmploymentListings = 0;
}
$startRow_MyEmploymentListings = $pageNum_MyEmploymentListings * $maxRows_MyEmploymentListings;

mssql_select_db($database_MyEmploymentListings, $MyEmploymentListings);
$query_MyEmploymentListings = "SELECT * FROM JobListings";
$query_limit_MyEmploymentListings = sprintf("select top %d *, convert(varchar, AppDeadline, 20) as TumbleTime from JobListings LEFT JOIN Locations on Locations.LocationID = JobListings.LocationID where JobListings.JobID not in (select top %d JobID from JobListings ORDER BY AppDeadline DESC) ORDER BY AppDeadline DESC", $maxRows_MyEmploymentListings, $startRow_MyEmploymentListings);
//error_log($query_limit_MyEmploymentListings);
$MyEmploymentListings = mssql_query($query_limit_MyEmploymentListings, $MyEmploymentListings) or die(mssql_error());
$row_MyEmploymentListings = mssql_fetch_assoc($MyEmploymentListings);
if (isset($_GET['totalRows_MyEmploymentListings'])) {
  $totalRows_MyEmploymentListings = $_GET['totalRows_MyEmploymentListings'];
} else {
  $all_MyEmploymentListings = mssql_query($query_MyEmploymentListings);
  $totalRows_MyEmploymentListings = mssql_num_rows($all_MyEmploymentListings);
}
$totalPages_MyEmploymentListings = ceil($totalRows_MyEmploymentListings/$maxRows_MyEmploymentListings)-1;

if($pageNum_MyEmploymentListings > $totalPages_MyEmploymentListings)
{
    $pageNum_MyEmploymentListings = $totalPages_MyEmploymentListings;
}
?>
<listings>
<?php do { ?>
<listing>
<jobID><?php echo $row_MyEmploymentListings['jobID']; ?></jobID>
<jobTitle><?php echo $row_MyEmploymentListings['JobTitle']; ?></jobTitle>
<deadline><?php
    if(!is_null($row_MyEmploymentListings['AppDeadline']))
    {
        echo strftime('%B %d, %Y',strtotime($row_MyEmploymentListings['TumbleTime']));
    } else {
        echo $row_MyEmploymentListings['AppDeadline'];
    }?></deadline>
<location><?php echo $row_MyEmploymentListings['Location']; ?></location>
<untilFilled><?php echo $row_MyEmploymentListings['UntilFilled']; ?></untilFilled>
</listing>
<?php } while ($row_MyEmploymentListings = mssql_fetch_assoc($MyEmploymentListings)); ?>
<totalPages><?php echo $totalPages_MyEmploymentListings?></totalPages>
<currentPageNumber><?php echo $pageNum_MyEmploymentListings?></currentPageNumber>
</listings>
<?php
mssql_free_result($MyEmploymentListings);
?>

The associated XSL file

myjobs2xml.xsl

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xhtml="http://www.w3.org/1999/xhtml"
    version="1.0">
<xsl:output method="html" version="3.2" encoding="US-ASCII"/>
    <xsl:param name="pageNum_MyEmploymentListings" select="'1'"/>
<xsl:template match="listings">
    <table cellpadding="0" cellspacing="0" width="100%">
        <tr>
            <th style="text-align:left; font-size:12px; text-transform:uppercase">Job Title</th>
            <th style="text-align:left; font-size:12px; text-transform:uppercase">Location</th>
            <th style="text-align:left; font-size:12px; text-transform:uppercase">Deadline</th>
        </tr>
        <xsl:apply-templates select="listing">
        </xsl:apply-templates>
    </table>
    <xsl:apply-templates select="totalPages"/>
</xsl:template>
<xsl:template match="listing">
    <tr>
        <xsl:attribute name="style">
            font-size:11 px;
            font-weight; bold;
            <xsl:choose>
                <xsl:when test="position() mod 2 = 1">
                    background-color:#F0F0F0;
                    color:black;
                </xsl:when>
                <xsl:otherwise>
                </xsl:otherwise>
            </xsl:choose>
        </xsl:attribute>
        <td valign="top">
            <a>
                <xsl:attribute name="style">
                    font-weight: bold;
                    color: black;
                </xsl:attribute>
                <xsl:attribute name="href">/Visitors/HumanResources/JobListings/JobDescription/tabid/911/Default.aspx?jobid=<xsl:value-of select=".//jobID"/></xsl:attribute>
                <xsl:value-of select=".//jobTitle"/>
            </a>
        </td>
        <td valign="top">
            <xsl:attribute name="style">
                font-size:11px;
                font-weight:bold;
            </xsl:attribute>
            <xsl:value-of select=".//location"/>
        </td>
        <td valign="top">
            <xsl:attribute name="style">
                font-size:11px;
                font-weight:bold;
            </xsl:attribute>
            <xsl:value-of select=".//deadline"/>
            <xsl:choose>
                <xsl:when test="not(string(.//untilFilled)) or .//untilFilled = 0">
                </xsl:when>
                <xsl:when test=".//untilFilled = 1 and not(string(.//deadline))">
                    Open until filled.
                </xsl:when>
                <xsl:otherwise>
                    or until filled.
                </xsl:otherwise>
            </xsl:choose>
        </td>
    </tr>
</xsl:template>
<xsl:template match="totalPages">
    <xsl:variable name="actualPage">
        <xsl:choose>
            <xsl:when test="$pageNum_MyEmploymentListings &lt;= 0">
                <xsl:value-of select="'1'"/>
            </xsl:when>
            <xsl:when test="$pageNum_MyEmploymentListings &gt; /listings/totalPages">
                <xsl:value-of select="/listings/totalPages"/>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="$pageNum_MyEmploymentListings"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:variable>
    <xsl:variable name="previousPage">
        <xsl:choose>
            <xsl:when test="($actualPage - 1) &lt; 0">
                <xsl:value-of select = "'0'"/>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select = "$actualPage - 1"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:variable>
    <xsl:variable name="nextPage">
        <xsl:choose>
            <xsl:when test="($actualPage + 1) &gt; /listings/totalPages">
                <xsl:value-of select = "/listings/totalPages"/>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select = "$actualPage + 1"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:variable>
    <a><xsl:attribute name="href">?pageNum_MyEmploymentListings=<xsl:value-of select="$previousPage"/></xsl:attribute>Previous</a>
    <xsl:text> </xsl:text>
    <a><xsl:attribute name="href">?pageNum_MyEmploymentListings=<xsl:value-of select="$nextPage"/></xsl:attribute>Next</a>
</xsl:template>
</xsl:stylesheet>

The setup of the XML module would be as follows:

XML Data Source Settings
Link Type: URL
Location: http://<whereEverServer>/myjobs2xml.php
Querystring Parameters
  pageNum_MyEmploymentListings Pass-Through QueryString pageNum_MyEmploymentListings (This would allow the parameter to reload itself with each passing)

Encoding - ASCII

XSL Transformation Settings

Link Type: URL
   http://f<YourServerHere>/myjobs2xml.xsl

XSL Parameters

Don't know if this makes a difference but I set Ouput to inside module, Content Type to *.xml.

Now when the page is accessed it merrily goes off and gets the data, to include the ability to paginate through the data. Since MSSQL doesn't handle things like LIMIT the way MySQL does, the query for doing the record selection becomes quirky, perhaps one of these days MS will add a Limit like function. Of course if it's already there, then my bad.

I hope this can provide an example on the use of the XML module, just as Stefan has provided some within his blog, hope this helps someone in the future. :)

Happy XML moduling!