Create XML based on 2 XMLs and looking up values in both files using XSLT2.0

Question

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>
</parent>

2nd XML- Getting this from another source

<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>

First I need to create 2 Data Tags as there are 2 distinct names - John and Allison(This part is already done and running fine). Then I need to check for John, whatever unique city tags are present in the DB rows returned. Lets consider the 1st XML, we have John related to Boston and Seattle. So one by one, I will check those cities in the 2nd XML and for every parent tag I match something, I will create a new tag details and paste all the relevant content.

1) If there are no matching entries, details tag should not be created, as there is no matching entry.

2) The city tag is going to come under a parent tag. The values of city tag will be UNIQUE within the parent tag. I have to match the city one by one in 2nd XML and take values from all the matching city tags, across all parents from the 2nd XML 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 -

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>

Currently my XSLT is resulting in someting like below -

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

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

score 0 · Accepted Answer · answered Apr 29 '20 at 14:22

Use keys to resolve the cross-references:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="#all"
    version="3.0">

  <xsl:param name="details">
<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>      
  </xsl:param>

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

  <xsl:output method="xml" indent="yes" />

  <xsl:template match="parent">
    <FinalData>
        <xsl:for-each-group select="child" group-by="name">
            <Data>
                <xsl:copy-of select="name"/>
            </Data>
            <Details>
                <xsl:apply-templates select="key('parent-ref', current-group()/city, $details)"/>
            </Details>
        </xsl:for-each-group>
    </FinalData>
  </xsl:template>

  <xsl:template match="details/parent">
      <detail>
          <xsl:apply-templates select="key('detail-ref', current-group()/city, .)"/>
      </detail>
  </xsl:template>

  <xsl:template match="detail">
      <city value="{city}">
          <xsl:value-of select="code"/>
      </city>
  </xsl:template>

</xsl:stylesheet>

https://xsltfiddle.liberty-development.net/gVhDDyY

For completeness, the second document is inlined but you can of course use <xsl:param name="details" select="doc('details.xml')"/> instead.

Perfect. This works for me but I am having troubles in understanding the logic a bit as I am not good with **keys** and here we are using 2 keys so. It would be great if you could please explain in brief how this is working? — karD, Apr 30 '20 at 16:07
@Kartik, have you tried to read a section keys in an XSLT tutorial or book (like https://cranesoftwrights.github.io/books/ptux/index.htm, to just name one available online) to understand keys? Perhaps start there and if you don't manage to apply the introduction there to this case then ask a new, specific question. The declaration of a key instructs the XSLT processor to "index" the `match`ed nodes by the `use` expression and the `key` function then allows to use to efficiently select/search nodes by the key value. — Martin Honnen, May 04 '20 at 17:49
is there any way to extract the **city** from 1st XML when its matching in the _apply-template_ part and not take the value from 2nd XML where it matched the template? If you see the below code snippet, ` ` // here I want the **city** tag value from 1st XML and not the 2nd. — karD, Jun 04 '20 at 09:26
@karD, please ask new, even if related question, as a new, separate question with the necessary details. — Martin Honnen, Jun 04 '20 at 09:37
thanks, have posted it here https://stackoverflow.com/questions/62195646/extract-value-from-one-xml-while-matching-template-in-other-xml-using-xslt . would be grateful if you can help me out. — karD, Jun 05 '20 at 09:16

Create XML based on 2 XMLs and looking up values in both files using XSLT2.0

1 Answers1

Linked