XSLT grouping

Half-Saint · January 18th, 2009, 09:47 AM

I've checked a number of examples found on the net but I can't figure this out as not one seems to cover a case like mine. Here's the problem. I need to create a large Excel table using XML which is generated from Oracle DB. The trouble is, my XML looks like this:

Code:

<DATA>
  <HEADER>
    --irrelevant header data
  </HEADER>
  <body>
    <WORKER_ID>1100</WORKER_ID>
    <CONTRACT_ID>2000</CONTRACT_ID>
    <FULL_NAME>JOHN JOHNSON</FULL_NAME>
    <WORKPLACE>MATH</WORKPLACE>
    <SALARY>500</SALARY>
  </body>
  <body>
    <WORKER_ID>1100</WORKER_ID>
    <CONTRACT_ID>2005</CONTRACT_ID>
    <FULL_NAME>JOHN JOHNSON</FULL_NAME>
    <WORKPLACE>GYM</WORKPLACE>
    <SALARY>400</SALARY>    
   </body>
  <body>
    <WORKER_ID>1101</WORKER_ID>
    <CONTRACT_ID>2007</CONTRACT_ID>
    <FULL_NAME>NICK NICKEL</FULL_NAME>
    <WORKPLACE>JANITOR</WORKPLACE>
    <SALARY>250</SALARY>
  </body>
  <body>
    <WORKER_ID>1102</WORKER_ID>
    <CONTRACT_ID>2010</CONTRACT_ID>
    <FULL_NAME>ERIC ERICSSON</FULL_NAME>
    <WORKPLACE>ENGLISH</WORKPLACE>
    <SALARY>550</SALARY>
  </body>
  <body>
    <WORKER_ID>1102</WORKER_ID>
    <CONTRACT_ID>2015</CONTRACT_ID>
    <FULL_NAME>ERIC ERICSSON</FULL_NAME>
    <WORKPLACE>DUTCH</WORKPLACE>
    <SALARY>420</SALARY>
   </body>
  <body>
      <WORKER_ID>1102</WORKER_ID>
      <CONTRACT_ID>2015</CONTRACT_ID>
      <FULL_NAME>ERIC ERICSSON</FULL_NAME>
    <WORKPLACE>LIBRARIAN</WORKPLACE>
    <SALARY>250</SALARY>
   </body>
 </DATA>

As you can see, each worker can have many different contracts. Now, I need to group the workers first by WORKER_ID and then by CONTRACT_ID. Figured I could make the job easier by adding a new column concatenating WORKER_ID and CONTRACT_ID into one ID such as WORKER_CONTRACT_ID. Then I'd just have to group them by contracts since that's basically what we need:

JOHN JOHNSON (WORKER_ID:1100,CONTRACT_ID:2000)
MATH 500

JOHN JOHNSON (WORKER_ID:1100,CONTRACT_ID:2005)
GYM 400

NICK NICKEL (WORKER_ID:1101,CONTRACT_ID:2007)
JANITOR 250

ERIC ERICSSON (WORKER_ID:1102,CONTRACT_ID:2010)
ENGLISH 550
DUTCH 420

ERIC ERICSSON (WORKER_ID:1102,CONTRACT_ID:2015)
LIBRARIAN 250

I tried the Muenchian Method but I don't seem to be able to understand the examples without help. Most of the examples I found rely on having the data in the top node such as <body id=12345> which I don't have. I'd appreciate it, if someone could help me solve this. What I need is the code that will loop through the table and return formatted output like the above. I already have the main XSLT figured out and written but this is beyond me. Thanks.

Cheers
SainT

Martin Honnen · January 18th, 2009, 10:13 AM

Here is an XSLT 1.0 stylesheet:

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="1.0">
  
  <xsl:output method="text"/>
  
  <xsl:param name="lb" select="'
'"/>
  
  <xsl:key name="by-contract-id" match="body" use="concat(WORKER_ID, '_', CONTRACT_ID)"/>
  
  <xsl:template match="/">
    <xsl:for-each select="DATA/body[generate-id() = generate-id(key('by-contract-id', concat(WORKER_ID, '_', CONTRACT_ID))[1])]">
      <xsl:value-of select="concat(FULL_NAME, ' (WORKER_ID:', WORKER_ID, ', CONTRACT_ID:', CONTRACT_ID, ')', $lb)"/>
      <xsl:for-each select="key('by-contract-id', concat(WORKER_ID, '_', CONTRACT_ID))">
        <xsl:value-of select="concat(WORKPLACE, ' ', SALARY, $lb)"/>
      </xsl:for-each>
      <xsl:value-of select="$lb"/>
    </xsl:for-each>
  </xsl:template>

