Search code examples
xmlxsltxslt-2.0xslt-grouping

Need to create XML based on another XML using XSLT2.0 based on distinct attributes and group by

I am trying to generate a XML based on 2 other XMLs. I am polling a DB that returns details of people(There can be n number of people returned in query). The final XML should have the exact number of Data tags as the distinct name tags in the XML coming from DB. For Ex:

1st XML- Getting this from DB

<parent>
    <child>
        <name>John</name>
        <city>Boston</city>
    </child>
    <child>
        <name>John</name>
        <city>Seattle</city>
    </child>
    <child>
        <name>Allison</name>
        <city>Houston</city>
    </child>
    <child>
        <name>John</name>
        <city>Boston</city>
    </child>
</parent>

2nd XML- Getting this from another source

<details>
    <detail>
        <city>Boston</city>
        <code>abc</code>
    </detail>
    <detail>
        <city>Houston</city>
        <code>xyz</code>
    </detail>
</details>

First I need to create 2 Data Tags as there are 2 distinct names(John and Allison) and then I need to check one by one if the City Tag for all child tags in the 1st XML matches the in 2nd XML and then create Details Tag and copy Code Tag and its value in the Final XML.

1) If there are no matching entries, details tag should not be created, as there is no matching entry. 2) No there wont be multiple matching entries ever. The data tag should be created based on the Distinct NAME tags and details tag based on the number of matching city tags.

Final XML to be generated

<FinalData>
    <Data>
        <name>John</name>
        <details>
            <city>Boston</city>
            <code>xyz</code>
        </details>
    </Data>
    <Data>
        <name>Allison</name>
        <details>
            <city>Houston</city>
            <code>abc</code>
        </details>
    </Data>
</FinalData>

EDIT 1:

I am facing issues while populating the code tag as I have to match the city tag in both the XMLs and need a for loop for matching.

The below is my attempt - 

<?xml version="1.0" encoding="UTF-8"?>
<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:param name="otherFile" select="document('SOF Input 2.xml')"/>
    <xsl:template match="parent">
        <FinalData>
            <xsl:for-each-group select="child" group-by="name">
                <data>
                    <name>
                        <xsl:value-of select="name"/>
                    </name>
                    <details>
                        <city>
                            <xsl:value-of select="city"/>
                        </city>
                        <code>
                            <xsl:for-each select="$otherFile/details/detail">
                                <xsl:if test="parent/child/city='/city'">
                                    <!--I am getting stuck here - not able to get value here..i guess both city tags are referring to the same-->
                                    <xsl:value-of select="/code"/>
                                </xsl:if>
                            </xsl:for-each>
                        </code>
                    </details>
                </data>
                <xsl:text>
</xsl:text>
            </xsl:for-each-group>
        </FinalData>
    </xsl:template>
</xsl:stylesheet>

I am getting the below output with this - 

<?xml version="1.0" encoding="UTF-8"?>
<FinalData><data>
        <name>John</name>
        <details>
            <city>Boston</city>
            <code/>
        </details>
    </data>
<data>
        <name>Allison</name>
        <details>
            <city>Houston</city>
            <code/>
        </details>
    </data>
</FinalData>

Its slightly wrongly indented as well. Let me know if I missed anything.

EDIT 2:

I am afraid the scenario mentioned by Michael has come much earlier than expected. The city tag is going to repeat but come under a parent tag. The values of city tag will be unique within the parent tag. I have to take values from all the matching city tags,one by one, across all parents and populate in a manner where whatever matched in a parent tag goes in the respective detail tag in output XML. PFB the sample XMLs, that would explain in a better way -

1st XML - 

<parent>
    <child>
        <name>John</name>
        <city>Boston</city>
    </child>
    <child>
        <name>John</name>
        <city>Seattle</city>
    </child>
    <child>
        <name>Allison</name>
        <city>Houston</city>
    </child>
    <child>
        <name>John</name>
        <city>Boston</city>
    </child>
</parent>

2nd XML-

<details>
    <parent>
        <detail>
            <city>Boston</city>
            <code>abc</code>
        </detail>
        <detail>
            <city>Houston</city>
            <code>xyz</code>
        </detail>
    </parent>
    <parent>
        <detail>
            <city>Boston</city>
            <code>abc</code>
        </detail>
        <detail>
            <city>Seattle</city>
            <code>mno</code>
        </detail>
    </parent>
    <parent>
        <detail>
            <city>Houston</city>
            <code>xyz</code>
        </detail>
        <detail>
            <city>Seattle</city>
            <code>mno</code>
        </detail>
    </parent>
</details>

Final Expected XML-

<FinalData>
    <Data>
        <name>John</name>
        <details>
            <detail>
                <city value="Boston">abc</city>
            </detail>
            <detail>
                <city value="Boston">abc</city>
                <city value="Seattle">mno</city>
            </detail>
            <detail>
                <city value="Seattle">mno</city>
            </detail>
        </details>
    </Data>
    <Data>
        <name>Allison</name>
        <details>
            <detail>
                <city value="Houston">xyz</city>
            </detail>
            <detail>
                <city value="Houston">xyz</city>
            </detail>
        </details>
    </Data>
</FinalData>

Hope this is clear, as I am not a good at giving explanations.

Solution

  • Try it this way?

    XSLT 2.0

    <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:strip-space elements="*"/>

<xsl:param name="otherFile" select="document('SOF Input 2.xml')"/>

<xsl:key name="detail-by-city" match="detail" use="city" />

<xsl:template match="/parent">
    <FinalData>
        <xsl:for-each-group select="child" group-by="name">
            <data>
                <xsl:copy-of select="name"/>
                <xsl:for-each select="key('detail-by-city', current-group()/city, $otherfile)">
                    <details>
                         <xsl:copy-of select="*"/>
                    </details>
                </xsl:for-each>
            </data>
        </xsl:for-each-group>
    </FinalData>
</xsl:template>

</xsl:stylesheet>

    This is assuming a group can have more than one matching entry in the other document, and you want to create a separate details node for each of those.