How to group XML elements by node value in XSLT?

2
votes

I'm trying to group records from a filemaker database. I'm exporting as XML with the option of using XSLT to transform.

I've been doing some search and reading the other posts, and I don't think they cover exactly what I want to do.

An excerpt of the XML:

<?xml version="1.0" encoding="UTF-8"?>
<!-- This grammar has been deprecated - use FMPXMLRESULT instead -->
<FMPDSORESULT xmlns="http://www.filemaker.com/fmpdsoresult">
   <ERRORCODE>0</ERRORCODE>
   <DATABASE>Artpostersnbbs.fp7</DATABASE>
   <LAYOUT />
   <ROW MODID="19" RECORDID="11116">
      <Art_Type>Poster</Art_Type>
      <Location>1</Location>
      <Line1>ELEVATOR
                   MACHINE
                   ROOM
                   107</Line1>
   </ROW>
   <ROW MODID="19" RECORDID="11116">
      <Art_Type>Poster</Art_Type>
      <Location>2</Location>
      <Line1>ELEVATOR
                   MACHINE
                   ROOM
                   107</Line1>
   </ROW>
   <ROW MODID="19" RECORDID="11116">
      <Art_Type>Poster</Art_Type>
      <Location>3</Location>
      <Line1>ELEVATOR
                   MACHINE
                   ROOM
                   107</Line1>
   </ROW>
</FMPDSORESULT>

I want the group each record that matches both ART_TYPE and LINE1. After being grouped it should add the Location from the match to the one being grouped into so it should look like:

<ROW MODID="19" RECORDID="11116">
    <Art_Type>Poster</Art_Type>
    <Location>1 2 3</Location>
    <Line1>ELEVATOR
           MACHINE
           ROOM
           107
    </Line1>
</ROW>

Any help on how to get started would be appreciated. Also is there any good xslt 1.0 testing program?

Thanks in advance!

EDIT: I was pointed to muenchian grouping and found this site: http://www.jenitennison.com/xslt/grouping/muenchian.html

So from reading that I came up with:

<xsl:stylesheet version="1.1" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:key name="artTypeNames" match="ROW" use="Art_Type" /> 
  <xsl:key name="artCopy" match="ROW" use="Line1" />
  <xsl:template match="FMPDSORESULT">
    <xsl:for-each select="ROW[count(. | key('artTypeNames', Art_Type)[1]) = 1]">
        <xsl:sort select="Art_Type" />
        <xsl:value-of select="Art_Type" />
        <xsl:for-each select="key('artTypeNames', Art_Type)">
            <xsl:sort select="Art_Type" />
            <xsl:value-of select="Art_Type" />
        </xsl:for-each>
    </xsl:for-each>

    <xsl:apply-templates/>
  </xsl:template>
</xsl:stylesheet>

I entered the XML and the XSLT into an online XML Transformer and I get 'XSLT is invalid' error.

This is frustrating.

EDIT2: With Tim's help I was able to construct a proper XSLT transform:

<xsl:stylesheet version="1.1" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fm="http://www.filemaker.com/fmpdsoresult">


<xsl:template match="fm:FMPDSORESULT">
    <xsl:apply-templates select="fm:ROW[count(. | key('lineData', fm:Line1)[1]) = 1]">
    </xsl:apply-templates>
</xsl:template>

<xsl:template match="fm:ROW">
    <xsl:copy>
       <xsl:apply-templates select="fm:Art_Type" />
       <fm:Location>
          <xsl:apply-templates select="key('artTypeNames', fm:Art_Type)/fm:Location" />
       </fm:Location>
       <xsl:apply-templates select="fm:Line1" />
    </xsl:copy>
</xsl:template>

<xsl:template match="fm:Location">
   <xsl:if test="position() > 1">-</xsl:if>
   <xsl:value-of select="." />
</xsl:template>

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

It groups the Art_Type and then by the Line1 text, but now adds the location numbers to all of them like this:

<ROW xmlns="http://www.filemaker.com/fmpdsoresult">
<Art_Type>Poster</Art_Type>
<fm:Location xmlns:fm="http://www.filemaker.com/fmpdsoresult" xmlns="">1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-19-20-21-22-23-24-25-26-27-28-29-30-31-32-33-34</fm:Location>
<Line1>CUSTODIAL
  LOUNGE

  117A
</Line1>
</ROW>
<ROW xmlns="http://www.filemaker.com/fmpdsoresult">
<Art_Type>Poster</Art_Type>
<fm:Location xmlns:fm="http://www.filemaker.com/fmpdsoresult" xmlns="">1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-19-20-21-22-23-24-25-26-27-28-29-30-31-32-33-34</fm:Location>
<Line1>STORAGE
  ROOM

  117B
</Line1>
</ROW>

If the Line1 text is different, it should be adding it into another group.

