XSLT: How to convert XML into text file with Muenchian Grouping

1
votes

I hope to get help here one more time. Here is my sample input XML:

<Report>
  <RecordValues>
    <Record>
        <FieldValue fieldName="firm_name" fieldValue="Firm_1"/>
        <FieldValue fieldName="firm_number" fieldValue="11"/>
        <FieldValue fieldName="prepared_by" fieldValue="PARKER"/>
        <FieldValue fieldName="contact_number" fieldValue="123456789"/>
        <FieldValue fieldName="trade_date" fieldValue="2010-10-17"/>
        <FieldValue fieldName="symbol" fieldValue="ADM"/>
    </Record>
    <Record>
    <FieldValue fieldName="firm_name" fieldValue="Firm_1"/>
        <FieldValue fieldName="firm_number" fieldValue="11"/>
        <FieldValue fieldName="prepared_by" fieldValue="PARKER"/>
        <FieldValue fieldName="contact_number" fieldValue="123456789"/>
        <FieldValue fieldName="trade_date" fieldValue="2010-10-16"/>
        <FieldValue fieldName="symbol" fieldValue="ACW"/>
    </Record>
    <Record>
        <FieldValue fieldName="firm_name" fieldValue="Firm_2"/>
        <FieldValue fieldName="firm_number" fieldValue="12"/>
        <FieldValue fieldName="prepared_by" fieldValue="EDWARDS"/>
        <FieldValue fieldName="contact_number" fieldValue="123456780"/>
        <FieldValue fieldName="trade_date" fieldValue="2010-10-19"/>
        <FieldValue fieldName="symbol" fieldValue="ADS"/>
    </Record>
  </RecordValues>
</Report>

Here is the output I need to get:

A Firm_1 11
B PARKER 123456789
C 2010-10-17 ADM
C 2010-10-16 ACW
T 4
A Firm_2 12
B EDWARDS 123456780
C 2010-10-19 ADS
T 3

As you can see I need to group records by 'firm_name' or 'firm_number'. Each group must have one record of type 'A', one records of type 'B' and multiple records of type 'C'. Record 'T' is a total of each group without record 'T'. The input XML already sorted. I found Muenchian Method which is grouping records, but didn't succeed anything. Obviously I'm doing something wrong. Here is XSLT I wrote:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="text"/>
   <xsl:strip-space elements="*"/>

   <xsl:key name="value-by-firm" match="Report/RecordValues/Record/FieldValue" use="firm_number"/>
  <xsl:template match="Record">
   <xsl:for-each select="FieldValue/@fieldValue[count(. | key('value-by-firm', firm_number))]">
    <xsl:text>A </xsl:text>
    <xsl:value-of select="firm_name"/>
    <xsl:text> </xsl:text>
    <xsl:value-of select="firm_number"/>
    <xsl:text>&#xA;</xsl:text>
    <xsl:text>B </xsl:text>
    <xsl:value-of select="prepared_by"/>
    <xsl:text> </xsl:text>
    <xsl:value-of select="contact_number"/>
    <xsl:text>&#xA;</xsl:text>
    <xsl:for-each select="key('value-by-firm', firm_number)">
      <xsl:text>C </xsl:text>
      <xsl:value-of select="trade_date"/>
      <xsl:text> </xsl:text>
      <xsl:value-of select="symbol"/>
      <xsl:text>&#xA;</xsl:text>
   </xsl:for-each>
  </xsl:for-each>
 </xsl:template>
</xsl:stylesheet>

Maybe there is another way to do that. Thanks in advance.

2
xmlxsltmuenchian-grouping
This doesn't make any sense! What is a "record of type A", "record of type B", "record of typeC", "record of type T" ? What values are totalled? – Dimitre Novatchev
@Dimitre Novatchev: It's just hard coded record identifier. – klipa
The total should show number of records for one firm without total itself. – klipa
@klipa: In your XML I don't see any such hard-coded record identifier. Please, edit the question and make it meaningful. – Dimitre Novatchev
@klipa: Please, edit your question and explain everything that now is missing. Also, correct the expected output: the values for T must be 2 and 1. – Dimitre Novatchev

2 Answers

1
votes