</xsl:stylesheet>

The result I get is however slightly different from what you posted:

Code:

JOHN JOHNSON (WORKER_ID:1100, CONTRACT_ID:2000)
MATH 500

JOHN JOHNSON (WORKER_ID:1100, CONTRACT_ID:2005)
GYM 400

NICK NICKEL (WORKER_ID:1101, CONTRACT_ID:2007)
JANITOR 250

ERIC ERICSSON (WORKER_ID:1102, CONTRACT_ID:2010)
ENGLISH 550

ERIC ERICSSON (WORKER_ID:1102, CONTRACT_ID:2015)
DUTCH 420
LIBRARIAN 250

So for ERIC ERICSSON there are different results however I think the stylesheet's result is correct while your grouping is not correct as for workplace "DUTCH" the contract is i 2015.

Half-Saint · January 19th, 2009, 06:58 AM

Thank you Martin. You're correct, I attached the wrong contract ID to Eric :)

Can you please explain how xsl:key and generate-id work in this case? I'd really like to understand this so I can do it on my own next time.

Regards,
Bojan

Martin Honnen · January 19th, 2009, 08:04 AM

Muenchian grouping is explained here: http://www.jenitennison.com/xslt/grouping/muenchian.xml

Half-Saint · January 19th, 2009, 08:09 AM

I found that page even before I started this thread. However, I found that needed a more noob friendly explanation especially regarding the generate-id method :)

Martin Honnen · January 19th, 2009, 09:17 AM

In the line

Code:

<xsl:for-each select="DATA/body[generate-id() = generate-id(key('by-contract-id', concat(WORKER_ID, '_', CONTRACT_ID))[1])]">

the code processes the first 'body' element of each group formed by the key named 'by-contract-id'.
The xsl:for-each select="DATA/body" would process each 'body' element, the predicate in square brackets however filters out only those 'body' elements for which generate-id() is equal to generate-id applied to the first element returned by the call to the key function. That is simply a way in XSLT 1.0 to ensure the first element in each group is processed.

What we really want is something alike e.g.
<xsl:for-each select="DATA/body[. is key('by-contract-id', concat(WORKER_ID, '_', CONTRACT_ID))[1]]">
only XSLT/XPath 1.0 does not have an 'is' operator. However using generate-id we can express exactly that condition.

Half-Saint · January 19th, 2009, 09:57 AM

I'm curious about this since I'm more used to coding in C++ and C#.. as the key is defined by the value of use="concat(WORKER_ID, '_', CONTRACT_ID) why do we have to repeat this definition each time we use that key? Is seems a bit redundant that's all. It would be logical only having to use the name of the key which is in this case "by-contract-id".

Bojan

mhkay · January 19th, 2009, 10:32 AM

This is 1.0 code, and grouping in 1.0 is messy. In XSLT 2.0 the code becomes much more concise and elegant, but you asked for a 1.0 solution.

Martin Honnen · January 19th, 2009, 10:33 AM

No, you need to supply the value of the key as well to the 'key' function. In the sample the value is computed by the same expression as in the use attribute of the xsl:key element but that is not necessarily the case, you could have other cases where you use a different expression to pass in the key value.

Half-Saint · January 23rd, 2009, 11:10 AM

Thank you, both of you. In the end I figured I needed the header for each worker and not just the document. By including contract_ID in each <header> element made things simple since I could then simply for-each through /DATA/HEADER and inside this through each /DATA/BODY with corresponding CONTRACT_ID.

The problem I'm facing now is that I need a cumulative sum for wage factors (sub-element <factor> inside each <body> element). Here's the example:

factor | csum
0.2 | 0.2
0.3 | 0.5 (csum = 0.2 + 0.3)
0.1 | 0.6 (csum = 0.2 + 0.3 + 0.1)
0.5 | 1.1 (csum = 0.2 + 0.3 + 0.1 + 0.5)

Any suggestions?