none
BizTalk - XSLT grouping and structuring

    Question

  • Hi - I want to group these sequentially in order depending on following elements.
    Sequence and Code. 
    The output should be arranged sequentially looking at code sequence. 
    Code 11  1
                    2
                    3
                    4
    Code 12  1
                    2
                    3
                    4
    File instance
    <ns0:Order xmlns:ns0="http://TEST.InSchema">
      <Top>
        <T1>T1_0</T1>
        <T2>T2_0</T2>
      </Top>
      <LineItems>
        <Records>
          <Major>
            <Identification>Identification_0</Identification>
          </Major>
     
          <Minor>
            <Name>A</Name>
            <City>Cali</City>
            <Sequence>4</Sequence>
            <Quantity>100</Quantity>
            <Code>11</Code>
            <Zip>00001</Zip>
          </Minor>  
       <Minor>
            <Name>B</Name>
            <City>Cali</City>
            <Sequence>2</Sequence>
            <Quantity>122</Quantity>
            <Code>12</Code>
            <Zip>00001</Zip>
          </Minor> 
      
     <Minor>
            <Name>A</Name>
            <City>Cali</City>
            <Sequence>2</Sequence>
            <Quantity>100</Quantity>
            <Code>11</Code>
            <Zip>00001</Zip>
          </Minor>
     
     <Minor>
            <Name>A</Name>
            <City>Cali</City>
            <Sequence>1</Sequence>
            <Quantity>100</Quantity>
            <Code>11</Code>
            <Zip>00001</Zip>
          </Minor>  
     
     <Minor>
            <Name>B</Name>
            <City>Cali</City>
            <Sequence>1</Sequence>
            <Quantity>122</Quantity>
            <Code>12</Code>
            <Zip>00001</Zip>
          </Minor>
     
    <Minor>
            <Name>A</Name>
            <City>Cali</City>
            <Sequence>3</Sequence>
            <Quantity>100</Quantity>
            <Code>11</Code>
            <Zip>00001</Zip>
          </Minor>
     
     <Minor>
            <Name>B</Name>
            <City>Cali</City>
            <Sequence>3</Sequence>
            <Quantity>122</Quantity>
            <Code>12</Code>
            <Zip>00001</Zip>
          </Minor>
     
     <Minor>
            <Name>B</Name>
            <City>Cali</City>
            <Sequence>4</Sequence>
            <Quantity>122</Quantity>
            <Code>12</Code>
            <Zip>00001</Zip>
          </Minor>
     
     
        </Records>
      </LineItems>
    </ns0:Order>

    I will post schemas in following post



    Friday, February 10, 2017 2:03 AM

Answers

  • Hi Reason101,

    Use Munchean Grouping which works based on keys.

    Its very simple once you understand it. I always refer to the below post whenever I work on it. Its clearly explained with examples.

    http://www.jenitennison.com/xslt/grouping/muenchian.html

    Thanks

    Prabath

    Friday, February 10, 2017 8:30 AM

