SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XSLT sum and xpath


XSLT sum and xpath

Author
Message
sfletcher-933270
sfletcher-933270
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 218
I am trying to sum the costs values in this sample xml using the xslt below. I expect to see a subtotal of 92, ie the sum of all costs in the userfieldgroupid = 9. Instead I get:

Result -
Item $
1.5 69
0.5 23
Subtotal 69 23

I suspect the issues is with the xpath and that my result is the individual costs summed with themselves. If I use <xsl:value-of select="sum(//costs)"/> I get:

Result -
Item $
1.5 69
0.5 23
Subtotal NaN

I am using an IE control in a database application to render a data view. I have searched and tried so many examples but can't work this out so I would be very grateful for any help you can give me.

Sample xml:
<?xml version="1.0"?>
<xml>
<object>
<objectid>183382</objectid>
<objectnumber>Test00001237</objectnumber>
<lccs>
<lcc>
<groupname>Costs: Acquisition planning</groupname>
<userfieldgroupid>9</userfieldgroupid>
<userfieldname>Concept development hours</userfieldname>
<userfieldid>42</userfieldid>
<fieldvalue>1.5</fieldvalue>
<depreciationlife>25</depreciationlife>
<costs>69</costs>
</lcc>
<lcc>
<groupname>Costs: Acquisition planning</groupname>
<userfieldgroupid>9</userfieldgroupid>
<userfieldname>Concept assessment hours</userfieldname>
<userfieldid>43</userfieldid>
<fieldvalue>0.5</fieldvalue>
<depreciationlife>25</depreciationlife>
<costs>23</costs>
</lcc>
</lccs>
</object>
</xml>



Sample xslt:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:js="urn:custom-javascriptscript" exclude-result-prefixes="msxsl js">
<xsl:output method="html" omit-xml-declaration="yes" doctype-public="-//W3C//DTD HTML 4.01 Transitional//EN" doctype-system="http://www.w3.org/TR/html4/loose.dtd" indent="yes"/>

<xsl:template match="/xml/object">
<html>
<head>
</head>
<body>
<table>
<tr style="font-weight:bold" >
<td>Item</td>
<td>$</td>
</tr>
<xsl:for-each select="lccs/lcc">
<xsl:choose>
<xsl:when test="normalize-space(userfieldid)='42'">
<tr>
<td> <xsl:value-of select="normalize-space(fieldvalue)"/></td>
<td> <xsl:value-of select="normalize-space(costs)"/></td>
</tr>
</xsl:when>
<xsl:when test="normalize-space(userfieldid)='43'">
<tr>
<td><xsl:value-of select="normalize-space(fieldvalue)"/></td>
<td><xsl:value-of select="normalize-space(costs)"/></td>
</tr>
</xsl:when>
</xsl:choose>
</xsl:for-each>
</table>
<table>
<tr>
<td>Subtotal</td>
<xsl:for-each select="lccs/lcc">
<xsl:choose>
<xsl:when test="userfieldgroupid='9'">
<xsl:if test ="costs !=''">
<td><xsl:value-of select="sum(costs)"/></td>
</xsl:if>
</xsl:when>
</xsl:choose>
</xsl:for-each>
</tr>
</table>

</body>
</html>
</xsl:template>
</xsl:stylesheet>

Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41268 Visits: 19500
One small change needed

<td><xsl:value-of select="sum(../../*/*/costs)"  /></td>



Cool
sfletcher-933270
sfletcher-933270
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 218
Hi thanks so much for replying and helping me however when I make the change you suggest the result I get is :

Result -

Item $
1.5 69
0.5 23
Subtotal NaN NaN

The values that were previously displayed as numbers now read NaN and still in the list rather than sum.

The underlying data is from a SQL data base and is in decimal format.

I tried using <xsl:value-of select='format-number(sum(../../*/*/costs), "###,###.00")' /> and this gives the same result as above, ie with NaN

If I just use <xsl:value-of select='format-number(costs, "###,###.00")' /> directly on costs I get the numbers, but not summed! so back to where I started.

Result-
Item $
1.5 69
0.5 23
Subtotal 69.00 23.00

Grateful for any further suggestions.
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41268 Visits: 19500
Ran your example through on
http://xslttest.appspot.com/

noticed the grouping is incorrect, if it is on lccs/lcc/, you will get multiple lines of subtotal.

NaN could be null or empty string.

Cool
sfletcher-933270
sfletcher-933270
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 218
Hi thank you so much for this, it was a huge help. Part of my problem was the null values which Ii thought I had prevented by using <xsl:if test ="costs !=''">. This is no doubt not the way to prevent a null value. In any case, I cheated a bit and changed the underlying SQL view to replace the null values with 0 and I am just very happy that it is now working :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search