Grouping XML key value using XSLT

Questions about XML that are not covered by the other forums should go here.
198709km
Posts: 2
Joined: Wed Jun 22, 2022 12:34 pm

Grouping XML key value using XSLT

Post by 198709km »

Hi All,

There is a query for which i am seeing assistance, on how to group the XML by keyElements using XSLT. Below is the sample XML code: -

Code: Select all

<?xml version = "1.0" encoding = "UTF-8"?>
<CISDocument>
    <Aheader>
        <OperationName>APINAME</OperationName>
    </Aheader>
    <ResponseHeader>
        <CompletedSuccessfully>true</CompletedSuccessfully>
    </ResponseHeader>
    <Page>
        <StartAtRow>0</StartAtRow>
        <MaxRows>5</MaxRows>
        <TotalRowCount>1</TotalRowCount>
    </Page>
    <Entity>
        <Load>
            <LID>A</LID>
            <ccode>B</ccode>
            <servicecode>C</servicecode>
            <flocationcode>D</flocationcode>
            <llocationcode>E</llocationcode>
            <BaseShipmentModule>
                <ShipUnitModule>
                    <Shnum>1234567</Shnum>
                    <Sfromlocation>01234</Sfromlocation>
                    <Stolocation>04567</Stolocation>
                    <Container>
                        <SContainerID>1</SContainerID>
                        <Quantity>1</Quantity>
                        <ContainerVinformation>
                            <Volume>1</Volume>
                        </ContainerVinformation>
                        <ItemNumber>AA</ItemNumber>
                        <WFClass>
                            <Fweigth>222</Fweigth>
                            <FClassCode>10</FClassCode>
                        </WFClass>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>BBB</ReferenceNumberTypeCode>
                            <ReferenceNumber>CCC</ReferenceNumber>
                        </ReferenceNumberStructure>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>DDD</ReferenceNumberTypeCode>
                            <ReferenceNumber>1233</ReferenceNumber>
                        </ReferenceNumberStructure>
                    </Container>
                </ShipUnitModule>
            </BaseShipmentModule>
            <BaseShipmentModule>
                <ShipUnitModule>
                    <Shnum>1234568</Shnum>
                    <Sfromlocation>01234</Sfromlocation>
                    <Stolocation>04567</Stolocation>
                    <Container>
                        <SContainerID>1</SContainerID>
                        <Quantity>1</Quantity>
                        <ContainerVinformation>
                            <Volume>1</Volume>
                        </ContainerVinformation>
                        <ItemNumber>AA</ItemNumber>
                        <WFClass>
                            <Fweigth>222</Fweigth>
                            <FClassCode>10</FClassCode>
                        </WFClass>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>BBB</ReferenceNumberTypeCode>
                            <ReferenceNumber>CCC</ReferenceNumber>
                        </ReferenceNumberStructure>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>DDD</ReferenceNumberTypeCode>
                            <ReferenceNumber>1233</ReferenceNumber>
                        </ReferenceNumberStructure>
                    </Container>
                </ShipUnitModule>
            </BaseShipmentModule>
            <BaseShipmentModule>
                <ShipUnitModule>
                    <Shnum>1234569</Shnum>
                    <Sfromlocation>11234</Sfromlocation>
                    <Stolocation>14567</Stolocation>
                    <Container>
                        <SContainerID>1</SContainerID>
                        <Quantity>1</Quantity>
                        <ContainerVinformation>
                            <Volume>1</Volume>
                        </ContainerVinformation>
                        <ItemNumber>AA</ItemNumber>
                        <WFClass>
                            <Fweigth>222</Fweigth>
                            <FClassCode>10</FClassCode>
                        </WFClass>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>BBB</ReferenceNumberTypeCode>
                            <ReferenceNumber>CCC</ReferenceNumber>
                        </ReferenceNumberStructure>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>DDD</ReferenceNumberTypeCode>
                            <ReferenceNumber>1233</ReferenceNumber>
                        </ReferenceNumberStructure>
                    </Container>
                </ShipUnitModule>
            </BaseShipmentModule>
            <BaseShipmentModule>
                <ShipUnitModule>
                    <Shnum>1234570</Shnum>
                    <Sfromlocation>11234</Sfromlocation>
                    <Stolocation>14567</Stolocation>
                    <Container>
                        <SContainerID>1</SContainerID>
                        <Quantity>1</Quantity>
                        <ContainerVinformation>
                            <Volume>1</Volume>
                        </ContainerVinformation>
                        <ItemNumber>AA</ItemNumber>
                        <WFClass>
                            <Fweigth>222</Fweigth>
                            <FClassCode>10</FClassCode>
                        </WFClass>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>BBB</ReferenceNumberTypeCode>
                            <ReferenceNumber>CCC</ReferenceNumber>
                        </ReferenceNumberStructure>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>DDD</ReferenceNumberTypeCode>
                            <ReferenceNumber>1233</ReferenceNumber>
                        </ReferenceNumberStructure>
                    </Container>
                </ShipUnitModule>
            </BaseShipmentModule>
            <S1>
                <SID>1613243
                </SSID>
                <SCount>3</SCount>
                <CDStop>0</CDStop>
                <AATime>2022-06-17T08:00:00</AATime>
                <SLCode>06V0018963</SLCode>
            </S1>
            <S1>
                <SSID>1613244</SSID>
                <SCount>0</SCount>
                <CDStop>16</CDStop>
                <AATime>2022-06-17T19:45:00</AATime>
                <SLCode>06C0124A</SLCode>
            </S1>
            <S1>
                <SSID>1613245</SSID>
                <SCount>4</SCount>
                <CDStop>0</CDStop>
                <AATime>2022-06-17T09:00:00</AATime>
                <SLCode>06V0029699</SLCode>
            </S1>
        </Stop>
    </Load>