2
xmlxslt
If you are looking for a simple XSLT online tool you can try this, xslttest.appspot.com – Hash
Thanks. It will come in handy, once I figure out how to use xslt lol. – Hussien Hussien
You might also want to google "default namespace" in XSLT. – Daniel Haley
your missing the ending tag in the XML </FMPDSORESULT> – Hash
If the online XSLT tool you are using can't give you better diagnostics than that, then choose a different tool. If you're planning to do any amount of XSLT development, I would recommend installing an IDE such as oXygen or Stylus Studio. – Michael Kay

2 Answers

4
votes

If you're using XSLT 2.0, look for information on xsl:for-each-group. If you're using 1.0, look for information on 'Muenchian grouping'.

3
votes

One problem you have, that was mentioned in the comments, is to do with namespaces. In your XML, there is a namespace declaration:

<FMPDSORESULT xmlns="http://www.filemaker.com/fmpdsoresult">

This means that element, and all descendants elements below it, belong to that namespace (unless over-ridden lower down). But in your XSLT there is no mention of any namespace, and so the XSLT is looking for elements that belong to NO namespace.

You will need to declare the namespace in your XSLT, and then ensure you use the namespace prefix when trying to reference any element in the original XML.

<xsl:stylesheet version="1.1" 
     xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
     xmlns:fm="http://www.filemaker.com/fmpdsoresult">
  <xsl:key name="artTypeNames" match="fm:ROW" use="fm:Art_Type" /> 
  <xsl:template match="fm:FMPDSORESULT">

As for your XSLT sample, I don't get any error when I try it, although perhaps you are only showing a snippet. The grouping looks correct (assuming you are indeed intending to group ROW elements by Art_Type), but what you are missing is any code to copy the existing elements across.

<xsl:for-each select="fm:ROW[count(. | key('artTypeNames', fm:Art_Type)[1]) = 1]">
    <xsl:sort select="fm:Art_Type" />
    <xsl:copy>
       <xsl:copy-of select="@*" />
       <xsl:copy-of select="fm:Art_Type" />

So, this snippet copies the existing ROW element, its attributes, and then the Art_Type element (which will be the same for all elements in the group).

Try this (complete) XSLT

<xsl:stylesheet version="1.1" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fm="http://www.filemaker.com/fmpdsoresult">
  <xsl:key name="artTypeNames" match="fm:ROW" use="fm:Art_Type" /> 

  <xsl:template match="fm:FMPDSORESULT">
    <xsl:for-each select="fm:ROW[count(. | key('artTypeNames', fm:Art_Type)[1]) = 1]">
        <xsl:sort select="fm:Art_Type" />
        <xsl:copy>
           <xsl:copy-of select="@*" />
           <xsl:copy-of select="fm:Art_Type" />
           <fm:Location>
             <xsl:for-each select="key('artTypeNames', fm:Art_Type)">
                <xsl:if test="position() > 1">-</xsl:if>
                <xsl:value-of select="fm:Location" />
             </xsl:for-each>
           </fm:Location>
           <xsl:copy-of select="fm:Line1" />
        </xsl:copy>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

Note there was no need for the sort in the inner for-each loop because obviously Art_Type will be the same for all elements in the group.

EDIT: If you wanted to check two fields to determine what makes up a group, you need to use a concatenated key to achieve this. In your case, you say you want to check both Art_Type and fm:Line1, so your key could look something like this.

<xsl:key name="artTypeNames" match="fm:ROW" use="concat(fm:Art_Type, '||', fm:Line1)" />

Note the use of the '||' here. This can be anything, just as long as it does not appear in either of the elements you are checking.

To use this key, you just use it in a similar manner as before, but with the concatenated values of the elements. For example

<xsl:apply-templates select="fm:ROW[count(. | key('artTypeNames', concat(fm:Art_Type, '||', fm:Line1))[1]) = 1]">

Note, it is often better to use xsl:apply-templates instead of xsl:for-each, if only to reduce indentation.

Try this XSLT too

<xsl:stylesheet version="1.1" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fm="http://www.filemaker.com/fmpdsoresult">
   <xsl:output method="xml" indent="yes"/>
   <xsl:key name="artTypeNames" match="fm:ROW" use="concat(fm:Art_Type, '||', fm:Line1)"/>

   <xsl:template match="fm:FMPDSORESULT">
      <xsl:apply-templates select="fm:ROW[count(. | key('artTypeNames', concat(fm:Art_Type, '||', fm:Line1))[1]) = 1]">
         <xsl:sort select="fm:Art_Type"/>
      </xsl:apply-templates>
   </xsl:template>

   <xsl:template match="fm:ROW">
      <xsl:copy>
         <xsl:apply-templates select="@*"/>
         <xsl:apply-templates select="fm:Art_Type"/>
         <fm:Location>
            <xsl:apply-templates select="key('artTypeNames', concat(fm:Art_Type, '||', fm:Line1))/fm:Location"/>
         </fm:Location>
         <xsl:apply-templates select="fm:Line1"/>
      </xsl:copy>
   </xsl:template>

   <xsl:template match="fm:Location">
      <xsl:if test="position() &gt; 1">-</xsl:if>
      <xsl:value-of select="."/>
   </xsl:template>

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

Also note the use of the XSLT identity transform to copy existing elements instead of xsl:copy-of