Combine XML Messages based on element value including sub-entities

Question

I need so combine XML nodes based on an element value and create child nodes for some elements

Sample Input XML:

<?xml version="1.0" encoding="UTF-8"?>
<orders>
    <Order>
        <OrderNumber>SO0000001</OrderNumber>
        <!--All Orders with same OrderNumber are combines-->
        <OrderLineNumber>000010</OrderLineNumber>
        <!--Product sequence number in order-->
        <OrderDate>20181026090000</OrderDate>
        <CustomerNumber>0000112233</CustomerNumber>
        <CustomerName>Customer name 01</CustomerName>
        <Quantity>1</Quantity>
        <SaleRevenue>20</SaleRevenue>
        <UnitPrice>15</UnitPrice>
        <Currency>USD</Currency>
        <ProductNumber>ZZZ000BBB</ProductNumber>
        <!--Product number in order-->
        <ProductDescription>Product 01</ProductDescription>
    </Order>
    <Order>
        <OrderNumber>SO0000001</OrderNumber> 
        <OrderLineNumber>000020</OrderLineNumber>
        <OrderDate>20181026090000</OrderDate>
        <CustomerNumber>0000112233</CustomerNumber>
        <CustomerName>Customer name 01</CustomerName>
        <Quantity>2</Quantity>
        <SaleRevenue>20</SaleRevenue>
        <UnitPrice>5</UnitPrice>
        <Currency>USD</Currency>
        <ProductNumber>ZZZ000AAA</ProductNumber>
        <ProductDescription>Product 02</ProductDescription>
    </Order>
    <Order>
        <OrderNumber>SO0000999</OrderNumber>
        <OrderLineNumber>000010</OrderLineNumber>
        <OrderDate>20181026090100</OrderDate>
        <CustomerNumber>0000778899</CustomerNumber>
        <CustomerName>Customer name 02</CustomerName>
        <Quantity>1</Quantity>
        <SaleRevenue>60</SaleRevenue>
        <UnitPrice>10</UnitPrice>
        <Currency>USD</Currency>
        <ProductNumber>ABC00111</ProductNumber>
        <ProductDescription>Product 03</ProductDescription>
    </Order>
    <Order>
        <OrderNumber>SO0000999</OrderNumber>
        <OrderLineNumber>000020</OrderLineNumber>
        <OrderDate>20181026090100</OrderDate>
        <CustomerNumber>0000778899</CustomerNumber>
        <CustomerName>Customer name 02</CustomerName>
        <Quantity>2</Quantity>
        <SaleRevenue>60</SaleRevenue>
        <UnitPrice>20</UnitPrice>
        <Currency>USD</Currency>
        <ProductNumber>ABC00222</ProductNumber>
        <ProductDescription>Product 04</ProductDescription>
    </Order>
    <Order>
        <OrderNumber>SO0000999</OrderNumber>
        <OrderLineNumber>0000230</OrderLineNumber>
        <OrderDate>20181026090100</OrderDate>
        <CustomerNumber>0000778899</CustomerNumber>
        <CustomerName>Customer name 02</CustomerName>
        <Quantity>3</Quantity>
        <SaleRevenue>60</SaleRevenue>
        <UnitPrice>30</UnitPrice>
        <Currency>USD</Currency>
        <ProductNumber>ABC00333</ProductNumber>
        <ProductDescription>Product 05</ProductDescription>
    </Order>
</orders>

from this I like to create following output using XSLT 1.0 or 2.0