This stylesheet:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text"/>
    <xsl:key name="kRecordByFirmAndContact" match="Record"
             use="concat(FieldValue[@fieldName='firm_number']
                            /@fieldValue,
                         '+',
                         FieldValue[@fieldName='contact_number']
                            /@fieldValue)"/>
    <xsl:template
         match="Record
                   [count(.|key('kRecordByFirmAndContact',
                                concat(FieldValue
                                          [@fieldName='firm_number']
                                          /@fieldValue,
                                       '+',
                                       FieldValue
                                          [@fieldName='contact_number']
                                          /@fieldValue))[1])
                    = 1 ]">
        <xsl:variable name="vRecords"
                      select="key('kRecordByFirmAndContact',
                                  concat(FieldValue
                                            [@fieldName='firm_number']
                                            /@fieldValue,
                                         '+',
                                         FieldValue
                                            [@fieldName='contact_number']
                                            /@fieldValue))"/>
        <xsl:value-of select="concat('A ',
                                     *[@fieldName='firm_name']
                                      /@fieldValue,
                                     ' ',
                                     *[@fieldName='firm_number']
                                      /@fieldValue,
                                     '&#xA;',
                                     'B ',
                                     *[@fieldName='prepared_by']
                                      /@fieldValue,
                                     ' ',
                                     *[@fieldName='contact_number']
                                      /@fieldValue,
                                     '&#xA;')"/>
        <xsl:apply-templates select="$vRecords" mode="RecordC"/>
        <xsl:value-of select="concat('T ',count($vRecords) + 2,'&#xA;')"/>
    </xsl:template>
    <xsl:template match="Record" mode="RecordC">
        <xsl:value-of select="concat('C ',
                                     *[@fieldName='trade_date']
                                      /@fieldValue,
                                     ' ',
                                     *[@fieldName='symbol']
                                      /@fieldValue,
                                     '&#xA;')"/>
    </xsl:template>
</xsl:stylesheet>

Output:

A Firm_1 11
B PARKER 123456789
C 2010-10-17 ADM
C 2010-10-16 ACW
T 4
A Firm_2 12
B EDWARDS 123456780
C 2010-10-19 ADS
T 3

Note: As you can see this is not complex, but your schema makes code so verbose... That looks like M$ XML format for data dumps.

1
votes

You're grouping the "wrong" thing, and using the groups incorrectly.

  • You're trying to group Record elements. Ergo, those should be matched by your xsl:key (the use=... attribute should then reference the firm name)
  • The grouping trick works by processing each group only once. You can't explicitly process each key in an xsl:key so instead you process all the values and just ignore all but the first value in the group - and do the entire group's processing there. That means your foreach should select the same elements as the xsl:key does and add node test ala [count(. | reference-to-group[1]) = 1] - and here, you forgot the [1] and = 1 part.

The fixed XSLT file then (note that the field lookup also changed slightly and I didn't add the T computation:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text"/>
  <xsl:strip-space elements="*"/>

  <xsl:key name="value-by-firm" match="/Report/RecordValues/Record" use="FieldValue[@fieldName='firm_number']/@fieldValue"/>
  <xsl:template match="/">
    <xsl:for-each select="/Report/RecordValues/Record[count(. | key('value-by-firm', FieldValue[@fieldName='firm_number']/@fieldValue)[1]) = 1]">
      <xsl:text>A </xsl:text>
      <xsl:value-of select="FieldValue[@fieldName='firm_name']/@fieldValue"/>
      <xsl:text> </xsl:text>
      <xsl:value-of select="FieldValue[@fieldName='firm_number']/@fieldValue"/>
      <xsl:text>&#xA;</xsl:text>
      <xsl:text>B </xsl:text>
      <xsl:value-of select="FieldValue[@fieldName='prepared_by']/@fieldValue"/>
      <xsl:text> </xsl:text>
      <xsl:value-of select="FieldValue[@fieldName='contact_number']/@fieldValue"/>
      <xsl:text>&#xA;</xsl:text>
      <xsl:for-each select="key('value-by-firm', FieldValue[@fieldName='firm_number']/@fieldValue)">
        <xsl:text>C </xsl:text>
        <xsl:value-of select="FieldValue[@fieldName='trade_date']/@fieldValue"/>
        <xsl:text> </xsl:text>
        <xsl:value-of select="FieldValue[@fieldName='symbol']/@fieldValue"/>
        <xsl:text>&#xA;</xsl:text>
      </xsl:for-each>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

Edit: A final note: What you're doing given this data schema is not one of XSLT's strong points. It looks like you have a data-structure that could naturally translate to a much cleaner schema (e.g. in which name-value pairs are represented by XML's natural name-value pairs; namely attributes). Alternatively, you may wish to import it into a "real" programming language (which is where this data almost certainly comes from), where all the cruft of such as the FieldValue element and the fieldName and fieldValue attributes aren't represented. Basically; although this is possible with XML+XSLT you'll end up with a more complex, brittle solution than you would if you were to represent it with a more natural representation and process it with a more natural tool.