Solved

Basic XSL Transformations and Averages

Posted on 2002-04-07
13
822 Views
Last Modified: 2007-12-19
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



0
Comment
Question by:bmccleary
  • 9
  • 4
13 Comments
 
LVL 23

Expert Comment

by:b1xml2
ID: 6924387
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.
0
 

Author Comment

by:bmccleary
ID: 6924390
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?
0
 
LVL 23

Accepted Solution

by:
b1xml2 earned 150 total points
ID: 6924452
XML Document
============
<?xml version="1.0" encoding="utf-8"?>
<ROOT>
<tbl_PipelineDailyPrices_Crosstab ValidDate="2002-03-02T00:00:00" Zone1-Sub1="2.49" Zone1-Sub2="2.49" Zone1-Sub3="2.32" />
<tbl_PipelineDailyPrices_Crosstab ValidDate ="2002-03-03T00:00:00" Zone1-Sub1="2.4" Zone1-Sub2="2.51" Zone1-Sub3="2.52" />
<tbl_PipelineDailyPrices_Crosstab ValidDate ="2002-03-04T00:00:00" Zone1-Sub1="2.43" Zone1-Sub2="2.65" Zone1-Sub3="2.22" />
</ROOT>


XSLT Document (Please note the different namespace used)
=============
<?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html" indent="yes" encoding="iso-8859-1"/>
<xsl:key name="summation" match="tbl_PipelineDailyPrices_Crosstab/@*" use="name()" />
<xsl:variable name="total" select="count(//tbl_PipelineDailyPrices_Crosstab)" />
<xsl:variable name="total-fields" select="count(//tbl_PipelineDailyPrices_Crosstab[1]/@*)" />
<xsl:template match="/">
<center>
<table border="1" width="100%">
<tr>
<th>ValidDate</th>
<xsl:for-each select="//tbl_PipelineDailyPrices_Crosstab[1]/@*[starts-with(name(),'Zone')]">
<xsl:variable name="field" select="name()" />
<th>
<xsl:choose>
<xsl:when test="contains($field,'-')">
<xsl:value-of select="substring-before($field,'-')" />
<br />
<xsl:value-of select="substring-after($field,'-')" />
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="name()" />
</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]/@*[starts-with(name(),'Zone')]">
     <th><xsl:value-of select="format-number(sum(key('summation',name())) div $total,'#,###,##0.0000')" /></th>
     </xsl:for-each>    
</tr>
</table>
</center>
</xsl:template>
<xsl:template match="tbl_PipelineDailyPrices_Crosstab">
<tr>
<td align="center" width="{100 div $total-fields}%"><xsl:value-of select="concat(substring(@ValidDate,6,2),'/',substring(@ValidDate,9,2),'/',substring(@ValidDate,1,4))" /> </td>
<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>
</tr>
</xsl:template>

</xsl:stylesheet>

HTML Output
===========
<center>
<table border="1" width="100%">
<tr>
<th>ValidDate</th>
<th>Zone1<br>Sub1</th>
<th>Zone1<br>Sub2</th>
<th>Zone1<br>Sub3</th>
</tr>
<tr>
<td align="center" width="25%">03/02/2002</td>
<td align="center" width="25%">2.4900</td>
<td align="center" width="25%">2.4900</td>
<td align="center" width="25%">2.3200</td>
</tr>
<tr>
<td align="center" width="25%">03/03/2002</td>
<td align="center" width="25%">2.4000</td>
<td align="center" width="25%">2.5100</td>
<td align="center" width="25%">2.5200</td>
</tr>
<tr>
<td align="center" width="25%">03/04/2002</td>
<td align="center" width="25%">2.4300</td>
<td align="center" width="25%">2.6500</td>
<td align="center" width="25%">2.2200</td>
</tr>
<tr>
<th>Average</th>
<th>2.4400</th>
<th>2.5500</th>
<th>2.3533</th>
</tr>
</table>
</center>
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 6924453
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.


0
 
LVL 23

Expert Comment

by:b1xml2
ID: 6924458
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.
0
 

Author Comment

by:bmccleary
ID: 6924543
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?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 23

Expert Comment

by:b1xml2
ID: 6924650
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.

0
 
LVL 23

Expert Comment

by:b1xml2
ID: 6924669
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
%>
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 6924750
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>
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 6924759
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.
0
 

Author Comment

by:bmccleary
ID: 6925918
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>
0
 

Author Comment

by:bmccleary
ID: 6925952
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!
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 6927016
<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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now