<?xml version="1.0" encoding="UTF-8"?>
<orders>
    <Order>
        <OrderNumber>SO0000001</OrderNumber>    
        <OrderDate>20181026090000</OrderDate>
        <CustomerNumber>0000112233</CustomerNumber>
        <CustomerName>Customer name 01</CustomerName>
        <SaleRevenue>20</SaleRevenue>
        <products>
            <Product>
                <OrderLineNumber>000010</OrderLineNumber>
                <UnitPrice>15</UnitPrice>
                <Currency>USD</Currency>
                <Quantity>1</Quantity>
                <ProductNumber>ZZZ000BBB</ProductNumber>
                <ProductDescription>Product 01</ProductDescription>
            </Product>
            <Product>
                <OrderLineNumber>000020</OrderLineNumber>
                <Quantity>2</Quantity>
                <UnitPrice>5</UnitPrice>
                <Currency>USD</Currency>
                <ProductNumber>ZZZ000AAA</ProductNumber>
                <ProductDescription>Product 02</ProductDescription>
            </Product>
        </products>
    </Order>
    <Order>
        <OrderNumber>SO0000999</OrderNumber>
        <OrderDate>20181026090100</OrderDate>
        <CustomerNumber>0000778899</CustomerNumber>
        <CustomerName>Customer name 02</CustomerName>
        <products>
            <Product>
                <OrderLineNumber>000010</OrderLineNumber>
                <Quantity>1</Quantity>
                <UnitPrice>10</UnitPrice>
                <Currency>USD</Currency>
                <ProductNumber>ABC00111</ProductNumber>
                <ProductDescription>Product 03</ProductDescription>
            </Product>
            <Product>
                <OrderLineNumber>000020</OrderLineNumber>
                <Quantity>2</Quantity>
                <UnitPrice>20</UnitPrice>
                <Currency>USD</Currency>
                <ProductNumber>ABC00222</ProductNumber>
                <ProductDescription>Product 04</ProductDescription>
            </Product>
            <Product>
                <Quantity>3</Quantity>
                <UnitPrice>30</UnitPrice>
                <Currency>USD</Currency>
                <ProductNumber>ABC00333</ProductNumber>
                <ProductDescription>Product 05</ProductDescription>
            </Product>
        </products>
    </Order>
</orders>

I think I am close but can get the last piece working. I can't get the products included as part of the Order since I am using for-each-group (I think).

The is my current XSLT

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" version="1.0" indent="yes"/>
    <xsl:template match="orders">
        <orders>
            <xsl:for-each-group select="Order" group-by="OrderNumber">  
                <Order>
                    <xsl:if test="current-group()[2]">
                        <!--<OrderNumber name="{OrderNumber}"/>--> <!--products node-->
                        <xsl:copy-of select="OrderNumber"/>
                        <!--<xsl:copy-of select="OrderLineNumber"/>--> <!--products node-->
                        <xsl:copy-of select="OrderDate"/>
                        <xsl:copy-of select="CustomerNumber"/>
                        <xsl:copy-of select="CustomerName"/>
                        <!--<xsl:copy-of select="Quantity"/>--> <!--products node-->
                        <xsl:copy-of select="SaleRevenue"/>
                        <!--<xsl:copy-of select="UnitPrice"/>--> <!--products node-->
                        <xsl:copy-of select="Currency"/>
                        <!--<xsl:copy-of select="LineStatus"/>--> <!--products node-->
                        <!--<xsl:copy-of select="ProductNumber"/>--> <!--products node-->
                        <!--<xsl:copy-of select="ModelNumber"/>--> <!--products node-->
                        <!--<xsl:copy-of select="SerialNumber"/>--> <!--products node-->
                        <!--<xsl:copy-of select="ProductDescription"/>--> <!--products node-->
                    </xsl:if>
                </Order>
            </xsl:for-each-group>
            <xsl:if test="Order">
            <products>
                <xsl:for-each select="Order"> 
                    <product>
                        <xsl:copy-of select="OrderLineNumber"/>
                        <xsl:copy-of select="Quantity"/>
                        <xsl:copy-of select="UnitPrice"/>
                        <xsl:copy-of select="LineStatus"/>
                        <xsl:copy-of select="ProductNumber"/>
                        <xsl:copy-of select="ProductDescription"/>
                    </product>
                </xsl:for-each>
            </products>
            </xsl:if>
        </orders>
    </xsl:template>
