Merging XML records into one

Question

I am new to XSLT. I have requirement of merging and adding.

XML:

<OrderDetails>
  <OrderDetail action="add">
    <OrderedUnits>18</OrderedUnits>
    <Date>2013-09-30T00:00:00</Date>
    <LocationCode>3202</LocationCode>
    <PONumber>022548295755</PONumber>
  </OrderDetail>
  <OrderDetail action="add">
    <OrderedUnits>12</OrderedUnits>
    <Date>2013-09-30T00:00:00</Date>
    <LocationCode>3202</LocationCode>
    <PONumber>022548295755</PONumber>
  </OrderDetail>
  <IOrderDetail action="add">
    <OrderedUnits>18</OrderedUnits>
    <Date>2013-09-30T00:00:00</Date>
    <LocationCode>3202</LocationCode>
    <PONumber>022548295762</PONumber>
  </OrderDetail>
<OrderDetails>

If the LocationCode, Date, and PONumber fields match, I need to add the OrderedUnits and make it only one entry.

Expected output XML:

<OrderDetails>
  <OrderDetail action="add">
    <OrderedUnits>30</OrderedUnits>
    <Date>2013-09-30T00:00:00</Date>
    <LocationCode>3202</LocationCode>
    <PONumber>022548295755</PONumber>
  </OrderDetail>
  <IOrderDetail action="add">
    <OrderedUnits>18</OrderedUnits>
    <Date>2013-09-30T00:00:00</Date>
    <LocationCode>3202</LocationCode>
    <PONumber>022548295762</PONumber>
  </OrderDetail>
<OrderDetails>

How can I write this XSLT?

What XSLT engine are you using? – David Oct 24 '13 at 18:57 — David, Oct 24 '13 at 18:57
Are you using XSLT 1.0 or 2.0? – Nic Gibson Oct 24 '13 at 19:29 — Nic Gibson, Oct 24 '13 at 19:29

score 1 · Answer 1 · edited Nov 04 '13 at 09:51

What about that?

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
   <xsl:output method="xml" encoding="utf-8" indent="no" />
   <xsl:template match="/">
      <OrderDetails>
         <xsl:for-each select="//OrderDetail">
            <xsl:variable name="this_date" select="Date" />
            <xsl:variable name="this_pon" select="PONumber" />
            <xsl:variable name="this_loc" select="LocationCode" />
            <xsl:variable name="all" select="count(//OrderDetail[Date = $this_date and PONumber = $this_pon and LocationCode = $this_loc]) " />
            <xsl:variable name="before" select="count(preceding-sibling::OrderDetail[Date = $this_date and PONumber = $this_pon and LocationCode = $this_loc]) " />
            <xsl:if test="$before + 1 = $all">
               <!-- we are in the last distinct order, display it  -->
               <OrderDetail action="add">
                  <OrderedUnits>
                     <xsl:value-of select="sum(//OrderDetail[Date = $this_date and PONumber = $this_pon and LocationCode = $this_loc]/OrderedUnits)" />
                  </OrderedUnits>
                  <Date>
                     <xsl:value-of select="Date" />
                  </Date>
                  <LocationCode>
                     <xsl:value-of select="LocationCode" />
                  </LocationCode>
                  <PONumber>
                     <xsl:value-of select="PONumber" />
                  </PONumber>
               </OrderDetail>
            </xsl:if>
         </xsl:for-each>
      </OrderDetails>
   </xsl:template>
</xsl:stylesheet>

corrected XML:

<?xml version="1.0" encoding="ISO-8859-1"?>
<OrderDetails>
  <OrderDetail action="add">
    <OrderedUnits>18</OrderedUnits>
    <Date>2013-09-30T00:00:00</Date>
    <LocationCode>3202</LocationCode>
    <PONumber>022548295755</PONumber>
  </OrderDetail>

 <OrderDetail action="add">
    <OrderedUnits>12</OrderedUnits>
    <Date>2013-09-30T00:00:00</Date>
    <LocationCode>3202</LocationCode>
    <PONumber>022548295755</PONumber>
  </OrderDetail>
  <OrderDetail action="add">
    <OrderedUnits>18</OrderedUnits>
    <Date>2013-09-30T00:00:00</Date>
    <LocationCode>3202</LocationCode>
    <PONumber>022548295762</PONumber>
  </OrderDetail>
</OrderDetails>

Result (tested in):

<?xml version="1.0" encoding="UTF-8"?>
<OrderDetails>
   <OrderDetail action="add">
      <OrderedUnits>30</OrderedUnits>
      <Date>2013-09-30T00:00:00</Date>
      <LocationCode>3202</LocationCode>
      <PONumber>022548295755</PONumber>
   </OrderDetail>
   <OrderDetail action="add">
      <OrderedUnits>18</OrderedUnits>
      <Date>2013-09-30T00:00:00</Date>
      <LocationCode>3202</LocationCode>
      <PONumber>022548295762</PONumber>
   </OrderDetail>
</OrderDetails>

Daniel Haley · Answer 2 · 2013-11-06T17:40:08.777

Since you didn't specify a version and more people should be using it, here's an XSLT 2.0 option.

XML Input

<OrderDetails>
    <OrderDetail action="add">
        <OrderedUnits>18</OrderedUnits>
        <Date>2013-09-30T00:00:00</Date>
        <LocationCode>3202</LocationCode>
        <PONumber>022548295755</PONumber>
    </OrderDetail>
    <OrderDetail action="add">
        <OrderedUnits>12</OrderedUnits>
        <Date>2013-09-30T00:00:00</Date>
        <LocationCode>3202</LocationCode>
        <PONumber>022548295755</PONumber>
    </OrderDetail>
    <OrderDetail action="add">
        <OrderedUnits>18</OrderedUnits>
        <Date>2013-09-30T00:00:00</Date>
        <LocationCode>3202</LocationCode>
        <PONumber>022548295762</PONumber>
    </OrderDetail>
</OrderDetails>

XSLT 2.0

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

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

    <xsl:template match="OrderDetails">
        <xsl:copy>
            <xsl:apply-templates select="@*"/>
            <xsl:for-each-group select="OrderDetail" group-by="string-join((Date,LocationCode,PONumber),'|')">
                <xsl:copy>
                    <xsl:apply-templates select="@*"/>
                    <xsl:for-each-group select="current-group()/*" group-by="name()">
                        <xsl:apply-templates select="current-group()[1]"/>
                    </xsl:for-each-group>
                </xsl:copy>
            </xsl:for-each-group>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="OrderedUnits">
        <xsl:copy>
            <xsl:value-of select="sum(current-group())"/>
        </xsl:copy>
    </xsl:template>

</xsl:stylesheet>

XML Output

<OrderDetails>
   <OrderDetail action="add">
      <OrderedUnits>30</OrderedUnits>
      <Date>2013-09-30T00:00:00</Date>
      <LocationCode>3202</LocationCode>
      <PONumber>022548295755</PONumber>
   </OrderDetail>
   <OrderDetail action="add">
      <OrderedUnits>18</OrderedUnits>
      <Date>2013-09-30T00:00:00</Date>
      <LocationCode>3202</LocationCode>
      <PONumber>022548295762</PONumber>
   </OrderDetail>
</OrderDetails>

Merging XML records into one

2 Answers2