Introduction

I have an XML in the following format:

<section>
  <entries>
    <entry>
      <name>Product 1</name>
      <categories>
        <category>Fruit</category>
        <category>Apple</category>
        <category>Green</category>
      </categories>
    </entry>
    <entry>
      <name>Product 2</name>
      <categories>
        <category>Fruit</category>
        <category>Apple</category>
        <category>Red</category>
      </categories>
    </entry>
    <entry>
      <name>Product 3</name>
      <categories>
        <category>Fruit</category>
        <category>Apple</category>
        <category>Blue</category>
      </categories>
    </entry>
    <entry>
      <name>Product 4</name>
      <categories>
        <category>Fruit</category>
        <category>Apple</category>
        <category>Black</category>
      </categories>
    </entry>
    <entry>
      <name>Product 5</name>
      <categories>
        <category>Fruit</category>
        <category>Apple</category>
        <category>Purple</category>
      </categories>
    </entry>
  </entries>
</section>

I want to convert this XML into another format of the following type:

<categories>
  <category>
    <categoryname>Fruit</categoryname>
    <products>
      <product>Product 1</product>
      <product>Product 2</product>
      <product>Product 3</product>
      <product>Product 4</product>
      <product>Product 5</product>
    </products>
  </category>
  <category>
    <categoryname>Apple</categoryname>
    <products>
      <product>Product 1</product>
      <product>Product 2</product>
      <product>Product 3</product>
      <product>Product 4</product>
      <product>Product 5</product>
    </products>
  </category>
  <category>
    <categoryname>Green</categoryname>
    <products>
      <product>Product 1</product>
    </products>
  </category>
  <category>
    <categoryname>Red</categoryname>
    <products>
      <product>Product 2</product>
    </products>
  </category>
  <category>
    <categoryname>Blue</categoryname>
    <products>
      <product>Product 3</product>
    </products>
  </category>
  <category>
    <categoryname>Black</categoryname>
    <products>
      <product>Product 4</product>
    </products>
  </category>
  <category>
    <categoryname>Purple</categoryname>
    <products>
      <product>Product 5</product>
    </products>
  </category>
</categories>

Building the Sample

Custom XSLT is required in BizTalk Map for such type of format conversion.

First create Schema for Input and OutPut Xml.

Custom XSLT will convert this kind of format :

Within the XSLT, we will use a method called the Muenchian Method to handle the grouping.

Just below the <xsl:output> block of our XSLT, we're going to create a key value for this grouping. This creates an index which the transform engine can use to loop through each unique value of that key.

<xsl:key name="groups" match="entries/entry/categories/category/text()" use="."/>

From there, at the top of where we're going to start our /Category branch, we can modify the "for-each" block to loop through our key instead of source nodes, like we might typically do.

<xsl:for-each select="entries/entry/categories/category/text()[generate-id()=generate-id(key('groups',.))]">

Finally we loop through the "groups" key we created before and output our data:

<xsl:for-each select="key('groups',.)">


<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:key name="groups" match="entries/entry/categories/category/text()" use="."/>
  <xsl:template match="/">
    <xsl:apply-templates select="/section" />
  </xsl:template>
  <xsl:template match="/section">
    <categories>
      <xsl:for-each select="entries/entry/categories/category/text()[generate-id()=generate-id(key('groups',.))]">
        <category>
          <categoryname>
            <xsl:value-of select="." />
          </categoryname>
          <products>
            <xsl:for-each select="key('groups',.)">
              <product>
                <xsl:value-of select="../../../name/text()" />
              </product>
            </xsl:for-each>
          </products>
        </category >
      </xsl:for-each>
    </categories>
  </xsl:template>
</xsl:stylesheet>

See Also

Read suggested related topics:

Good place to find an extensive amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.