All replies

  • Inbound schema

    <?xml version="1.0" encoding="utf-16"?>
    <xs:schema xmlns="http://TEST.InSchema" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://TEST.InSchema" xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:element name="Order">
        <xs:annotation>
          <xs:appinfo>
            
          </xs:appinfo>
        </xs:annotation>
        <xs:complexType>
          <xs:sequence>
            <xs:element name="Top">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="T1" type="xs:string" />
                  <xs:element name="T2" type="xs:string" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <xs:element name="LineItems">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="Records">
                    <xs:complexType>
                      <xs:sequence>
                        <xs:element name="Major">
                          <xs:complexType>
                            <xs:sequence>
                              <xs:element name="Identification" type="xs:string" />
                            </xs:sequence>
                          </xs:complexType>
                        </xs:element>
                        <xs:element maxOccurs="unbounded" name="Minor">
                          <xs:complexType>
                            <xs:sequence>
                              <xs:element name="Name" type="xs:string" />
                              <xs:element name="City" type="xs:string" />
                              <xs:element name="Sequence" type="xs:string" />
                              <xs:element name="Quantity" type="xs:string" />
                              <xs:element name="Code" type="xs:string" />
                              <xs:element name="Zip" type="xs:string" />
                            </xs:sequence>
                          </xs:complexType>
                        </xs:element>
                      </xs:sequence>
                    </xs:complexType>
                  </xs:element>
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>
    
    

    And Output schema is below

    <?xml version="1.0" encoding="utf-16"?>
    <xs:schema xmlns="http://TEST.OutSchema" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://TEST.OutSchema" xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:element name="Root">
        <xs:complexType>
          <xs:sequence>
            <xs:element name="Header">
              <xs:complexType />
            </xs:element>
            <xs:element maxOccurs="unbounded" name="Detail">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="Id" type="xs:string" />
                  <xs:element name="SeqNo" type="xs:string" />
                  <xs:element name="Desc" type="xs:string" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>


    • Edited by Reason101 Friday, February 10, 2017 4:10 PM i
    Friday, February 10, 2017 2:09 AM
  • below is my XSLT . Please suggest anyone how to sort and order this ?

    <?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 s0" version="1.0" xmlns:ns0="http://TEST.OutSchema" xmlns:s0="http://TEST.InSchema">
      <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
    - <xsl:template match="/">
      <xsl:apply-templates select="/s0:Order" />
      </xsl:template>
      <xsl:template match="/s0:Order" />
      </xsl:stylesheet>


    • Edited by Reason101 Sunday, February 12, 2017 12:30 AM i
    Friday, February 10, 2017 4:41 AM
  • First of all I don't understand where the "Id" element in your output schema is fetched from?

    Also don't you need to have the "Detail" element in your output schema as multi-occurant?

    Anyway, here is some XSLT that groups and sorts as you describe:

    <?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 s0" version="1.0" xmlns:ns0="http://TEST.OutSchema" xmlns:s0="http://TEST.InSchema">
      <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
      <xsl:key name="codes" match="//Minor" use="Code" />
      <xsl:template match="/">
        <xsl:apply-templates select="/s0:Order" />
      </xsl:template>
      <xsl:template match="/s0:Order">
        <ns0:Root>
          <Header />
          <xsl:for-each select="//Minor[generate-id(.)=generate-id(key('codes',Code))]">
            <xsl:sort select="Code" order="ascending" />
            <xsl:variable name="code" select="Code" />
            <xsl:for-each select="//Minor[Code = $code]">
              <xsl:sort select="Sequence" />
              <Detail>
                <Id>
                  <xsl:value-of select="'N/A'" />
                </Id>
                <SeqNo>
                  <xsl:value-of select="Sequence" />
                </SeqNo>
                <Code>
                  <xsl:value-of select="Code" />
                </Code>
              </Detail>
            </xsl:for-each>
          </xsl:for-each>
        </ns0:Root>
      </xsl:template>
    </xsl:stylesheet>

    Morten la Cour

    Friday, February 10, 2017 7:56 AM
  • Hi Reason101,

    Use Munchean Grouping which works based on keys.

    Its very simple once you understand it. I always refer to the below post whenever I work on it. Its clearly explained with examples.

    http://www.jenitennison.com/xslt/grouping/muenchian.html

    Thanks

    Prabath

    Friday, February 10, 2017 8:30 AM
  • Hi La Cour,

    the XSLT you provided needs the logic,

    for each 'Code'  group all the respective Sequence numbers and for each each number we need to put constant value in Description as :

    Input example :   test instance is provide on top

    Code  Seq
    
       11  4
    
       12  1
    
       11  1
    
       12  3
    
       11   3
    
       11   2
    
       12   4
    
       12   2

    For code 11 output should be :

    Seq    Desc
    
        1   Hi
        2   Hello
        3   Mary
        4   David
    
    
    
    For code 12 output should be 
    
    Seq    Desc
    
        1   Hi
        2   Hello
        3   Mary
        4   David




    • Edited by Reason101 Friday, February 10, 2017 4:33 PM i
    Friday, February 10, 2017 4:20 PM
  • <?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 s0" version="1.0" xmlns:ns0="http://TEST.OutSchema" xmlns:s0="http://TEST.InSchema">
      <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
      <xsl:key name="codes" match="//Minor" use="Code" />
      <xsl:template match="/">
        <xsl:apply-templates select="/s0:Order" />
      </xsl:template>
      <xsl:template match="/s0:Order">
        <ns0:Root>
          <Header />
          <xsl:for-each select="//Minor[generate-id(.)=generate-id(key('codes',Code))]">
            <xsl:sort select="Code" order="ascending" />
            <xsl:variable name="code" select="Code" />
            <xsl:for-each select="//Minor[Code = $code]">
              <xsl:sort select="Sequence" />
              <xsl:variable name="desc">
                <xsl:choose>
                  <xsl:when test="position() = 1">
                    <xsl:text>Hi</xsl:text>
                  </xsl:when>
                  <xsl:when test="position() = 2">
                    <xsl:text>Hello</xsl:text>
                  </xsl:when>
                  <xsl:when test="position() = 3">
                    <xsl:text>Mary</xsl:text>
                  </xsl:when>
                  <xsl:when test="position() = 4">
                    <xsl:text>David</xsl:text>
                  </xsl:when>
                </xsl:choose>
              </xsl:variable>
              <Detail>
                <Id>
                  <xsl:value-of select="'N/A'" />
                </Id>
                <SeqNo>
                  <xsl:value-of select="Sequence" />
                </SeqNo>
                <Code>
                  <xsl:value-of select="Code" />
                </Code>
                <Desc>
                  <xsl:value-of select="$desc" />
                </Desc>
              </Detail>
            </xsl:for-each>
          </xsl:for-each>
        </ns0:Root>
      </xsl:template>
    </xsl:stylesheet>
    Morten la Cour
    Friday, February 10, 2017 5:58 PM
  • Thanks Morten, can you also please mention the logic for accumulation of Quantity for each SequenceNumber having same Code. The input may be coming for Quantity as Positive or Negative values.

    Seq 11  Desc     Qty
    
        1       Hi        110 (90+20 accumluation of Seq 11 with Line#1)
        2       Hello     55 ( 60 -5 accumluation of Seq 11 with Line# 2)
        3       Mary      35 (accumluation of Seq 11 with Line# 3)
        4       David     25 (accumluation of Seq 11 with Line# 4)
    
    
    For code 12 output should be 
    
    Seq 12  Desc Qty
    
        1      Hi        20
        2     Hello    50
        3     Mary    10
        4     David   100

    Input instance is as below :

    <ns0:Order xmlns:ns0="http://TEST.InSchema">
      <Top>
        <T1>T1_0</T1>
        <T2>T2_0</T2>
      </Top>
      <LineItems>
        <Records>
          <Major>
            <Identification>Identification_0</Identification>
          </Major>
     
          <Minor>
            <Name>A</Name>
            <City>Cali</City>
            <Sequence>4</Sequence>
            <Quantity>30</Quantity>
            <Code>11</Code>
            <Zip>00001</Zip>
          </Minor>  
    	  
    	  <Minor>
            <Name>A</Name>
            <City>Cali</City>
            <Sequence>4</Sequence>
            <Quantity>-5</Quantity>
            <Code>11</Code>
            <Zip>00001</Zip>
          </Minor> 
    	  
    	  
    	  
          <Minor>
            <Name>B</Name>
            <City>Cali</City>
            <Sequence>2</Sequence>
            <Quantity>25</Quantity>
            <Code>12</Code>
            <Zip>00001</Zip>
          </Minor> 
    	  
    	  <Minor>
            <Name>B</Name>
            <City>Cali</City>
            <Sequence>2</Sequence>
            <Quantity>25</Quantity>
            <Code>12</Code>
            <Zip>00001</Zip>
          </Minor> 
      
     <Minor>
            <Name>A</Name>
            <City>Cali</City>
            <Sequence>2</Sequence>
            <Quantity>50</Quantity>
            <Code>11</Code>
            <Zip>00001</Zip>
          </Minor>
    	  
    	  <Minor>
            <Name>A</Name>
            <City>Cali</City>
            <Sequence>2</Sequence>
            <Quantity>5</Quantity>
            <Code>11</Code>
            <Zip>00001</Zip>
          </Minor>
     
     <Minor>
            <Name>A</Name>
            <City>Cali</City>
            <Sequence>1</Sequence>
            <Quantity>100</Quantity>
            <Code>11</Code>
            <Zip>00001</Zip>
          </Minor>  
    	  
    	  
    	  <Minor>
            <Name>A</Name>
            <City>Cali</City>
            <Sequence>1</Sequence>
            <Quantity>10</Quantity>
            <Code>11</Code>
            <Zip>00001</Zip>
          </Minor>  
     
     <Minor>
            <Name>B</Name>
            <City>Cali</City>
            <Sequence>1</Sequence>
            <Quantity>122</Quantity>
            <Code>12</Code>
            <Zip>00001</Zip>
          </Minor>
     
    <Minor>
            <Name>A</Name>
            <City>Cali</City>
            <Sequence>3</Sequence>
            <Quantity>100</Quantity>
            <Code>11</Code>
            <Zip>00001</Zip>
          </Minor>
     
     <Minor>
            <Name>B</Name>
            <City>Cali</City>
            <Sequence>3</Sequence>
            <Quantity>122</Quantity>
            <Code>12</Code>
            <Zip>00001</Zip>
          </Minor>
     
     <Minor>
            <Name>B</Name>
            <City>Cali</City>
            <Sequence>4</Sequence>
            <Quantity>122</Quantity>
            <Code>12</Code>
            <Zip>00001</Zip>
          </Minor>
     
     
        </Records>
      </LineItems>
    </ns0:Order>



    • Edited by Reason101 Monday, February 13, 2017 1:44 AM i
    Monday, February 13, 2017 12:15 AM
  • Hi La Cour - if position = 10, text should be Henry. But the XSLT is just looking for I believe for position 1 instead of complete 10 and populating output as Hi. For position = 10 it should be Henry. How can I fix this ?
    Monday, February 13, 2017 11:11 PM
  • Does anyone have any ideas how can I accumulate (add) quantity items in this xsl ? It should add for 1 code, if same multiple sequence Numbers are present add them ? How can this be done in .xsl ??
    Tuesday, February 14, 2017 4:41 AM