• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1837
  • Last Modified:

group and count xml nodes using XSL

Hi,

I have  a requirement to count the xml nodes after grouping.

 <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cIM00033" Version="4" TeamID="7457" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cIM00034" Version="4" TeamID="7457" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cIM00035" Version="4" TeamID="7457" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00001" Version="4" TeamID="7460" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00002" Version="4" TeamID="7460" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00003" Version="4" TeamID="7460" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00006" Version="4" TeamID="7460" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00007" Version="4" TeamID="7460" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00008" Version="4" TeamID="7460" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00009" Version="4" TeamID="7460" SurveyorID="710" />


In the above sample the teamid for some nodes are 7457 and some are 7460.

All I need to do is to count the number of nodes that have teamid 7457 and for 7460.

My expected result should be

7457 - 4
7460 - 7

Any ideas using XSL or VB.Net is much appreciated.

0
CCHSA
Asked:
CCHSA
2 Solutions
 
BobSiemensCommented:
<xsl:value-of select="count(//tblIndividualCriteriaRating[@TeamID='7457'])"/>

Syntax not checked
0
 
CCHSAAuthor Commented:
Hi Bob,

Thanks for the reply. I forgot to mention that the data is dynamic. Only the schema is permanant.

I found the solution. I used preceding-sibling to find the break between the teamids and then took store the teamid in variable. and count the nodes where the teamid matches the variable.

Thanks
0
 
Alexey FedorovSoftware developerCommented:
Hi!

I propose the next solution.
grp.xml:
<?xml version="1.0" encoding="UTF-8"?>
<root>
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cIM00033" Version="4" TeamID="7457" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cIM00034" Version="4" TeamID="7457" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cIM00035" Version="4" TeamID="7457" SurveyorID="710" />

  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00001" Version="4" TeamID="7460" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00002" Version="4" TeamID="7460" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00003" Version="4" TeamID="7460" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00006" Version="4" TeamID="7460" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00007" Version="4" TeamID="7460" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00008" Version="4" TeamID="7460" SurveyorID="710" />
  <tblIndividualCriteriaRating RatingValue="4" CriteriaCode="cAC00009" Version="4" TeamID="7460" SurveyorID="710" />
</root>

count.xsl:
<xsl:stylesheet version = "1.0"
                xmlns:xsl   = "http://www.w3.org/1999/XSL/Transform"
                exclude-result-prefixes = "xsl"
>
      <xsl:output method = "xml" indent = "no" encoding = "UTF-8" version = "4.0" />

      <xsl:key name = "kTeamId" match = "tblIndividualCriteriaRating" use = "@TeamID" />


      <xsl:template match = "/" >
            <xsl:apply-templates />
      </xsl:template>

      <xsl:template match = "root" >
      <report>
            <xsl:for-each select = "tblIndividualCriteriaRating[generate-id(.) = generate-id(key('kTeamId',@TeamID))]" >
                  <rating TeamID = "{@TeamID}" count="{count(key('kTeamId',@TeamID))}" />
            </xsl:for-each>
      </report>
      </xsl:template>

      <xsl:template match = "@*" >
            <xsl:copy />
      </xsl:template>

      <xsl:template match = "text()" >
            <xsl:value-of select = "normalize-space(.)" />
      </xsl:template>

      <xsl:template match = "processing-instruction() | comment()" >
            <xsl:copy-of select = "." />
      </xsl:template>

      <xsl:template match = "*" >
            <xsl:copy>
                  <xsl:apply-templates select = "node() | text() | processing-instruction() | comment() | @*" />
            </xsl:copy>
      </xsl:template>

</xsl:stylesheet>

result.xml:
<?xml version="1.0" encoding="UTF-8"?>
<report>
      <rating TeamID="7457" count="3" />
      <rating TeamID="7460" count="7" />
</report>

In order to get result, you can use msxsl.exe utility: msxsl grp.xml count.xsl >result.xml

You can download it here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnxml/html/msxsl.asp

http://download.microsoft.com/download/f/2/6/f263ac46-1fe9-4ae9-8fd3-21102100ebf5/msxsl.exe

Good luck!

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now