asked on

Basic XSL Transformations and Averages

I have the following XSL stylesheet that format an XML document for web viewing (as displayed by the text only results at the bottom). I am extreemly new at XSL programming. The displayed results are fine, but I would like to have the field headers (<th>'s) modified so that any hyphen is changed into a line break ("zone1-sub1" becomes "zone1<br>sub1"), and so that the date and number values that are displayed are a bit prettier without the time (date = mm/dd/yyyy format and values = 0.0000 format). How would I need to modify this XSL page to have that happen? Also, I would like to create one line at the bottom of the code that averages out the values for the zones fields... can this be done?

------ XSL PAGE -------

<?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 = 'tbl_PipelineDailyPrices_Crosstab'>
<TR>
<xsl:for-each select="@*">
<TD align="center" width="20%"><xsl:value-of select = "." /></TD>
</xsl:for-each>
</TR>
</xsl:template>
<xsl:template match = '/'>
<CENTER>
<TABLE border='1' width="100%">
<TR>
<xsl:for-each select="//tbl_PipelineDailyPrices_Crosstab[1]/@*">
<TH><xsl:eval>this.nodeName</xsl:eval></TH>
</xsl:for-each>
</TR>
<xsl:apply-templates select = 'ROOT' />
</TABLE>
</CENTER>
</xsl:template>
</xsl:stylesheet>

------ RESULTS (text only) -------

ValidDate Zone1-Sub1 Zone1-Sub2 Zone1-Sub3
2002-03-02T00:00:00 2.49 2.49 2.32
2002-03-03T00:00:00 2.4 2.51 2.52
2002-03-04T00:00:00 2.43 2.65 2.22

b1xml2

Firstly, the proprietary XSL is only supported by Microsoft, and is no longer widely used by the XML Community.

Secondly, we use XSLT which is a W3C Recommendation. In other words, it is a standard.

Final Comment, installs MSXML3 SP2 and you have it in replace mode, thus you can have XML Transforms using the standard inside MSIE.

bmccleary

ASKER

b1xml2,
What is the difference between XSL and XSLT? I simply copied some code that was shown in the SQL Server 2000 books on-line. To be honest with you, this is being developed in a propriary envrionment and the reliance on MS is not a big concern. I do have the MSXML3 parser installed, but how do I tell if it is SP2?. All I want to do is find the proper code to do some sort of replacement and formatting on the data that is returned. I believe that the correct function is "transform", but I'm not quite sure how to use it. Can you help?

ASKER CERTIFIED SOLUTION

b1xml2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

b1xml2

Notes
=====
1. XSL cannot do a lot of things that XSLT can.
2. XSLT has advanced functions and functionality that can be mimicked by XSL by writing out extensive codes in the <xsl:script /> element which is no longer exists in XSLT.

b1xml2

URL to download MSXML3 SP2
==========================
http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/MSDN-FILES/027/001/772/msdncompositedoc.xml&frame=true

Notes
=====
If you have MSIE6, you already have this.

bmccleary

ASKER

b1xml2,
Thank you very much. I appreciate your time on this, but when I tried to use the example that you gave me I got the following error:

HResult: 0x80004005
Source: msxml2.dll
Description: The value of the 'method' attribute may not be 'html'.

So I checked my system settings. I am running W2K Advanced Server with IE 6.0 installed, but I installed the XML parser anyway and it still didn't work. So I installed the MSXML4 SP1 and, again, it still didn't work. I don't know why the error would keep saying that the source was "msxml2.dll". Regardless, I deleted the "method" line and then I got this error:

HResult: 0x80004005
Source: msxml2.dll
Description: Keyword xsl:key may not be used here.

When I deleted that line, I got this error:

HResult: 0x80004005
Source: msxml2.dll
Description: NodeTest expected here. format-number(-->sum(<--key('summation',name())) div $total,'#,###,##0.0000')

Any thoughts?

b1xml2

just how are you doing the transform?

This is a scripted code transformation. MSXML4+ can only be used via scripted code.

<html>
<head>
<title>Scripted Transforms - Client Side</title>
<script language="javascript">
var oXML = new ActiveXObject("Msxml2.DOMDocument.4.0");
var oXSLT = new ActiveXObject("Msxml2.DOMDocument.4.0");
oXML.async = oXSLT.async = false;
oXML.load("xml/data.xml");
oXSLT.load("xslt/data.xsl");
window.onload = init;
function init() {
document.body.innerHTML += oXML.transformNode(oXSLT);
}

</script>
</head>
<body>
</body>
</html>

If you are using the xml-stylesheet processing instruction, then you should install MSXML3 SP2. This will install in replace mode. If unsure, run xmlinst.exe with no parameters to make MSXML3 the default XML Parser For MSIE.

If you are using MS-SQL Server 2000's XML Features, upgrade to MDAC 2.7 as the default parser used by MS SQL Server 2000 is MSXML2.6 which is msxml2.dll.

From the SDK.
Although version-independent GUIDs and ProgIDs appear simpler for coding, they are not supported in MSXML 4.0 for the following reasons.

The introduction of Windows XP Side-by-Side installation. This technology allows you to simulate the effects of version-independent ProgIDs and replace mode in a cleaner and more manageable way.
Customer experience reveals that in previous implementations of MSXML, version independence and replace mode are complicated to manage and can introduce instability in the production environment.
For example, the following types of application errors and support issues can arise. These problems easily outweigh the benefits of version independence and replace mode that were provided by previous versions of MSXML.

For Active Server Pages (ASP) programmers, using version-independent ProgIDs can result in unintended upgrades of legacy ASP-based sites to the latest version of MSXML. You can avoid this problem by modifying your Web pages to reference version-dependent programming IDs. For more information, see Workarounds to Version Independence.
In some cases, updating MSXML to use replace mode is intended. More often than not, however, it is too complex to predict the overall impact of a replace mode upgrade on a system. Although some applications might benefit from such an upgrade, other applications might break as a result of it. In many cases, you will not be able to know or fully predict which applications on your system depend on MSXML and will be affected by a replace mode upgrade.
If a customer relies on the built-in XSLT support of Microsoft® Internet Explorer for wide client-side deployment of a Web-based application, replace mode might hide the fact that some client desktops were not upgraded to the appropriate version of MSXML. Therefore, users running previous versions of MSXML replace mode might experience incomplete or incorrect functionality. This means that end users might experience instability in the application, instead of receiving a notification message to "Upgrade to current version MSXML."
Note Replace mode upgrades are no longer supported in MSXML 4.0. The Xmlinst.exe tool (a separate download) is for use with MSXML 3.0. It does not work with the current version of MSXML 4.0.

b1xml2

ASP Code (simple code with no error handling)
=========
<%
Dim oXML, oXSLT
Set oXML = Server.CreateObject("Msxml2.DOMDocument.4.0")
Set oXSLT = Server.CreateObject("Msxml2.DOMDocument.4.0")
oXML.async = False : oXSLT.async = False
oXML.load Server.MapPath("xml/data.xml")
oXSLT.load Server.MapPath("xslt/data.xsl")
Response.Write oXML.transformNode(oXSLT)
Set oXML = Nothing
Set oXSLT = Nothing
%>

b1xml2

This is the corresponding XSL Document (much tho I hesitate to write it) to show how limiting XSL is compared to XSLT.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
<xsl:script language="JScript">
<![CDATA[
function checkZone(oNode) {
var szName = oNode.nodeName;
return szName.indexOf("-");
}
function subZone(oNode) {
var szName = oNode.nodeName;
return szName.substring(szName.indexOf("-") + 1);
}
function formatMyDate(oNode) {
var szDate = oNode.nodeValue;
return szDate.substring(5,7) + "/" + szDate.substring(8,10) + "/" + szDate.substring(0,4);
}

function calcAverage(oNode) {
var intCount = 0;
var intTotal = 0;
var oDOM = oNode.ownerDocument;
var oNodeList = oDOM.selectNodes("//tbl_PipelineDailyPrices_Crosstab/@" + oNode.nodeName);
var intCount = oNodeList.length;
if (intCount == 0) return 0;
for (var i=0;i<intCount;i++) {
intTotal += Number(oNodeList.item(i).nodeValue);
}
return intTotal/intCount;

}

]]>
</xsl:script>
<xsl:template match="/">
<center>
<table border="1" width="100%">
<tr>
<xsl:for-each select="//tbl_PipelineDailyPrices_Crosstab[1]/@*">
<th>
<xsl:choose>
<xsl:when expr="checkZone(this) == -1"><xsl:node-name /></xsl:when>
<xsl:otherwise>Zone<br /><xsl:eval>subZone(this);</xsl:eval></xsl:otherwise>
</xsl:choose>
</th>
</xsl:for-each>
</tr>
<xsl:apply-templates select="//tbl_PipelineDailyPrices_Crosstab" />
<tr>
<th>Average</th>
<xsl:for-each select="//tbl_PipelineDailyPrices_Crosstab[1]/@*">
<xsl:if expr="this.nodeName != 'ValidDate'">
<th><xsl:eval>this.formatNumber(calcAverage(this),'#,###,##0.0000');</xsl:eval></th>
</xsl:if>
</xsl:for-each>
</tr>
</table>
</center>
</xsl:template>

<xsl:template match="tbl_PipelineDailyPrices_Crosstab">
<tr><xsl:apply-templates select="@*" /></tr>
</xsl:template>

<xsl:template match="tbl_PipelineDailyPrices_Crosstab/@*">
<td align="center"><xsl:eval>this.formatNumber(this.nodeValue,"#,###,##0.0000");</xsl:eval></td>
</xsl:template>

<xsl:template match="tbl_PipelineDailyPrices_Crosstab/@ValidDate">
<td align="center"><xsl:eval>formatMyDate(this);</xsl:eval></td>
</xsl:template>
</xsl:stylesheet>

b1xml2

As you compare between the XSL and XSLT syntax, you will notice two immediate things:

1. The XSL syntax initialises the jscript engine which slows down performance.

2. The XSL syntax is tightly bound to the XML Tree whereas the XSLT syntax is much looser.

Please note that there is and will be little support for XSL. It is not a standard and not many developers know advanced techniques using XSL (not XSLT!!!) and which makes transforms extremely slow and sluggish.

bmccleary

ASKER

b1xml2,
Absolutly fantastic!!! You went above and beyond the call of duty and I really appreciate it. I looks like you really know what you are doing. With that, we are going to have a couple of opportunities arise with this project and some others that we could certainly benfit from your expertise. If you would be interested in a little more formal arangement that would involve monitary compensation for your time, I would like to discuss some options with you. This would be nothing big, perhaps a few hours here and there, but I would like to have you help me out if you are up to it. If you are interested, email me at bmccleary@blueribbontech.com.

Now with the example, it worked perfectly. I am getting the data directly out of SQL Server without using ASP and the upgrade to MDAC 2.7 fixed the problem. One last thing that I need to wrap this section of the project up though:

I gave the example of fields named "Zone1-Sub1", but my zones (fields) are actually all named differently. Therefore, I want to select all the fields that are not part of the ValidDate column. Using the portion of the code that you gave below, instead of selecting [starts-with(name(),'Zone')], I want something like [does not starts-with(name(),'ValidDate')]. What would I use for this?

<xsl:for-each select="@*[starts-with(name(),'Zone')]">
<td align="center" width="{100 div $total-fields}%"><xsl:value-of select="format-number(.,'#,###0.0000')"/></td>
</xsl:for-each>

bmccleary

ASKER

O.K., I lied... I do have one more question. I want to replace a couple of character codes that are returned in the field names. Examples are spaces and parenthesis (I know that these are non-standard field names).
An example would be a field (zone) name called "LA (Z2&4)". The XML that SQL Server generates renames the field to "LA_x0020__x0028_Z2_x0026_4_x0029_". I tried to use the following syntax:
<xsl:variable name="field" select="translate(name(),'_x0020_',' ')" />
And it worked O.K. with replacing the spaces, but it converted the rest of the field name into "LA 8 Z 6 4 9 " Any thoughts? Thanks again!

b1xml2

<xsl:for-each select="@*[not(starts-with(name(),'ValidDate'))]">
<td align="center" width="{100 div $total-fields}%"><xsl:value-of select="format-number(.,'#,###0.0000')"/></td>
</xsl:for-each>

the translate function is not the same as the replace function, the translate function takes each character in the second argument and tries to find the replacing character in the same position in the third argument.

To replace words, you would have to use recursion, which is not available to XSL, only XSLT so thank heavens you upgraded to MDAC 2.7.