XSLT Consolidating data when ID is the same

Question

I am having some issues with consuming an XML and applying multiple conditions on it. I have an input XML that looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<results xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ResultsType">
    <result>
        <resultSets>
            <resultSet>
                <row>
                    <column1>11111</column1>
                    <column2>0</column2>
                    <column3>imageId/111111</column3>
                    <column4>2012-04-03T10:11:22.187</column4>
                </row>
                <row>
                    <column1>11111</column1>
                    <column2>2</column2>
                    <column3>imageId/111112</column3>
                    <column4>2012-04-03T10:11:22.187</column4>
                </row>
                <row>
                    <column1>11111</column1>
                    <column2>2</column2>
                    <column3>imageId/111113</column3>
                    <column4>2012-04-03T10:11:22.187</column4>
                </row>
                <row>
                    <column1>22222</column1>
                    <column2>0</column2>
                    <column3>imageId/222222</column3>
                    <column4>2012-04-03T10:11:22.187</column4>
                </row>
                <row>
                    <column1>22222</column1>
                    <column2>2</column2>
                    <column3>imageId/222223</column3>
                    <column4>2012-04-03T10:11:22.187</column4>
                </row>
            </resultSet>
        </resultSets>
    </result>
</results>

However i would like it to look like this:

<?xml version="1.0" encoding="UTF-8"?>
<results xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ResultsType">
    <result>
        <row>
            <id>11111</id>
            <lagrgeImage>imageId/111111</lagrgeImage>
            <smallImage>imageId/111112</smallImage>
            <smallImage>imageId/111113</smallImage>
        </row>
        <row>
            <id>22222</id>
            <lagrgeImage>imageId/222222</lagrgeImage>
            <smallImage>imageId/222223</smallImage>
        </row>
    </result>
</results>

As you can see there are two filtering condition:

If column2 = 0 then largeImage tag is needed in the output however column2 = 2 then smallImage tag is needed in the output.

UPDATE

Both of the examples below worked perfectly, however they are both including namespacing in the root that are unexpected. The output i get is:

<?xml version="1.0" encoding="utf-8"?>
<results xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ResultsType">
    <result>
        <row>
            <id>11111</id>
            <largeImage>imageId/111111</largeImage>
            <smallImage>imageId/111112</smallImage>
            <smallImage>imageId/111113</smallImage>
        </row>
        <row>
            <id>22222</id>
            <largeImage>imageId/222222</largeImage>
            <smallImage>imageId/222223</smallImage>
        </row>
    </result>
</results>

How do i remove xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ResultsType" from the above output?

Answer 1

For spitting out the 'largeimage' and 'smallimage' elements (in an xsl for loop) try:

<xsl:if test="/results/result/resultSets/resultSet/row/column2='0'">
<largeImage>
<xsl:value-of select="/results/result/resultSets/resultSet/row/column3"/>
</largeImage>    
</xsl:if>

Answer 2

In XSLT 1.0, the (IMO) most straightforward solution is to first find all unique IDs. This can be achieved by looking for IDs that did not appear in any preceding siblings (in other words, for each ID, you find the first element that contains that ID).

Once you have a unique ID, you can <xsl:for-each> over all <row> siblings (including the current element) with the same ID. In the below code, I have used an <xsl:choose> element to check <column2> and insert a <largeImage> or <smallImage> element based on the <column2> value.

This does what you are looking for:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" indent="yes"/>

    <xsl:template match="/results">
        <results>
            <xsl:copy-of select="@*"/>
            <result>
                <xsl:apply-templates select="result"/>
            </result>
        </results>
    </xsl:template>

    <xsl:template match="/results/result/resultSets/resultSet/row">
        <xsl:if test="not(column1 = preceding-sibling::row/column1)">
            <row>
                <id><xsl:value-of select="column1"/></id>
                <xsl:for-each select="../row[column1 = current()/column1]">
                    <xsl:choose>
                        <xsl:when test="column2 = '0'">
                            <largeImage><xsl:value-of select="column3"/></largeImage>
                        </xsl:when>
                        <xsl:when test="column2 = '2'">
                            <smallImage><xsl:value-of select="column3"/></smallImage>
                        </xsl:when>
                    </xsl:choose>
                </xsl:for-each>
            </row>
        </xsl:if>
    </xsl:template>

</xsl:stylesheet>

Answer 3

If you are using XSLT2.0 you make use of the for-each-group function.

<xsl:for-each-group select="row" group-by="column1">

Assuming your context is resultSets this would group the rows by the 'id' in column1. The current grouping key could then be obtained as follows:

<xsl:value-of select="current-grouping-key()"/>

And to get the various rows in the group, to transform them to either largeImage or smallImage, you would do this

<xsl:apply-templates select="current-group()" />

Here is the full XSLT

<xsl:stylesheet version="1.0" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns="http://www.castiron.com//response">

   <xsl:output method="xml" indent="yes"/>

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

   <xsl:template match="resultSet">
      <xsl:apply-templates select="@*"/>
      <xsl:for-each-group select="row" group-by="column1">
         <row>
            <id><xsl:value-of select="current-grouping-key()"/></id>
            <xsl:apply-templates select="current-group()" />
          </row>
      </xsl:for-each-group>
   </xsl:template>

   <xsl:template match="row[column2='0']">
      <largeImage><xsl:value-of select="column3" /></largeImage>
   </xsl:template>

   <xsl:template match="row">
      <smallImage><xsl:value-of select="column3" /></smallImage>
   </xsl:template>

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

   <xsl:template match="*">
      <xsl:element name="{local-name()}"> 
         <xsl:apply-templates select="@*|node()"/>
      </xsl:element>
   </xsl:template>
</xsl:stylesheet>

When applied to your sample XML, the following is output

<results xmlns="http://www.castiron.com//response">
   <result>
      <row>
         <id>11111</id>
         <largeImage>imageId/111111</largeImage>
         <smallImage>imageId/111112</smallImage>
         <smallImage>imageId/111113</smallImage>
      </row>
      <row>
         <id>22222</id>
         <largeImage>imageId/222222</largeImage>
         <smallImage>imageId/222223</smallImage>
      </row>
   </result>
</results>