Grouping Distinct Value nodes using Xquery and XSLT

Below sample explains to get distinct nodes from any xml and returning nodes for the same group node.

Let say the input has below xml where for multiple <person> node, you need to group <mail> nodes for the same <age> node

I/P

<persons>
 <person>
  <name>Tom</name>
  <age>20</age>
  <mail>abc@test.com</mail>
 </person>
 <person>
  <name>Tom</name>
  <age>10</age>
  <mail>pqr@test.com</mail>
 </person>
 <person>
  <name>Tom</name>
  <age>20</age>
  <mail>xyz@test.com</mail>
 </person>

</persons>

O/P

<persons>
 <person>
  <name>Tom</name>
  <age>20</age>
  <mails>
   <mail>abc@test.com</mail>
   <mail>pqr@test.com</mail>   <mail>xyz@test.com</mail>  </mails>
 </person>
</persons>

Below is the XQUERY solution to generates the above output

O/P

(:: pragma bea:global-element-parameter parameter="$groupSetup1" element="ns1:persons" location="../XSD/Inp.xsd" ::)
(:: pragma bea:global-element-return element="ns0:persons" location="../XSD/Out.xsd" ::)

declare namespace ns1 = "http://www.input.org";
declare namespace ns0 = "http://www.output.org";
declare namespace xf = "http://tempuri.org/Outbound820/Transformations/GroupTransform/";

declare function xf:GroupTransform($groupSetup1 as element(ns1:persons))
    as element(ns0:persons) {
        <ns0:persons>
            <ns0:person>
                {
                    
                    for $g in fn:distinct-values($groupSetup1/ns1:persons/ns1:age)
                    return
                            <ns0:age>{ $g }</ns0:age>
                            <ns0:mails>
                                {
                                for $grpset in $groupSetup1/ns1:persons
                                where $grpset[ns1:age eq $g]/ns1:mail/text() != ''
                                return
                                <ns0:mail>{ data($grpset[ns1:age eq $g]/ns1:mail/text()) }</ns0:mail>
                                }
                            </ns0:mails>
                        
                }
            </ns0:person>
        </ns0:persons>
};

declare variable $groupSetup1 as element(ns1:persons) external;

xf:GroupTransform($groupSetup1)




Grouping XML elements using XSLT - xsl:for-each-group

This is an informal post on XSLT for-each-group which is very powerful function to group xml elements based on some criteria.

For Ex, you may want to convert a flat structure, typically returned by DB into an xml which is, say department wise. for-each-group is what you want

This sample is to convert a flat structure into a projectOwner wise grouping

Input

<MailPayload>                              
    <ActivityPayload>                              
        <ActivityId>101</ActivityId>      
        <ActivityName>T1</ActivityName>      
        <ActivityOwner>Act1Owner</ActivityOwner>      
        <ProjectId>1001</ProjectId>          
        <ProjectName>Prj1</ProjectName>      
        <ProjectOwner>PrjOwner</ProjectOwner>      
        <ApprovalStatus>some desc 1</ApprovalStatus>
        <Comments>comments1</Comments>
    </ActivityPayload>                              
    <ActivityPayload>                              
        <ActivityId>102</ActivityId>      
        <ActivityName>T2</ActivityName>      
        <ActivityOwner>Act2Owner</ActivityOwner>      
        <ProjectId>1002</ProjectId>          
        <ProjectName>Prj2</ProjectName>      
        <ProjectOwner>PrjOwner</ProjectOwner>      
        <ApprovalStatus>some desc 2</ApprovalStatus>
        <Comments>comments2</Comments>
    </ActivityPayload>                              
</MailPayload>

Ouput Format required

<?xml version="1.0" encoding="UTF-8"?>
<MailPayload>
<ProjectOwnerWise>
<ProjectOwner>PrjOwner</ProjectOwner>
<Activities>
<IndActivity>
<ActivityId>101</ActivityId>
<ActivityName>T1</ActivityName>
<ProjectId>1001</ProjectId>
<ProjectName>Prj1</ProjectName>
<Comments>comments1</Comments>
</IndActivity>
<IndActivity>
<ActivityId>102</ActivityId>
<ActivityName>T2</ActivityName>
<ProjectId>1002</ProjectId>
<ProjectName>Prj2</ProjectName>
<Comments>comments2</Comments>
</IndActivity>
</Activities>
</ProjectOwnerWise>
<ProjectOwnerWise>
<ProjectOwner>PrjOwner2</ProjectOwner>
<Activities>
<IndActivity>
<ActivityId>103</ActivityId>
<ActivityName>T3</ActivityName>
<ProjectId>1002</ProjectId>
<ProjectName>Prj3</ProjectName>
<Comments>comments2</Comments>
</IndActivity>
</Activities>
</ProjectOwnerWise>
</MailPayload>

XSLT
-------

<?xml version="1.0"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
   <MailPayload>
<xsl:for-each-group select="/MailPayload/ActivityPayload" group-by="ProjectOwner">
    <ProjectOwnerWise>
<ProjectOwner>
  <xsl:value-of select="current-grouping-key()"/>
</ProjectOwner>
<Activities>
  <xsl:for-each select="current-group()">
   <IndActivity>
<xsl:copy-of select="ActivityId|ActivityName|ProjectId|ProjectName|ReviewerDecision|Comments"/>
   </IndActivity>
  </xsl:for-each>
</Activities>
</ProjectOwnerWise>
</xsl:for-each-group>
   </MailPayload>
  </xsl:template>
</xsl:stylesheet>

for-each-group groups the entire xml as per the group-by value defined and loads in memory

ex : <xsl:for-each-group select="/MailPayload/ActivityPayload" group-by="ProjectOwner">  -- ProjectOwner here

current-grouping-key() gives the values of all ProjectOwner

current-group() lists all the tags defined in a particular set