CODEWITHSUNDEEP
xsltxslt-1.0muenchian-grouping
Mehmet Ali Atlihan
Mehmet Ali Atlihan

Reputation: 11

XSLT 1.0 grouping with Muenchian method


I have the following XML 

 `<?xml version="1.0" encoding="UTF-8"?>
<resultSet>
    <row>
        <ACCT_NO>79501</ACCT_NO>
        <PVT_CHOICE_CD>C1</PVT_CHOICE_CD>
        <PVT_STATUS>O</PVT_STATUS>
        <OVEERRIDE_STATUS />
        <EFFCTV_DATE>2009-11-24 01:58:06.000001</EFFCTV_DATE>
        <PREF_PROVIDER />
        <DEST_EML_ADDR>[email protected]</DEST_EML_ADDR>                                                                                                                                                                                                                                          
        <EML_PVT_TYPE_CD>O</EML_PVT_TYPE_CD>
        <REP_ID />
        <OVERD_RSN_CD />
        <OVERD_DESC />
        <OVERD_EFF_TS />
    </row>
    <row>
        <ACCT_NO>79501</ACCT_NO>
        <PVT_CHOICE_CD>D1</PVT_CHOICE_CD>
        <PVT_STATUS>O</PVT_STATUS>
        <OVEERRIDE_STATUS />
        <EFFCTV_DATE>2013-11-24 01:58:06.000001</EFFCTV_DATE>
        <PREF_PROVIDER />
        <DEST_EML_ADDR>[email protected]</DEST_EML_ADDR>                                                                                                                                                                                                                                          
        <EML_PVT_TYPE_CD>O</EML_PVT_TYPE_CD>
        <REP_ID />
        <OVERD_RSN_CD />
        <OVERD_DESC />
        <OVERD_EFF_TS />
    </row>
    <row>
        <ACCT_NO>79500</ACCT_NO>
        <PVT_CHOICE_CD>D1</PVT_CHOICE_CD>
        <PVT_STATUS>O</PVT_STATUS>
        <OVEERRIDE_STATUS />
        <EFFCTV_DATE>2012-12-23 00:12:23.000001</EFFCTV_DATE>
        <PREF_PROVIDER />
        <DEST_EML_ADDR>[email protected] </DEST_EML_ADDR>                                                                                                                                                                                                                                           
        <EML_PVT_TYPE_CD>O</EML_PVT_TYPE_CD>
        <REP_ID />
        <OVERD_RSN_CD />
        <OVERD_DESC />
        <OVERD_EFF_TS />
    </row>
</resultSet>`

This resultSet is coming from a database call and it represents many to many relationship between a card account and its preference codes. A card might be associated with an email - (DEST_EML_ADDR) and an email choice (EML_PVT_TYPE_CD)and these 2 tags will also have same values for the same card number in multiple rows. The card will also have a unique preference code and a bunch of preference related information which is unique for each row.

My goal is to pull each unique card no, dest_email_addr and eml_pvt_type_cd and associate all preference related information to that in one block per account. In sum the desired output for the above input is :

    <?xml version="1.0" encoding="UTF-8"?>
<EntityResponse>
    <EntityDetails>
        <ACCT_NO>79501 </ACCT_NO>
        <DEST_EML_ADDR>[email protected] </DEST_EML_ADDR>
        <EML_PVT_TYPE_CD>O</EML_PVT_TYPE_CD>
        <PreferenceGroup>
                    <PreferenceDetails>
                                   <PVT_CHOICE_CD>C1</PVT_CHOICE_CD>
                                    <PVT_STATUS>O</PVT_STATUS>
                                    <OVEERRIDE_STATUS />
                                    <EFFCTV_DATE>2009-11-2401:58:06.000001</EFFCTV_DATE> 
                                    <PREF_PROVIDER />
                                    <REP_ID />
                                    <OVERD_RSN_CD />
                                    <OVERD_DESC />
                                    <OVERD_EFF_TS />

                    </PreferenceDetails>
                    <PreferenceDetails>

                                      <PVT_CHOICE_CD>D1</PVT_CHOICE_CD>
                                        <PVT_STATUS>O</PVT_STATUS>
                                        <OVEERRIDE_STATUS />
                                        <EFFCTV_DATE>2013-11-24 01:58:06.000001</EFFCTV_DATE>
                                         <PREF_PROVIDER />
                                         <REP_ID />
                                        <OVERD_RSN_CD />
                                        <OVERD_DESC />
                                        <OVERD_EFF_TS />
                    </PreferenceDetails>

            </PreferenceGroup>
       </EntityDetails>
       <EntityDetails>
                       <ACCT_NO>79500</ACCT_NO>
                       <DEST_EML_ADDR>[email protected]</DEST_EML_ADDR>
                        <EML_PVT_TYPE_CD>O</EML_PVT_TYPE_CD>
                        <PreferenceGroup>
                             <PreferenceDetails>
                                <PVT_CHOICE_CD>D1</PVT_CHOICE_CD>
                                <PVT_STATUS>O</PVT_STATUS>
                                <OVEERRIDE_STATUS />
                                <EFFCTV_DATE>2012-12-23 00:12:23.000001</EFFCTV_DATE>
                                <PREF_PROVIDER />

                                <REP_ID />
                                <OVERD_RSN_CD />
                                <OVERD_DESC />
                                <OVERD_EFF_TS />

                          </PreferenceDetails>
                        </PreferenceGroup>

       </EntityDetails>



    </EntityResponse>