</xsl:stylesheet>

I get the grouping working but would need to add all found product data to the created child node products. I am not an XSLT expert and am not sure if I am on the right path.

score 3 · Accepted Answer · answered Oct 30 '18 at 15:07

If you want the products to be nested in the order element, you need to move the relevant block of code within the creation of the <order> element. I also can't see what the test on current-group()[2] is trying to achieve, but I don't think you really need it.

Try this XSLT (which includes a shorter way to copy existing nodes)

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" version="1.0" indent="yes"/>
  <xsl:template match="orders">
    <orders>
      <xsl:for-each-group select="Order" group-by="OrderNumber">  
        <Order>
          <xsl:copy-of select="OrderNumber,OrderDate,CustomerNumber,CustomerName,SaleRevenue"/>
          <products>
            <xsl:for-each select="current-group()"> 
              <product>
                <xsl:copy-of select="OrderLineNumber,UnitPrice,Currency,Quantity,ProductNumber,ProductDescription"/>
              </product>
            </xsl:for-each>
          </products>          
        </Order>
      </xsl:for-each-group>
    </orders>
  </xsl:template>
</xsl:stylesheet>

Nice short solution, way cleaner than what I had in the works — ophychius, Oct 30 '18 at 15:09

score 0 · Answer 2 · answered Oct 30 '18 at 15:07

Move the products xsl:for-each into the xsl:for-each-group and add an absolute path to the Order elements. So change your XSLT to

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" version="1.0" indent="yes"/>
    <xsl:template match="orders">
        <orders>
            <xsl:for-each-group select="Order" group-by="OrderNumber">  
                <Order>
                    <xsl:if test="current-group()[2]">
                        <!--<OrderNumber name="{OrderNumber}"/>--> <!--products node-->
                        <xsl:copy-of select="OrderNumber"/>
                        <!--<xsl:copy-of select="OrderLineNumber"/>--> <!--products node-->
                        <xsl:copy-of select="OrderDate"/>
                        <xsl:copy-of select="CustomerNumber"/>
                        <xsl:copy-of select="CustomerName"/>
                        <!--<xsl:copy-of select="Quantity"/>--> <!--products node-->
                        <xsl:copy-of select="SaleRevenue"/>
                        <!--<xsl:copy-of select="UnitPrice"/>--> <!--products node-->
                        <xsl:copy-of select="Currency"/>
                        <!--<xsl:copy-of select="LineStatus"/>--> <!--products node-->
                        <!--<xsl:copy-of select="ProductNumber"/>--> <!--products node-->
                        <!--<xsl:copy-of select="ModelNumber"/>--> <!--products node-->
                        <!--<xsl:copy-of select="SerialNumber"/>--> <!--products node-->
                        <!--<xsl:copy-of select="ProductDescription"/>--> <!--products node-->
                        <!-- Moved to here -->
                        <products>
                            <xsl:for-each select="/orders/Order[OrderNumber = current()/OrderNumber]"> 
                                <product>
                                    <xsl:copy-of select="OrderLineNumber"/>
                                    <xsl:copy-of select="Quantity"/>
                                    <xsl:copy-of select="UnitPrice"/>
                                    <xsl:copy-of select="Currency"/>
                                    <xsl:copy-of select="LineStatus"/>
                                    <xsl:copy-of select="ProductNumber"/>
                                    <xsl:copy-of select="ProductDescription"/>
                                </product>
                            </xsl:for-each>
                        </products>
                    </xsl:if>
                </Order>
            </xsl:for-each-group>
            <xsl:if test="Order">
                <!-- Moved for-each from here to above ^^^ -->
            </xsl:if>
        </orders>
    </xsl:template>
</xsl:stylesheet>

This gives you the desired output.

Thanks, works! Still new to XSLT, trying to figure out how stuff works:) — user28441, Oct 30 '18 at 15:43

Combine XML Messages based on element value including sub-entities

2 Answers2