</Entity>[/color][/color][/color]
As we can see in the above XML example, there are multiple <BaseShipmentModule> tag and within each <BaseShipmentModule> we have a tag called: <Sfromlocation> and <Stolocation> which stands for location code, from and to location.



Now if we observe a little bit, we have a total of 4 <BaseShipmentModule> tag and the value for <Sfromlocation> and <Stolocation> in same in the 1st and 2nd <BaseShipmentModule> tag. Where as the value for <Sfromlocation> and <Stolocation> is same in the 3rd and 4th <BaseShipmentModule> tag. Looking for to group the XML based on the <Sfromlocation> and <Stolocation>.



Expected output is shown below: -

Code: Select all

<?xml version = "1.0" encoding = "UTF-8"?>
<CISDocument>
    <Aheader>
        <OperationName>APINAME</OperationName>
    </Aheader>
    <ResponseHeader>
        <CompletedSuccessfully>true</CompletedSuccessfully>
    </ResponseHeader>
    <Page>
        <StartAtRow>0</StartAtRow>
        <MaxRows>5</MaxRows>
        <TotalRowCount>1</TotalRowCount>
    </Page>
    <Entity>
        <Load>
            <LID>A</LID>
            <ccode>B</ccode>
            <servicecode>C</servicecode>
            <flocationcode>D</flocationcode>
            <llocationcode>E</llocationcode>
            <BaseShipmentModule>
                <ShipUnitModule>
                    <Shnum>1234567</Shnum>
                    <Shnum>1234568</Shnum>
                    <Sfromlocation>01234</Sfromlocation>
                    <Stolocation>04567</Stolocation>
                    <Container>
                        <SContainerID>1</SContainerID>
                        <Quantity>1</Quantity>
                        <ContainerVinformation>
                            <Volume>1</Volume>
                        </ContainerVinformation>
                        <ItemNumber>AA</ItemNumber>
                        <WFClass>
                            <Fweigth>222</Fweigth>
                            <FClassCode>10</FClassCode>
                        </WFClass>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>BBB</ReferenceNumberTypeCode>
                            <ReferenceNumber>CCC</ReferenceNumber>
                        </ReferenceNumberStructure>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>DDD</ReferenceNumberTypeCode>
                            <ReferenceNumber>1233</ReferenceNumber>
                        </ReferenceNumberStructure>
                    </Container>
                    <Container>
                        <SContainerID>1</SContainerID>
                        <Quantity>1</Quantity>
                        <ContainerVinformation>
                            <Volume>1</Volume>
                        </ContainerVinformation>
                        <ItemNumber>AA</ItemNumber>
                        <WFClass>
                            <Fweigth>222</Fweigth>
                            <FClassCode>10</FClassCode>
                        </WFClass>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>BBB</ReferenceNumberTypeCode>
                            <ReferenceNumber>CCC</ReferenceNumber>
                        </ReferenceNumberStructure>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>DDD</ReferenceNumberTypeCode>
                            <ReferenceNumber>1233</ReferenceNumber>
                        </ReferenceNumberStructure>
                    </Container>
                </ShipUnitModule>
            </BaseShipmentModule>
            <BaseShipmentModule>
                <ShipUnitModule>
                    <Shnum>1234569</Shnum>
                    <Shnum>1234570</Shnum>
                    <Sfromlocation>11234</Sfromlocation>
                    <Stolocation>14567</Stolocation>
                    <Container>
                        <SContainerID>1</SContainerID>
                        <Quantity>1</Quantity>
                        <ContainerVinformation>
                            <Volume>1</Volume>
                        </ContainerVinformation>
                        <ItemNumber>AA</ItemNumber>
                        <WFClass>
                            <Fweigth>222</Fweigth>
                            <FClassCode>10</FClassCode>
                        </WFClass>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>BBB</ReferenceNumberTypeCode>
                            <ReferenceNumber>CCC</ReferenceNumber>
                        </ReferenceNumberStructure>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>DDD</ReferenceNumberTypeCode>
                            <ReferenceNumber>1233</ReferenceNumber>
                        </ReferenceNumberStructure>
                    </Container>
                    <Container>
                        <SContainerID>1</SContainerID>
                        <Quantity>1</Quantity>
                        <ContainerVinformation>
                            <Volume>1</Volume>
                        </ContainerVinformation>
                        <ItemNumber>AA</ItemNumber>
                        <WFClass>
                            <Fweigth>222</Fweigth>
                            <FClassCode>10</FClassCode>
                        </WFClass>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>BBB</ReferenceNumberTypeCode>
                            <ReferenceNumber>CCC</ReferenceNumber>
                        </ReferenceNumberStructure>
                        <ReferenceNumberStructure>
                            <ReferenceNumberTypeCode>DDD</ReferenceNumberTypeCode>
                            <ReferenceNumber>1233</ReferenceNumber>
                        </ReferenceNumberStructure>
                    </Container>
                </ShipUnitModule>
            </BaseShipmentModule>
            <S1>
                <SID>1613243
                </SSID>
                <SCount>3</SCount>
                <CDStop>0</CDStop>
                <AATime>2022-06-17T08:00:00</AATime>
                <SLCode>06V0018963</SLCode>
            </S1>
            <S1>
                <SSID>1613244</SSID>
                <SCount>0</SCount>
                <CDStop>16</CDStop>
                <AATime>2022-06-17T19:45:00</AATime>
                <SLCode>06C0124A</SLCode>
            </S1>
            <S1>
                <SSID>1613245</SSID>
                <SCount>4</SCount>
                <CDStop>0</CDStop>
                <AATime>2022-06-17T09:00:00</AATime>
                <SLCode>06V0029699</SLCode>
            </S1>
        </Stop>
    </Load>
</Entity>


As we can see in the above result, we have combined the <ShipUnitModule> based on the <Sfromlocation> and <Stolocation> tag. And he tag <Container> has been grouped and the rest of the tag <S1> is unaffected by it. We did get help from one of the Engineers (Michael), with the below XSLT,

Code: Select all

<xsl:stylesheet version="2.0" xmlns:xsl=http://www.w3.org/1999/XSL/Transform>
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
    <xsl:template match="/Entity">
        <xsl:copy>
            <xsl:for-each-group select="Shipment" group-by="concat(shipFromLocation, '|', shipToLocation)">
                <Shipment>
                    <xsl:copy-of select="shipFromLocation, shipToLocation"/>
                    <xsl:copy-of select="current-group()/container"/>
                </Shipment>
            </xsl:for-each-group>
        </xsl:copy>
    </xsl:template>
</xsl:stylesheet>


But, it is not able to help the overall output, please do help us here. Its quite important. Any suggestion will matter a lot.