Groups two xml files like a sql group-by [2]

Question

This is an evolution of my ask here : Groups two xml files like a sql group-by The Example given and Dimitre Solution was counting distinct isbn value. Now modify library xml to have mylibrary.xml :

<library>  
   <book id="1" isbn="1"/>
   <book id="2" isbn="1"/>
   <book id="3" isbn="2"/>
   <book id="4" isbn="4"/>
   <book id="5" isbn="5"/>
   <book id="6" isbn="4"/>
   <book id="7" isbn="4"/>   
</library>

and this one that can be used : bookreference.xml :

<reference>  
    <book isbn="1">  
        <category>SF</category>  
    </book>  
    <book isbn="2">  
        <category>SF</category>  
    </book>  
    <book isbn="3">  
        <category>SF</category>  
    </book>  
    <book isbn="4">  
        <category>Comedy</category>  
    </book>  
    <book isbn="5">  
        <category>Comedy</category>  
    </book>
</reference>

i want to get the numbers of book i got in mylibrary 'even if some have same isbn', groupby category, using xslt 1-0.

output wanted:

SF : 3 book(s) 
Comedy : 4 book(s)

my xslt propose here : Groups two xml files like a sql group-by works fine but of course use 'for-each' loop and extension functions. Surely there is a better solution.

score 1 · Answer 1 · answered Dec 02 '11 at 14:00

modified Dimitri version to work for this

<xsl:output omit-xml-declaration="yes" indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:key name="kBookByCat" match="book" use="category"/>

<xsl:variable name="vRef" select="document('file:///c:/temp/delete/reference.xml')"/>

<xsl:variable name="meh" select="*"/>

<xsl:template match="/">
    <xsl:apply-templates select="$vRef/reference/book[generate-id()=generate-id(key('kBookByCat', category)[1])]" />
</xsl:template>

<xsl:template match="book">
    <xsl:variable name="cat" select="category"/>
    <xsl:value-of select="category"/> : <xsl:text/>
    <xsl:variable name="isbns" select="$vRef/reference/book[category=$cat]/@isbn"/>
    <xsl:value-of select="count($meh/book[@isbn=$isbns])"/>
    <xsl:text> book(s)&#xA;</xsl:text>
</xsl:template>

score 1 · Accepted Answer · answered Dec 02 '11 at 14:09

Again a very good question! (+1)

This transformation, using two keys for achieving full efficiency:

<xsl:stylesheet version="1.0"
     xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
     <xsl:output omit-xml-declaration="yes" indent="yes"/>
     <xsl:strip-space elements="*"/>

     <xsl:key name="kBookByCat" match="book"
          use="category"/>

     <xsl:key name="kBookByIsbn" match="book"
          use="@isbn"/>

     <xsl:variable name="vDoc" select="/"/>

     <xsl:variable name="vRef" select=
     "document('file:///c:/temp/delete/reference.xml')"/>

     <xsl:variable name="vMyIsbns" select="/*/*/@isbn"/>

     <xsl:variable name="vResult">
      <xsl:apply-templates select="$vRef/*"/>
     </xsl:variable>

     <xsl:template match="/">
      <xsl:copy-of select="$vResult"/>
     </xsl:template>

     <xsl:template match=
      "book[generate-id()
           =
            generate-id(key('kBookByCat', category)[1])
            ]
      ">
         <xsl:variable name="vBooksinCat" select=
              "key('kBookByCat', category)"/>

         <xsl:value-of select="category"/> : <xsl:text/>
         <xsl:for-each select="$vDoc">
           <xsl:value-of select="count(key('kBookByIsbn',$vBooksinCat/@isbn))"/>
         </xsl:for-each>
         <xsl:text> book(s)&#xA;</xsl:text>
     </xsl:template>
     <xsl:template match="text()"/>
</xsl:stylesheet>

when applied on the provided XML document contained in the file mylibrary.xml:

<library>
   <book id="1" isbn="1"/>
   <book id="2" isbn="1"/>
   <book id="3" isbn="2"/>
   <book id="4" isbn="4"/>
   <book id="5" isbn="5"/>
   <book id="6" isbn="4"/>
   <book id="7" isbn="4"/>
</library>

and having this provided XML document in C:\temp\delete\reference.xml:

<reference>
    <book isbn="1">
        <category>SF</category>
    </book>
    <book isbn="2">
        <category>SF</category>
    </book>
    <book isbn="3">
        <category>SF</category>
    </book>
    <book isbn="4">
        <category>Comedy</category>
    </book>
    <book isbn="5">
        <category>Comedy</category>
    </book>
</reference>

produces the wanted, correct output:

SF : 3 book(s)
Comedy : 4 book(s)

see your always 1 step ahead :P although for some reason when i was testing the solution the template match for the 2 unique category's did not seem to work (vs2008) untill I moved it, u will see what i mean if u look at difference between the answers! — Treemonkey, Dec 02 '11 at 14:23
And again a great answer, many thanks, i fell like a student spending hours on problems and my bad : never finding the solution, when you do in ... 5 minutes ? — Seb, Dec 02 '11 at 16:07
i have not yet enought reputation (<100) to answer my question yet, but in 3 hours i will be able to propose a code modification to not show category when books count = 0. — Seb, Dec 02 '11 at 16:23
@Seb: You are welcome. Don't worry about having to spend much time -- this is how you gradually get to spending less time :) — Dimitre Novatchev, Dec 02 '11 at 17:15
@Seb: Re: [code modification to not show category when books count = 0] ==> Just add another variable for the category and rearrange the code a little. — Dimitre Novatchev, Dec 02 '11 at 17:17
@Treemonkey: I am using the XSelerator. Also, I was ahead, because I had the experience from answering the previous, similar question. Your answer is also good, +1. — Dimitre Novatchev, Dec 02 '11 at 17:19

score 0 · Answer 3 · edited Dec 02 '11 at 19:45

in add to the great dimitri response, i propose the following to not print book category that have 0 book set in mylibrary :

<xsl:variable name="catname" select="category"/>
<xsl:for-each select="$vDoc">
    <xsl:variable name="cnt" select="count(key('kBookByIsbn',$vBooksinCat/@isbn))"/>
    <xsl:if test="$cnt &gt; 0">
        <xsl:value-of select="$catname"/> : 
        <xsl:text/>
        <xsl:value-of select="$cnt"/>
        <xsl:text> book(s)&#xA;</xsl:text>
    </xsl:if>
</xsl:for-each>

Groups two xml files like a sql group-by [2]

3 Answers3