4

I am working on a BizTalk application that performs typed polling from a database using the WCF SQL adapter. After researching how to group data from the generated schema to my canonical schema, I came across Muenchian Grouping as the common solution. However, I am having a hard time figure out how to apply it to my situation.

Basically, the data that I am polling is spread out across multiple tables which are a mix of one-to-one and one-to-many relationships that share the same primary key across all the tables. Due to the one-to-many relationships, I cannot extract all of the data from the various tables using a single SELECT statement, and opted to instead split it up as multiple queries in my Polling Data statement which generated the source schema shown in the image below.

What I am trying to do is simply separate all of the records from my source schema by "SharedID" and put them into the appropriate record in my destination schema. (I also have secondary instance number ID's for subgroups, but I would imagine those will be solved in the same manner.) All of the examples I have seen show how to do this when the XSLT key is derived from a single node or multiple nodes from the source schema, but they don't show how to account for when the same ID is coming from multiple nodes.

I have analyzed the examples from many sources as best I can, but I am still new to XSLT so I am having a hard time figuring out how to set up my keys and for-each statements to accommodate this approach. Any help demystifying how I should go about doing this would be greatly appreciated. Thanks!

Here is a screenshot of a map using generalized versions of my source and destination schema structure.

enter image description here

Here is the XSLT that the map above generates, which I am using as my starting point.

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var"
                exclude-result-prefixes="msxsl var s0"
                version="1.0"
                xmlns:s0="http://SmallGenericSchemaTest.Schemas/SourceSchema"
                xmlns:ns0="http://SmallGenericSchemaTest.Schemas/DestinationSchema">
    <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />

    <xsl:template match="/">
        <xsl:apply-templates select="/s0:TypedPolling" />
    </xsl:template>

    <xsl:template match="/s0:TypedPolling">
        <ns0:AllRecords>
            <ns0:SingleRecord>
                <xsl:for-each select="s0:TypedPollingResultSet0">
                    <xsl:for-each select="s0:TypedPollingResultSet0">
                        <ns0:GroupA>
                            <xsl:if test="s0:SharedID">
                                <ns0:SharedID>
                                    <xsl:value-of select="s0:SharedID/text()" />
                                </ns0:SharedID>
                            </xsl:if>

                            <xsl:if test="s0:MiscInfoA1">
                                <ns0:MiscInfoA1>
                                    <xsl:value-of select="s0:MiscInfoA1/text()" />
                                </ns0:MiscInfoA1>
                            </xsl:if>

                            <xsl:if test="s0:MiscInfoA2">
                                <ns0:MiscInfoA2>
                                    <xsl:value-of select="s0:MiscInfoA2/text()" />
                                </ns0:MiscInfoA2>
                            </xsl:if>
                        </ns0:GroupA>
                    </xsl:for-each>
                </xsl:for-each>

                <xsl:for-each select="s0:TypedPollingResultSet1">
                    <xsl:for-each select="s0:TypedPollingResultSet1">
                        <ns0:GroupB>
                            <xsl:if test="s0:MiscInfoB1">
                                <ns0:MiscInfoB1>
                                    <xsl:value-of select="s0:MiscInfoB1/text()" />
                                </ns0:MiscInfoB1>
                            </xsl:if>

                            <xsl:if test="s0:MiscInfoB2">
                                <ns0:MiscInfoB2>
                                    <xsl:value-of select="s0:MiscInfoB2/text()" />
                                </ns0:MiscInfoB2>
                            </xsl:if>

                            <ns0:SubGroupBAContainer>
                                <xsl:for-each select="../../s0:TypedPollingResultSet2">
                                    <xsl:for-each select="s0:TypedPollingResultSet2">
                                        <ns0:SubGroupBA>
                                            <xsl:if test="s0:MiscInfoBA1">
                                                <ns0:MiscInfoBA1>
                                                    <xsl:value-of select="s0:MiscInfoBA1/text()" />
                                                </ns0:MiscInfoBA1>
                                            </xsl:if>

                                            <xsl:if test="s0:MiscInfoBA2">
                                                <ns0:MiscInfoBA2>
                                                    <xsl:value-of select="s0:MiscInfoBA2/text()" />
                                                </ns0:MiscInfoBA2>
                                            </xsl:if>
                                        </ns0:SubGroupBA>
                                    </xsl:for-each>
                                </xsl:for-each>
                            </ns0:SubGroupBAContainer>

                            <ns0:SubGroupBBContainer>
                                <xsl:for-each select="../../s0:TypedPollingResultSet3">
                                    <xsl:for-each select="s0:TypedPollingResultSet3">
                                        <ns0:SubGroupBB>
                                            <xsl:if test="s0:MiscInfoBB1">
                                                <ns0:MiscInfoBB1>
                                                    <xsl:value-of select="s0:MiscInfoBB1/text()" />
                                                </ns0:MiscInfoBB1>
                                            </xsl:if>

                                            <xsl:if test="s0:MiscInfoBB2">
                                                <ns0:MiscInfoBB2>
                                                    <xsl:value-of select="s0:MiscInfoBB2/text()" />
                                                </ns0:MiscInfoBB2>
                                            </xsl:if>
                                        </ns0:SubGroupBB>
                                    </xsl:for-each>
                                </xsl:for-each>
                            </ns0:SubGroupBBContainer>
                        </ns0:GroupB>
                    </xsl:for-each>
                </xsl:for-each>

                <ns0:GroupCContainer>
                    <xsl:for-each select="s0:TypedPollingResultSet4">
                        <xsl:for-each select="s0:TypedPollingResultSet4">
                            <ns0:GroupC>
                                <xsl:if test="s0:GroupCInstanceID">
                                    <ns0:GroupCInstanceID>
                                        <xsl:value-of select="s0:GroupCInstanceID/text()" />
                                    </ns0:GroupCInstanceID>
                                </xsl:if>

                                <xsl:if test="s0:MiscInfoC1">
                                    <ns0:MiscInfoC1>
                                        <xsl:value-of select="s0:MiscInfoC1/text()" />
                                    </ns0:MiscInfoC1>
                                </xsl:if>

                                <xsl:if test="s0:MiscInfoC2">
                                    <ns0:MiscInfoC2>
                                        <xsl:value-of select="s0:MiscInfoC2/text()" />
                                    </ns0:MiscInfoC2>
                                </xsl:if>
                            </ns0:GroupC>
                        </xsl:for-each>
                    </xsl:for-each>
                </ns0:GroupCContainer>

                <ns0:GroupDContainer>
                    <xsl:for-each select="s0:TypedPollingResultSet5">
                        <xsl:for-each select="s0:TypedPollingResultSet5">
                            <ns0:GroupD>
                                <xsl:if test="s0:GroupDInstanceID">
                                    <ns0:GroupDInstanceID>
                                        <xsl:value-of select="s0:GroupDInstanceID/text()" />
                                    </ns0:GroupDInstanceID>
                                </xsl:if>

                                <xsl:if test="s0:MiscInfoD1">
                                    <ns0:MiscInfoD1>
                                        <xsl:value-of select="s0:MiscInfoD1/text()" />
                                    </ns0:MiscInfoD1>
                                </xsl:if>

                                <xsl:if test="s0:MiscInfoD2">
                                    <ns0:MiscInfoD2>
                                        <xsl:value-of select="s0:MiscInfoD2/text()" />
                                    </ns0:MiscInfoD2>
                                </xsl:if>
                                <xsl:for-each select="../../s0:TypedPollingResultSet6">
                                    <xsl:for-each select="s0:TypedPollingResultSet6">
                                        <ns0:SubGroupDA>
                                            <xsl:if test="s0:MiscInfoDA1">
                                                <ns0:MiscInfoDA1>
                                                    <xsl:value-of select="s0:MiscInfoDA1/text()" />
                                                </ns0:MiscInfoDA1>
                                            </xsl:if>

                                            <xsl:if test="s0:MiscInfoDA2">
                                                <ns0:MiscInfoDA2>
                                                    <xsl:value-of select="s0:MiscInfoDA2/text()" />
                                                </ns0:MiscInfoDA2>
                                            </xsl:if>
                                        </ns0:SubGroupDA>
                                    </xsl:for-each>
                                </xsl:for-each>

                                <ns0:SubGroupDBContainer>
                                    <xsl:for-each select="../../s0:TypedPollingResultSet7">
                                        <xsl:for-each select="s0:TypedPollingResultSet7">
                                            <ns0:SubGroupDB>
                                                <xsl:if test="s0:MiscInfoDB1">
                                                    <ns0:MiscInfoDB1>
                                                        <xsl:value-of select="s0:MiscInfoDB1/text()" />
                                                    </ns0:MiscInfoDB1>
                                                </xsl:if>

                                                <xsl:if test="s0:MiscInfoDB2">
                                                    <ns0:MiscInfoDB2>
                                                        <xsl:value-of select="s0:MiscInfoDB2/text()" />
                                                    </ns0:MiscInfoDB2>
                                                </xsl:if>
                                            </ns0:SubGroupDB>
                                        </xsl:for-each>
                                    </xsl:for-each>
                                </ns0:SubGroupDBContainer>
                            </ns0:GroupD>
                        </xsl:for-each>
                    </xsl:for-each>
                </ns0:GroupDContainer>
            </ns0:SingleRecord>
        </ns0:AllRecords>
    </xsl:template>
</xsl:stylesheet>

EDIT:

Here is a sample input xml document and the desired output.

Sample input:

<ns0:TypedPolling xmlns:ns0="http://SmallGenericSchemaTest.Schemas/SourceSchema">
    <ns0:TypedPollingResultSet0>
        <ns0:TypedPollingResultSet0>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:MiscInfoA1>A1_1</ns0:MiscInfoA1>
            <ns0:MiscInfoA2>A2_1</ns0:MiscInfoA2>
        </ns0:TypedPollingResultSet0>

        <ns0:TypedPollingResultSet0>
            <ns0:SharedID>SharedID_2</ns0:SharedID>
            <ns0:MiscInfoA1>A1_2</ns0:MiscInfoA1>
            <ns0:MiscInfoA2>A2_2</ns0:MiscInfoA2>
        </ns0:TypedPollingResultSet0>
    </ns0:TypedPollingResultSet0>

    <ns0:TypedPollingResultSet1>
        <ns0:TypedPollingResultSet1>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:MiscInfoB1>B1_1</ns0:MiscInfoB1>
            <ns0:MiscInfoB2>B2_1</ns0:MiscInfoB2>
        </ns0:TypedPollingResultSet1>

        <ns0:TypedPollingResultSet1>
            <ns0:SharedID>SharedID_2</ns0:SharedID>
            <ns0:MiscInfoB1>B1_2</ns0:MiscInfoB1>
            <ns0:MiscInfoB2>B2_2</ns0:MiscInfoB2>
        </ns0:TypedPollingResultSet1>
    </ns0:TypedPollingResultSet1>

    <ns0:TypedPollingResultSet2>
        <ns0:TypedPollingResultSet2>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:MiscInfoBA1>BA1_1A</ns0:MiscInfoBA1>
            <ns0:MiscInfoBA2>BA2_1A</ns0:MiscInfoBA2>
        </ns0:TypedPollingResultSet2>

        <ns0:TypedPollingResultSet2>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:MiscInfoBA1>BA1_1B</ns0:MiscInfoBA1>
            <ns0:MiscInfoBA2>BA2_1B</ns0:MiscInfoBA2>
        </ns0:TypedPollingResultSet2>

        <ns0:TypedPollingResultSet2>
            <ns0:SharedID>SharedID_2</ns0:SharedID>
            <ns0:MiscInfoBA1>BA1_2A</ns0:MiscInfoBA1>
            <ns0:MiscInfoBA2>BA2_2A</ns0:MiscInfoBA2>
        </ns0:TypedPollingResultSet2>

        <ns0:TypedPollingResultSet2>
            <ns0:SharedID>SharedID_2</ns0:SharedID>
            <ns0:MiscInfoBA1>BA1_2B</ns0:MiscInfoBA1>
            <ns0:MiscInfoBA2>BA2_2B</ns0:MiscInfoBA2>
        </ns0:TypedPollingResultSet2>
    </ns0:TypedPollingResultSet2>

    <ns0:TypedPollingResultSet3>
        <ns0:TypedPollingResultSet3>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:MiscInfoBB1>BB1_1A</ns0:MiscInfoBB1>
            <ns0:MiscInfoBB2>BB2_1A</ns0:MiscInfoBB2>
        </ns0:TypedPollingResultSet3>

        <ns0:TypedPollingResultSet3>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:MiscInfoBB1>BB1_1B</ns0:MiscInfoBB1>
            <ns0:MiscInfoBB2>BB2_1B</ns0:MiscInfoBB2>
        </ns0:TypedPollingResultSet3>

        <ns0:TypedPollingResultSet3>
            <ns0:SharedID>SharedID_2</ns0:SharedID>
            <ns0:MiscInfoBB1>BB1_2</ns0:MiscInfoBB1>
            <ns0:MiscInfoBB2>BB2_2</ns0:MiscInfoBB2>
        </ns0:TypedPollingResultSet3>
    </ns0:TypedPollingResultSet3>

    <ns0:TypedPollingResultSet4>
        <ns0:TypedPollingResultSet4>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:GroupCInstanceID>GroupCInstanceID_1A</ns0:GroupCInstanceID>
            <ns0:MiscInfoC1>C1_1A</ns0:MiscInfoC1>
            <ns0:MiscInfoC2>C2_1A</ns0:MiscInfoC2>
        </ns0:TypedPollingResultSet4>

        <ns0:TypedPollingResultSet4>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:GroupCInstanceID>GroupCInstanceID_1B</ns0:GroupCInstanceID>
            <ns0:MiscInfoC1>C1_1B</ns0:MiscInfoC1>
            <ns0:MiscInfoC2>C2_1B</ns0:MiscInfoC2>
        </ns0:TypedPollingResultSet4>

        <ns0:TypedPollingResultSet4>
            <ns0:SharedID>SharedID_2</ns0:SharedID>
            <ns0:GroupCInstanceID>GroupCInstanceID_2</ns0:GroupCInstanceID>
            <ns0:MiscInfoC1>C1_2</ns0:MiscInfoC1>
            <ns0:MiscInfoC2>C2_2</ns0:MiscInfoC2>
        </ns0:TypedPollingResultSet4>
    </ns0:TypedPollingResultSet4>

    <ns0:TypedPollingResultSet5>
        <ns0:TypedPollingResultSet5>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:GroupDInstanceID>GroupDInstanceID_1A</ns0:GroupDInstanceID>
            <ns0:MiscInfoD1>D1_1A</ns0:MiscInfoD1>
            <ns0:MiscInfoD2>D2_1A</ns0:MiscInfoD2>
        </ns0:TypedPollingResultSet5>

        <ns0:TypedPollingResultSet5>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:GroupDInstanceID>GroupDInstanceID_1B</ns0:GroupDInstanceID>
            <ns0:MiscInfoD1>D1_1B</ns0:MiscInfoD1>
            <ns0:MiscInfoD2>D2_1B</ns0:MiscInfoD2>
        </ns0:TypedPollingResultSet5>

        <ns0:TypedPollingResultSet5>
            <ns0:SharedID>SharedID_2</ns0:SharedID>
            <ns0:GroupDInstanceID>GroupDInstanceID_2</ns0:GroupDInstanceID>
            <ns0:MiscInfoD1>D1_2</ns0:MiscInfoD1>
            <ns0:MiscInfoD2>D2_2</ns0:MiscInfoD2>
        </ns0:TypedPollingResultSet5>
    </ns0:TypedPollingResultSet5>

    <ns0:TypedPollingResultSet6>
        <ns0:TypedPollingResultSet6>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:GroupDInstanceID>GroupDInstanceID_1A</ns0:GroupDInstanceID>
            <ns0:MiscInfoDA1>DA1_1A</ns0:MiscInfoDA1>
            <ns0:MiscInfoDA2>DA2_1A</ns0:MiscInfoDA2>
        </ns0:TypedPollingResultSet6>

        <ns0:TypedPollingResultSet6>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:GroupDInstanceID>GroupDInstanceID_1B</ns0:GroupDInstanceID>
            <ns0:MiscInfoDA1>DA1_1B</ns0:MiscInfoDA1>
            <ns0:MiscInfoDA2>DA2_1B</ns0:MiscInfoDA2>
        </ns0:TypedPollingResultSet6>

        <ns0:TypedPollingResultSet6>
            <ns0:SharedID>SharedID_2</ns0:SharedID>
            <ns0:GroupDInstanceID>GroupDInstanceID_2</ns0:GroupDInstanceID>
            <ns0:MiscInfoDA1>DA1_2</ns0:MiscInfoDA1>
            <ns0:MiscInfoDA2>DA2_2</ns0:MiscInfoDA2>
        </ns0:TypedPollingResultSet6>
    </ns0:TypedPollingResultSet6>

    <ns0:TypedPollingResultSet7>
        <ns0:TypedPollingResultSet7>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:GroupDInstanceID>GroupDInstanceID_1A</ns0:GroupDInstanceID>
            <ns0:MiscInfoDB1>DB1_1A</ns0:MiscInfoDB1>
            <ns0:MiscInfoDB2>DB2_1A</ns0:MiscInfoDB2>
        </ns0:TypedPollingResultSet7>

        <ns0:TypedPollingResultSet7>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:GroupDInstanceID>GroupDInstanceID_1B</ns0:GroupDInstanceID>
            <ns0:MiscInfoDB1>DB1_1B</ns0:MiscInfoDB1>
            <ns0:MiscInfoDB2>DB2_1B</ns0:MiscInfoDB2>
        </ns0:TypedPollingResultSet7>

        <ns0:TypedPollingResultSet7>
            <ns0:SharedID>SharedID_2</ns0:SharedID>
            <ns0:GroupDInstanceID>GroupDInstanceID_2</ns0:GroupDInstanceID>
            <ns0:MiscInfoDB1>DB1_2</ns0:MiscInfoDB1>
            <ns0:MiscInfoDB2>DB2_2</ns0:MiscInfoDB2>
        </ns0:TypedPollingResultSet7>
    </ns0:TypedPollingResultSet7>
</ns0:TypedPolling>

Desired output:

<ns0:AllRecords xmlns:ns0="http://SmallGenericSchemaTest.Schemas/DestinationSchema">
    <ns0:SingleRecord>
        <ns0:GroupA>
            <ns0:SharedID>SharedID_1</ns0:SharedID>
            <ns0:MiscInfoA1>A1_1</ns0:MiscInfoA1>
            <ns0:MiscInfoA2>A2_1</ns0:MiscInfoA2>
        </ns0:GroupA>

        <ns0:GroupB>
            <ns0:MiscInfoB1>B1_1</ns0:MiscInfoB1>
            <ns0:MiscInfoB2>B2_1</ns0:MiscInfoB2>

            <ns0:SubGroupBAContainer>
                <ns0:SubGroupBA>
                    <ns0:MiscInfoBA1>BA1_1A</ns0:MiscInfoBA1>
                    <ns0:MiscInfoBA2>BA2_1A</ns0:MiscInfoBA2>
                </ns0:SubGroupBA>

                <ns0:SubGroupBA>
                    <ns0:MiscInfoBA1>BA1_1B</ns0:MiscInfoBA1>
                    <ns0:MiscInfoBA2>BA2_1B</ns0:MiscInfoBA2>
                </ns0:SubGroupBA>
            </ns0:SubGroupBAContainer>

            <ns0:SubGroupBBContainer>
                <ns0:SubGroupBB>
                    <ns0:MiscInfoBB1>BB1_1A</ns0:MiscInfoBB1>
                    <ns0:MiscInfoBB2>BB2_1A</ns0:MiscInfoBB2>
                </ns0:SubGroupBB>

                <ns0:SubGroupBB>
                    <ns0:MiscInfoBB1>BB1_1B</ns0:MiscInfoBB1>
                    <ns0:MiscInfoBB2>BB2_1B</ns0:MiscInfoBB2>
                </ns0:SubGroupBB>
            </ns0:SubGroupBBContainer>
        </ns0:GroupB>

        <ns0:GroupCContainer>
            <ns0:GroupC>
                <ns0:GroupCInstanceID>GroupCInstanceID_1A</ns0:GroupCInstanceID>
                <ns0:MiscInfoC1>C1_1A</ns0:MiscInfoC1>
                <ns0:MiscInfoC2>C2_1A</ns0:MiscInfoC2>
            </ns0:GroupC>

            <ns0:GroupC>
                <ns0:GroupCInstanceID>GroupCInstanceID_1B</ns0:GroupCInstanceID>
                <ns0:MiscInfoC1>C1_1B</ns0:MiscInfoC1>
                <ns0:MiscInfoC2>C2_1B</ns0:MiscInfoC2>
            </ns0:GroupC>
        </ns0:GroupCContainer>

        <ns0:GroupDContainer>
            <ns0:GroupD>
                <ns0:GroupDInstanceID>GroupDInstanceID_1A</ns0:GroupDInstanceID>
                <ns0:MiscInfoD1>D1_1A</ns0:MiscInfoD1>
                <ns0:MiscInfoD2>D2_1A</ns0:MiscInfoD2>

                <ns0:SubGroupDA>
                    <ns0:MiscInfoDA1>DA1_1A</ns0:MiscInfoDA1>
                    <ns0:MiscInfoDA2>DA2_1A</ns0:MiscInfoDA2>
                </ns0:SubGroupDA>

                <ns0:SubGroupDBContainer>
                    <ns0:SubGroupDB>
                        <ns0:MiscInfoDB1>DB1_1A</ns0:MiscInfoDB1>
                        <ns0:MiscInfoDB2>DB2_1A</ns0:MiscInfoDB2>
                    </ns0:SubGroupDB>
                </ns0:SubGroupDBContainer>
            </ns0:GroupD>

            <ns0:GroupD>
                <ns0:GroupDInstanceID>GroupDInstanceID_1B</ns0:GroupDInstanceID>
                <ns0:MiscInfoD1>D1_1B</ns0:MiscInfoD1>
                <ns0:MiscInfoD2>D2_1B</ns0:MiscInfoD2>

                <ns0:SubGroupDA>
                    <ns0:MiscInfoDA1>DA1_1B</ns0:MiscInfoDA1>
                    <ns0:MiscInfoDA2>DA2_1B</ns0:MiscInfoDA2>
                </ns0:SubGroupDA>

                <ns0:SubGroupDBContainer>
                    <ns0:SubGroupDB>
                        <ns0:MiscInfoDB1>DB1_1B</ns0:MiscInfoDB1>
                        <ns0:MiscInfoDB2>DB2_1B</ns0:MiscInfoDB2>
                    </ns0:SubGroupDB>
                </ns0:SubGroupDBContainer>
            </ns0:GroupD>
        </ns0:GroupDContainer>
    </ns0:SingleRecord>

    <ns0:SingleRecord>
        <ns0:GroupA>
            <ns0:SharedID>SharedID_2</ns0:SharedID>
            <ns0:MiscInfoA1>A1_2</ns0:MiscInfoA1>
            <ns0:MiscInfoA2>A2_2</ns0:MiscInfoA2>
        </ns0:GroupA>

        <ns0:GroupB>
            <ns0:MiscInfoB1>B1_2</ns0:MiscInfoB1>
            <ns0:MiscInfoB2>B2_2</ns0:MiscInfoB2>

            <ns0:SubGroupBAContainer>
                <ns0:SubGroupBA>
                    <ns0:MiscInfoBA1>BA1_2A</ns0:MiscInfoBA1>
                    <ns0:MiscInfoBA2>BA2_2A</ns0:MiscInfoBA2>
                </ns0:SubGroupBA>

                <ns0:SubGroupBA>
                    <ns0:MiscInfoBA1>BA1_2B</ns0:MiscInfoBA1>
                    <ns0:MiscInfoBA2>BA2_2B</ns0:MiscInfoBA2>
                </ns0:SubGroupBA>
            </ns0:SubGroupBAContainer>

            <ns0:SubGroupBBContainer>
                <ns0:SubGroupBB>
                    <ns0:MiscInfoBB1>BB1_2</ns0:MiscInfoBB1>
                    <ns0:MiscInfoBB2>BB2_2</ns0:MiscInfoBB2>
                </ns0:SubGroupBB>
            </ns0:SubGroupBBContainer>
        </ns0:GroupB>

        <ns0:GroupCContainer>
            <ns0:GroupC>
                <ns0:GroupCInstanceID>GroupCInstanceID_2</ns0:GroupCInstanceID>
                <ns0:MiscInfoC1>C1_2</ns0:MiscInfoC1>
                <ns0:MiscInfoC2>C2_2</ns0:MiscInfoC2>
            </ns0:GroupC>
        </ns0:GroupCContainer>

        <ns0:GroupDContainer>
            <ns0:GroupD>
                <ns0:GroupDInstanceID>GroupDInstanceID_2</ns0:GroupDInstanceID>
                <ns0:MiscInfoD1>D1_2</ns0:MiscInfoD1>
                <ns0:MiscInfoD2>D2_2</ns0:MiscInfoD2>

                <ns0:SubGroupDA>
                    <ns0:MiscInfoDA1>DA1_2</ns0:MiscInfoDA1>
                    <ns0:MiscInfoDA2>DA2_2</ns0:MiscInfoDA2>
                </ns0:SubGroupDA>

                <ns0:SubGroupDBContainer>
                    <ns0:SubGroupDB>
                        <ns0:MiscInfoDB1>DB1_2</ns0:MiscInfoDB1>
                        <ns0:MiscInfoDB2>DB2_2</ns0:MiscInfoDB2>
                    </ns0:SubGroupDB>
                </ns0:SubGroupDBContainer>
            </ns0:GroupD>
        </ns0:GroupDContainer>
    </ns0:SingleRecord>
</ns0:AllRecords>
Community
  • 1
  • 1
NspectorHector
  • 143
  • 1
  • 8
  • Without a sample payload it is a bit hard for people to reproduce. Can you add that? Also highlighting what the current output is vs what you want would also help. – Dijkgraaf Sep 03 '16 at 03:01
  • Did you try a FOR XML query? In that case, SQL Server will talk care of all the grouping for you. – Johns-305 Sep 05 '16 at 12:49
  • Actually, very important, this may *not* be a Muenchian case. Is SharedID in TypedPollingResultSet0 unique per resultset? If so, this is not a Muenchian case and gets a lot easier. – Johns-305 Sep 05 '16 at 13:00
  • Dijkgraaf, that's a good point. I added a sample input and the desired output. – NspectorHector Sep 06 '16 at 17:14
  • Johns-305, we have used FOR XML in the past, but the scripts end up being a few thousand lines of generating XML manually. I am avoiding that in hopes of finding a better way, which led me to this approach. As for the question on SharedID in TypedPollingResultSet0, yes it is unique per resultset. Are you saying I should be able to just use for-each elements without dealing with assigning keys? – NspectorHector Sep 06 '16 at 17:20

1 Answers1

0

As per Johns-305's suggestion, I looked at alternatives to Muenchian grouping and arrived at the solution below which appears to be working as intended. It certainly seems that I had given myself tunnel vision after looking into the Muenchian method. The end result did end up being a lot easier, but my inexperience with XSLT made it less obvious.

Of course, if someone knows of a better way to do this or sees any room for improvement, please feel free to make additional suggestions. I'll keep the question open for a few days before accepting this answer in case a better one comes along. Thanks!

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var"
                exclude-result-prefixes="msxsl var s0"
                version="1.0"
                xmlns:s0="http://SmallGenericSchemaTest.Schemas/SourceSchema"
                xmlns:ns0="http://SmallGenericSchemaTest.Schemas/DestinationSchema">
    <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />

    <xsl:template match="/">
        <xsl:apply-templates select="/s0:TypedPolling" />
    </xsl:template>

    <xsl:template match="/s0:TypedPolling">
        <ns0:AllRecords>
            <xsl:for-each select="s0:TypedPollingResultSet0/s0:TypedPollingResultSet0">
                <ns0:SingleRecord>

                    <ns0:GroupA>
                        <xsl:if test="s0:SharedID">
                            <ns0:SharedID>
                                <xsl:value-of select="s0:SharedID/text()" />
                            </ns0:SharedID>
                        </xsl:if>

                        <xsl:if test="s0:MiscInfoA1">
                            <ns0:MiscInfoA1>
                                <xsl:value-of select="s0:MiscInfoA1/text()" />
                            </ns0:MiscInfoA1>
                        </xsl:if>

                        <xsl:if test="s0:MiscInfoA2">
                            <ns0:MiscInfoA2>
                                <xsl:value-of select="s0:MiscInfoA2/text()" />
                            </ns0:MiscInfoA2>
                        </xsl:if>
                    </ns0:GroupA>


                    <xsl:for-each select="/s0:TypedPolling/s0:TypedPollingResultSet1/s0:TypedPollingResultSet1[s0:SharedID = current()/s0:SharedID]">
                        <ns0:GroupB>
                            <xsl:if test="s0:MiscInfoB1">
                                <ns0:MiscInfoB1>
                                    <xsl:value-of select="s0:MiscInfoB1/text()" />
                                </ns0:MiscInfoB1>
                            </xsl:if>

                            <xsl:if test="s0:MiscInfoB2">
                                <ns0:MiscInfoB2>
                                    <xsl:value-of select="s0:MiscInfoB2/text()" />
                                </ns0:MiscInfoB2>
                            </xsl:if>

                            <ns0:SubGroupBAContainer>
                                <xsl:for-each select="/s0:TypedPolling/s0:TypedPollingResultSet2/s0:TypedPollingResultSet2[s0:SharedID = current()/s0:SharedID]">
                                    <ns0:SubGroupBA>
                                        <xsl:if test="s0:MiscInfoBA1">
                                            <ns0:MiscInfoBA1>
                                                <xsl:value-of select="s0:MiscInfoBA1/text()" />
                                            </ns0:MiscInfoBA1>
                                        </xsl:if>

                                        <xsl:if test="s0:MiscInfoBA2">
                                            <ns0:MiscInfoBA2>
                                                <xsl:value-of select="s0:MiscInfoBA2/text()" />
                                            </ns0:MiscInfoBA2>
                                        </xsl:if>
                                    </ns0:SubGroupBA>
                                </xsl:for-each>
                            </ns0:SubGroupBAContainer>

                            <ns0:SubGroupBBContainer>
                                <xsl:for-each select="/s0:TypedPolling/s0:TypedPollingResultSet3/s0:TypedPollingResultSet3[s0:SharedID = current()/s0:SharedID]">
                                    <ns0:SubGroupBB>
                                        <xsl:if test="s0:MiscInfoBB1">
                                            <ns0:MiscInfoBB1>
                                                <xsl:value-of select="s0:MiscInfoBB1/text()" />
                                            </ns0:MiscInfoBB1>
                                        </xsl:if>

                                        <xsl:if test="s0:MiscInfoBB2">
                                            <ns0:MiscInfoBB2>
                                                <xsl:value-of select="s0:MiscInfoBB2/text()" />
                                            </ns0:MiscInfoBB2>
                                        </xsl:if>
                                    </ns0:SubGroupBB>
                                </xsl:for-each>
                            </ns0:SubGroupBBContainer>
                        </ns0:GroupB>
                    </xsl:for-each>


                    <xsl:if test="/s0:TypedPolling/s0:TypedPollingResultSet4/s0:TypedPollingResultSet4[s0:SharedID = current()/s0:SharedID]">
                        <ns0:GroupCContainer>
                            <xsl:for-each select="/s0:TypedPolling/s0:TypedPollingResultSet4/s0:TypedPollingResultSet4[s0:SharedID = current()/s0:SharedID]">
                                <ns0:GroupC>
                                    <xsl:if test="s0:GroupCInstanceID">
                                        <ns0:GroupCInstanceID>
                                            <xsl:value-of select="s0:GroupCInstanceID/text()" />
                                        </ns0:GroupCInstanceID>
                                    </xsl:if>

                                    <xsl:if test="s0:MiscInfoC1">
                                        <ns0:MiscInfoC1>
                                            <xsl:value-of select="s0:MiscInfoC1/text()" />
                                        </ns0:MiscInfoC1>
                                    </xsl:if>

                                    <xsl:if test="s0:MiscInfoC2">
                                        <ns0:MiscInfoC2>
                                            <xsl:value-of select="s0:MiscInfoC2/text()" />
                                        </ns0:MiscInfoC2>
                                    </xsl:if>
                                </ns0:GroupC>
                            </xsl:for-each>
                        </ns0:GroupCContainer>
                    </xsl:if>


                    <xsl:if test="/s0:TypedPolling/s0:TypedPollingResultSet5/s0:TypedPollingResultSet5[s0:SharedID = current()/s0:SharedID]">
                        <ns0:GroupDContainer>
                            <xsl:for-each select="/s0:TypedPolling/s0:TypedPollingResultSet5/s0:TypedPollingResultSet5[s0:SharedID = current()/s0:SharedID]">
                                <ns0:GroupD>
                                    <xsl:if test="s0:GroupDInstanceID">
                                        <ns0:GroupDInstanceID>
                                            <xsl:value-of select="s0:GroupDInstanceID/text()" />
                                        </ns0:GroupDInstanceID>
                                    </xsl:if>

                                    <xsl:if test="s0:MiscInfoD1">
                                        <ns0:MiscInfoD1>
                                            <xsl:value-of select="s0:MiscInfoD1/text()" />
                                        </ns0:MiscInfoD1>
                                    </xsl:if>

                                    <xsl:if test="s0:MiscInfoD2">
                                        <ns0:MiscInfoD2>
                                            <xsl:value-of select="s0:MiscInfoD2/text()" />
                                        </ns0:MiscInfoD2>
                                    </xsl:if>

                                    <xsl:if test="/s0:TypedPolling/s0:TypedPollingResultSet6/s0:TypedPollingResultSet6[s0:SharedID = current()/s0:SharedID]
                                            and /s0:TypedPolling/s0:TypedPollingResultSet6/s0:TypedPollingResultSet6[s0:GroupDInstanceID = current()/s0:GroupDInstanceID]">
                                        <xsl:for-each select="/s0:TypedPolling/s0:TypedPollingResultSet6/s0:TypedPollingResultSet6[s0:SharedID = current()/s0:SharedID
                                                      and s0:GroupDInstanceID = current()/s0:GroupDInstanceID]">
                                            <ns0:SubGroupDA>
                                                <xsl:if test="s0:MiscInfoDA1">
                                                    <ns0:MiscInfoDA1>
                                                        <xsl:value-of select="s0:MiscInfoDA1/text()" />
                                                    </ns0:MiscInfoDA1>
                                                </xsl:if>

                                                <xsl:if test="s0:MiscInfoDA2">
                                                    <ns0:MiscInfoDA2>
                                                        <xsl:value-of select="s0:MiscInfoDA2/text()" />
                                                    </ns0:MiscInfoDA2>
                                                </xsl:if>
                                            </ns0:SubGroupDA>
                                        </xsl:for-each>
                                    </xsl:if>

                                    <xsl:if test="/s0:TypedPolling/s0:TypedPollingResultSet7/s0:TypedPollingResultSet7[s0:SharedID = current()/s0:SharedID]
                                            and /s0:TypedPolling/s0:TypedPollingResultSet7/s0:TypedPollingResultSet7[s0:GroupDInstanceID = current()/s0:GroupDInstanceID]">
                                        <ns0:SubGroupDBContainer>
                                            <xsl:for-each select="/s0:TypedPolling/s0:TypedPollingResultSet7/s0:TypedPollingResultSet7[s0:SharedID = current()/s0:SharedID
                                                          and s0:GroupDInstanceID = current()/s0:GroupDInstanceID]">
                                                <ns0:SubGroupDB>
                                                    <xsl:if test="s0:MiscInfoDB1">
                                                        <ns0:MiscInfoDB1>
                                                            <xsl:value-of select="s0:MiscInfoDB1/text()" />
                                                        </ns0:MiscInfoDB1>
                                                    </xsl:if>

                                                    <xsl:if test="s0:MiscInfoDB2">
                                                        <ns0:MiscInfoDB2>
                                                            <xsl:value-of select="s0:MiscInfoDB2/text()" />
                                                        </ns0:MiscInfoDB2>
                                                    </xsl:if>
                                                </ns0:SubGroupDB>
                                            </xsl:for-each>
                                        </ns0:SubGroupDBContainer>
                                    </xsl:if>
                                </ns0:GroupD>
                            </xsl:for-each>
                        </ns0:GroupDContainer>
                    </xsl:if>

                </ns0:SingleRecord>
            </xsl:for-each>
        </ns0:AllRecords>
    </xsl:template>
</xsl:stylesheet>
NspectorHector
  • 143
  • 1
  • 8