Flattening a hierarchy while deduping nested values using XSLT

Question

I am brand new to xml transforms and am attempting to transform an existing XML structure by flattening the information at the same time deduping nested fields but it looks like not all the data is able to be transformed.

Based on some of the limitations of the data, I need to substring some information from the source xml and dedupe that information as well as provide a new id for each product. After some trouble I was able to get it to work on a smaller subset but am noticing that when I use a larger subset of data I am getting entries not making it into the final xml.

XSLT File

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
  <xsl:output method="xml" indent="yes"/>

  <xsl:key name="category" match="/products/product/categories/category/categoryname/text()" use="substring-after(., ' &gt; ')" />
  <xsl:template match="/">
    <channel>
      <description>Testing Description</description>

      <xsl:for-each select="/products/product">
        <xsl:variable name="currentProduct" select="." />
        <xsl:choose>
          <xsl:when test="count($currentProduct/categories/category) &gt; 0">
            <xsl:for-each select="categories/category/categoryname/text()[generate-id() = generate-id(key('category', substring-after(., ' &gt; '))[1])]">
              <xsl:call-template name="output-item">
                <xsl:with-param name="product" select="$currentProduct" />
                <xsl:with-param name="category" select="substring-after(., ' &gt; ')" />
                <xsl:with-param name="category-count" select="position()" />
              </xsl:call-template>
            </xsl:for-each>
          </xsl:when>
          <xsl:otherwise>
            <xsl:call-template name="output-item">
              <xsl:with-param name="product" select="$currentProduct" />
              <xsl:with-param name="category-count" select="1" />
            </xsl:call-template>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:for-each>
    </channel>
  </xsl:template>

  <xsl:template name="output-item">
    <xsl:param name="product" />
    <xsl:param name="category-count" />
    <xsl:param name="category" />
    <item>
      <id>
        <xsl:value-of select="$product/productid"/>_<xsl:value-of select="$category-count"/>
      </id>
      <item_group_id>
        <xsl:value-of select="$product/productid"/>
      </item_group_id>
      <product_type>
        <xsl:value-of select="$category" />
      </product_type>
    </item>
  </xsl:template>

</xsl:stylesheet>

Input XML

<?xml version="1.0" encoding="utf-8" ?>
<products>
  <product>
    <productid>123</productid>
    <categories>
      <category>
        <categoryid>1</categoryid>
        <categoryname>main &gt; category-short</categoryname>
      </category>
      <category>
        <categoryid>2</categoryid>
        <categoryname>main &gt; category-medium</categoryname>
      </category>
      <category>
        <categoryid>3</categoryid>
        <categoryname>main &gt; category-large</categoryname>
      </category>
      <category>
        <categoryid>5</categoryid>
        <categoryname>main &gt; category-large</categoryname>
      </category>
    </categories>
    <image1>
      <url>image1-url</url>
    </image1>
    <image2>
      <url>image2-url</url>
    </image2>
  </product>
  <product>
    <productid>456</productid>
    <categories />
    <image1>
      <url>image1-url</url>
    </image1>
    <image2>
      <url>image2-url</url>
    </image2>
  </product>
  <product>
    <productid>789</productid>
    <categories>
      <category>
        <categoryid>1</categoryid>
        <categoryname>main &gt; category-short</categoryname>
      </category>
      <category>
        <categoryid>4</categoryid>
        <categoryname>main &gt; category-short</categoryname>
      </category>
    </categories>
    <image1>
      <url>image1-url</url>
    </image1>
    <image2>
      <url>image2-url</url>
    </image2>
  </product>
</products>

Current Output (Missing the 3rd item)

<?xml version="1.0" encoding="utf-8"?>
<channel>
  <description>Testing Description</description>
  <item>
    <id>123_1</id>
    <item_group_id>123</item_group_id>
    <product_type>category-short</product_type>
  </item>
  <item>
    <id>123_2</id>
    <item_group_id>123</item_group_id>
    <product_type>category-medium</product_type>
  </item>
  <item>
    <id>123_3</id>
    <item_group_id>123</item_group_id>
    <product_type>category-large</product_type>
  </item>
  <item>
    <id>456_1</id>
    <item_group_id>456</item_group_id>
    <product_type></product_type>
  </item>
</channel>

Expected Output (Includes 3rd item and deduped category)

<?xml version="1.0" encoding="utf-8"?>
<channel>
  <description>Testing Description</description>
  <item>
    <id>123_1</id>
    <item_group_id>123</item_group_id>
    <product_type>category-short</product_type>
  </item>
  <item>
    <id>123_2</id>
    <item_group_id>123</item_group_id>
    <product_type>category-medium</product_type>
  </item>
  <item>
    <id>123_3</id>
    <item_group_id>123</item_group_id>
    <product_type>category-large</product_type>
  </item>
  <item>
    <id>456_1</id>
    <item_group_id>456</item_group_id>
    <product_type></product_type>
  </item>
  <item>
    <id>789_1</id>
    <item_group_id>789</item_group_id>
    <product_type>category-short</product_type>
  </item>
</channel>

I believe the problem is with the deduping portion but have not been able to track it down. Any help would be awesome!

Answer 1

In XSLT 3 all you need is

  <xsl:template match="products">
    <channel>
        <description>Testing Description</description>
        <xsl:apply-templates/>
    </channel>
  </xsl:template>

  <xsl:template match="product">
      <xsl:for-each-group select="." group-by="let $keys := categories/category/categoryname/substring-after(., ' &gt; ') return if (exists($keys)) then $keys else ''">
          <item>
              <id>{productid}_{position()}</id>
              <item_group_id>{productid}</item_group_id>
              <product_type>{current-grouping-key()}</product_type>
          </item>
      </xsl:for-each-group>
  </xsl:template>

https://xsltfiddle.liberty-development.net/ncntCRN

Answer 2

How about a somewhat different approach?

XSLT 1.0

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

<xsl:key name="category" match="category" use="concat(ancestor::product/productid, '|', substring-after(categoryname, ' &gt; '))" />

<xsl:template match="/products">
    <channel>
        <description>Testing Description</description>
        <xsl:for-each select="product">
            <xsl:variable name="id" select="productid"/>
            <!-- unique categories of this product -->
            <xsl:variable name="categories" select="categories/category[count(. | key('category', concat($id, '|', substring-after(categoryname, ' &gt; ')))[1]) = 1]"/>
            <xsl:choose>
                <xsl:when test="$categories">
                    <xsl:for-each select="$categories">
                        <item>
                            <id>
                                <xsl:value-of select="concat($id, '_', position())"/>
                            </id>
                            <item_group_id>
                                <xsl:value-of select="$id"/>
                            </item_group_id>
                            <product_type>
                                <xsl:value-of select="substring-after(categoryname, ' &gt; ')"/>                    
                            </product_type>
                        </item>
                    </xsl:for-each>
                </xsl:when>
                <xsl:otherwise>
                    <item>
                        <id>
                            <xsl:value-of select="concat($id, '_1')"/>
                        </id>
                        <item_group_id>
                            <xsl:value-of select="$id"/>
                        </item_group_id>
                        <product_type/>
                    </item>
                </xsl:otherwise>
            </xsl:choose>
        </xsl:for-each>
    </channel>
</xsl:template>

</xsl:stylesheet>