I am restricted to use XSLT 1.0 and after reading this Muenchian method grouping based on multiple keys thread I realize this is the solution I need as well. Based on this information I created the following XSL in which I create a key utilizing ACCT_NO, DEST_EML_ADDR and EML_PVT_TYPE_CD to filter out the unique elements and apply a second template based on a second key to further group the one to many preference code relationship:

    <?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="xml" indent="yes"/>

   <xsl:key name="entity-email-key" match="row" use="concat(ACCT_NO,'~#~',DEST_EML_ADDR, '~#~',EML_PVT_TYPE_CD)"/>
   <xsl:key name="prefcd-key" match="row" use="concat(ACCT_NO,'~#~',DEST_EML_ADDR, '~#~',EML_PVT_TYPE_CD, '~#~',PVT_CHOICE_CD)"/>


   <xsl:template match="/resultSet">
      <EntityResponse>
                  <xsl:apply-templates select="row[generate-id() = generate-id(key('entity-email-key', concat(ACCT_NO,'~#~',DEST_EML_ADDR, '~#~',EML_PVT_TYPE_CD))[1])]" mode="entity-email-key"/>
      </EntityResponse>
   </xsl:template>

   <xsl:template match="row" mode="entity-email-key">

            <EntityDetails>

                   <xsl:copy-of select="ACCT_NO|PVT_STATUS|DEST_EML_ADDR|EML_PVT_TYPE_CD"/>

                       <PreferenceGroup>
                        <xsl:apply-templates select="key('entity-email-key', concat(ACCT_NO,'~#~',DEST_EML_ADDR, '~#~',EML_PVT_TYPE_CD))[generate-id() = generate-id(key('prefcd-key', concat(ACCT_NO,'~#~',DEST_EML_ADDR, '~#~',EML_PVT_TYPE_CD,PVT_CHOICE_CD))[1])]" mode="prefcd-key"/>
                      </PreferenceGroup>
           </EntityDetails>


   </xsl:template>


   <xsl:template match="row" mode="prefcd-key">

         <PreferenceDetails>
                          <xsl:copy-of select="PVT_CHOICE_CD|PVT_STATUS|OVEERRIDE_STATUS|EFFCTV_DATE|PREF_PROVIDER|REP_ID|OVERD_RSN_CD|OVERD_DESC|OVERD_EFF_TS"/>
         </PreferenceDetails>

   </xsl:template>



</xsl:stylesheet>

But when I ran the code I realized it only groups by the ACCT_NO, EMAIL and EML_PVT_TYPE_CD yet failing to group by the preference codes related tags. I tried different combinations of selecting the preference code data, the closest that I could get is to have one preference code for each card, but the preference code could be more than one as it is the case for the first card- which has two unique preference code related data.

So I do not think I do have a great understanding of this method yet, perhaps I do not even need to define the second key but after several days of wrestling with this problem, it is time to turn to the community to see what I am missing.
Thank you!

Upvotes: 1

Views: 303

Answers (1)

Tim C
Tim C

Reputation: 70618

Looking at the XSLT in your question, you have missed out the separator ~#~ before the final PVT_CHOICE_CD in the concat statement when you use the prefcd-key key:

Try this instead:

 <xsl:apply-templates select="key('entity-email-key', concat(ACCT_NO,'~#~',DEST_EML_ADDR, '~#~',EML_PVT_TYPE_CD))
     [generate-id() = generate-id(key('prefcd-key', concat(ACCT_NO,'~#~',DEST_EML_ADDR, '~#~',EML_PVT_TYPE_CD, '~#~', PVT_CHOICE_CD))[1])]" 
     mode="prefcd-key"/>

Upvotes: 2

Related Questions