merge two xml files xslt 3 based on the value of common id element

Question

<breakfast_menu>
<food>
<name>Belgian Waffles</name>
<id>$5</id>
<description>Two of our famous Belgian Waffles with plenty of real maple syrup</description>
<calories>650</calories>
</food>
<food>
<name>Strawberry Belgian Waffles</name>
<id>7</id>
<description>Light Belgian waffles covered with strawberries and whipped cream</description>
<calories>900</calories>
</food>
<food>
<name>Berry-Berry Belgian Waffles</name>
<id>8</id>
<description>Light Belgian waffles covered with an assortment of fresh berries and whipped cream</description>
<calories>900</calories>
</food>
<food>
<name>French Toast</name>
<id>4</id>
<description>Thick slices made from our homemade sourdough bread</description>
<calories>600</calories>
</food>
<food>
<name>Homestyle Breakfast</name>
<id>6</id>
<description>Two eggs, bacon or sausage, toast, and our ever-popular hash browns</description>
<calories>950</calories>
</food>
</breakfast_menu>

i have tried to achieve the task with the help of xsl:merge. In my trials i have to specific define in xsl which elements to retrieve. How would you suggest one with the help of xslt-3, can retrieve all elements from the second xml file, minus the id element, which already exists in XML file A, and is used as the match criteria for merging the two xml files into one?

second xml file:

<CATALOG>
<PLANT>
<COMMON>Butterfly Weed</COMMON>
<BOTANICAL>Asclepias tuberosa</BOTANICAL>
<ZONE>Annual</ZONE>
<LIGHT>Sunny</LIGHT>
<id>2</id>
<AVAILABILITY>063099</AVAILABILITY>
</PLANT>
<PLANT>
<COMMON>Primrose</COMMON>
<BOTANICAL>Oenothera</BOTANICAL>
<ZONE>3 - 5</ZONE>
<LIGHT>Sunny</LIGHT>
<id>6</id>
<AVAILABILITY>013099</AVAILABILITY>
</PLANT>
<PLANT>
<COMMON>Gentian</COMMON>
<BOTANICAL>Gentiana</BOTANICAL>
<ZONE>4</ZONE>
<LIGHT>Sun or Shade</LIGHT>
<id>17</id>
<AVAILABILITY>051899</AVAILABILITY>
</PLANT>
<PLANT>
<COMMON>Blue Gentian</COMMON>
<BOTANICAL>Gentiana</BOTANICAL>
<ZONE>4</ZONE>
<LIGHT>Sun or Shade</LIGHT>
<id>18</id>
<AVAILABILITY>050299</AVAILABILITY>
</PLANT>
<PLANT>
<COMMON>Jacob's Ladder</COMMON>
<BOTANICAL>Polemonium caeruleum</BOTANICAL>
<ZONE>Annual</ZONE>
<LIGHT>Shade</LIGHT>
<id>9</id>
<AVAILABILITY>022199</AVAILABILITY>
</PLANT>
<PLANT>
<COMMON>Greek Valerian</COMMON>
<BOTANICAL>Polemonium caeruleum</BOTANICAL>
<ZONE>Annual</ZONE>
<LIGHT>Shade</LIGHT>
<id>4</id>
<AVAILABILITY>071499</AVAILABILITY>
</PLANT>
<PLANT>
<COMMON>California Poppy</COMMON>
<BOTANICAL>Eschscholzia californica</BOTANICAL>
<ZONE>Annual</ZONE>
<LIGHT>Sun</LIGHT>
<id>7</id>
<AVAILABILITY>032799</AVAILABILITY>
</PLANT>
<PLANT>
<COMMON>Shooting Star</COMMON>
<BOTANICAL>Dodecatheon</BOTANICAL>
<ZONE>Annual</ZONE>
<LIGHT>Mostly Shady</LIGHT>
<id>8</id>
<AVAILABILITY>051399</AVAILABILITY>
</PLANT>
<PLANT>
<COMMON>Snakeroot</COMMON>
<BOTANICAL>Cimicifuga</BOTANICAL>
<ZONE>Annual</ZONE>
<LIGHT>Shade</LIGHT>
<id>5</id>
<AVAILABILITY>071199</AVAILABILITY>
</PLANT>
<PLANT>
<COMMON>Cardinal Flower</COMMON>
<BOTANICAL>Lobelia cardinalis</BOTANICAL>
<ZONE>2</ZONE>
<LIGHT>Shade</LIGHT>
<id>3</id>
<AVAILABILITY>022299</AVAILABILITY>
</PLANT>
</CATALOG>

Desired output:

<breakfast_menu>
<food>
<name>Belgian Waffles</name>
<id>5</id>
<description>Two of our famous Belgian Waffles with plenty of real maple syrup</description>
<calories>650</calories>
<COMMON>Snakeroot</COMMON>
<BOTANICAL>Cimicifuga</BOTANICAL>
<ZONE>Annual</ZONE>
<LIGHT>Shade</LIGHT>
<AVAILABILITY>071199</AVAILABILITY>
</food>
<food>
<name>Strawberry Belgian Waffles</name>
<id>7</id>
<description>Light Belgian waffles covered with strawberries and whipped cream</description>
<calories>900</calories>
<COMMON>California Poppy</COMMON>
<BOTANICAL>Eschscholzia californica</BOTANICAL>
<ZONE>Annual</ZONE>
<LIGHT>Sun</LIGHT>
<AVAILABILITY>032799</AVAILABILITY>
</food>
<food>
<name>Berry-Berry Belgian Waffles</name>
<id>8</id>
<description>Light Belgian waffles covered with an assortment of fresh berries and whipped cream</description>
<calories>900</calories>
<COMMON>Shooting Star</COMMON>
<BOTANICAL>Dodecatheon</BOTANICAL>
<ZONE>Annual</ZONE>
<LIGHT>Mostly Shady</LIGHT>
<AVAILABILITY>051399</AVAILABILITY>
</food>
<food>
<name>French Toast</name>
<id>4</id>
<description>Thick slices made from our homemade sourdough bread</description>
<calories>600</calories>
<COMMON>Greek Valerian</COMMON>
<BOTANICAL>Polemonium caeruleum</BOTANICAL>
<ZONE>Annual</ZONE>
<LIGHT>Shade</LIGHT>
<AVAILABILITY>071499</AVAILABILITY>
</food>
<food>
<name>Homestyle Breakfast</name>
<id>6</id>
<description>Two eggs, bacon or sausage, toast, and our ever-popular hash browns</description>
<calories>950</calories>
<COMMON>Primrose</COMMON>
<BOTANICAL>Oenothera</BOTANICAL>
<ZONE>3 - 5</ZONE>
<LIGHT>Sunny</LIGHT>
<AVAILABILITY>013099</AVAILABILITY>
</food>
</breakfast_menu>

my approach part of it to give you an idea:

 <?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"
    expand-text="yes"
    version="3.0">
    
  <xsl:param name="doc-A" select="doc('fileB.xml')"/>
  

  <xsl:mode on-no-match="shallow-copy"/>

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

  <xsl:template match="/*">
    <xsl:copy>
        <xsl:merge>
            <xsl:merge-source sort-before-merge="yes" for-each-item="." select="food">
                <xsl:merge-key select="id"/>
            </xsl:merge-source>
            <xsl:merge-source sort-before-merge="yes" for-each-item="$doc-A" select="//PLANT">
                <xsl:merge-key select="id"/>
            </xsl:merge-source>
            <xsl:merge-action>
  <xsl:copy>
   <xsl:copy-of select="current-merge-group('A')/*, current-merge-group('B')/(* except id)"/>
  </xsl:copy>
</xsl:merge-action>
        </xsl:merge>
    </xsl:copy>
  </xsl:template>
  
      <xsl:param name="merge-data"/>
      <xsl:copy>{.}{$merge-data}</xsl:copy>
  </xsl:template>
  
      <xsl:param name="merge-data"/>
      <xsl:next-match/>
  </xsl:template>
  
</xsl:stylesheet>

How about showing your attempt with xsl:merge so that we can fix it? There is a function current-merge-group() you should be able to use e.g. ``. — Martin Honnen, Jun 29 '22 at 11:10
Also most `id`s seem to be positive integers but one element has the id `$5`. Is that a typo? `xsl:merge` needs sorted items on the merge key or presort them so you need to clarify which values you have and which sort approach (numeric, alphanumeric) you want. — Martin Honnen, Jun 29 '22 at 11:23
$5 was a typo, sorting i guess needs to be done on the id element, since it is always numeric, i guess the numeric sort approach would be good. — mayela, Jun 29 '22 at 11:57
i added my approach, i would like your input on how to implement — mayela, Jun 29 '22 at 12:01

Martin Honnen · Accepted Answer · 2022-07-01T09:52:26.037

I would use e.g.

<xsl:merge-action>
  <xsl:copy>
   <xsl:copy-of select="current-merge-group('A')/*, current-merge-group('B')/(* except id)"/>
  </xsl:copy>
</xsl:merge-action>

This obviously assumes you are naming your merge sources, which you had done initially in your code, you edited that out after my answer, go figure.

Anyway, it seems you also want to ignore data from the second file so the merge action needs to check for a merge source from A:

<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:output indent="yes"/>
  
  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="/*">
    <xsl:copy>
        <xsl:merge>
            <xsl:merge-source name="A" sort-before-merge="yes" for-each-item="." select="food">
                <xsl:merge-key select="id"/>
            </xsl:merge-source>
            <xsl:merge-source name="B" sort-before-merge="yes" for-each-item="$doc-A" select="//PLANT">
                <xsl:merge-key select="id"/>
            </xsl:merge-source>
            <xsl:merge-action>
              <xsl:if test="current-merge-group('A')">
                 <xsl:copy>
                  <xsl:copy-of select="current-merge-group('A')/*, current-merge-group('B')/(* except id)"/>
                </xsl:copy>               
              </xsl:if>
            </xsl:merge-action>
      </xsl:merge>
    </xsl:copy>
  </xsl:template>
  
  <xsl:param name="doc-A" select="doc('fileB.xml')"/>

</xsl:stylesheet>

In the end, given that xsl:merge needs to order input sources to work and given that you desired output doesn't seem to be ordered based on the id, I would think that a key based solution might work better:

<xsl:param name="ref" match="PLANT" use="id"/>

and then <xsl:mode on-no-match="shallow-copy"/> plus

<xsl:template match="food[key('ref', id, doc('fileB.xml'))]">
  <xsl:copy>
    <xsl:apply-templates select="*, key('ref', id, doc('fileB.xml'))/(* except id)"/>
  </xsl:copy>
</xsl:template>

could you please add a full working example in your answer, please find in my question the code i try to solve the task, i am having issues to make it work, thank you — mayela, Jun 30 '22 at 08:29
@mayela, see whether the edit helps, but it doesn't make sense to name merge sources in your original attempt and then to remove those names once you got a suggestion based on those names. — Martin Honnen, Jun 30 '22 at 09:20
the snippet above works pretty ok. My problem is that my data in fileA have a few records that have already in them values stored in element say COMMON and AVAILABILITY. So the merge logic should become, add the elements, only if they do not already exist in the source record we need to add the elements from fileB. If you think this should be a new separate question, please let me know. Thank you — mayela, Jul 03 '22 at 10:39
and if you had the time, for completeness of the answer, could you please show how the snippet should become with the a key based solution? — mayela, Jul 03 '22 at 14:48
@mayela, consider to put the new requirement into a new question, explain exactly which elements and which data you want to merge, it is not clear to me whether the existence of e.g. `COMMON` in the primary input means you don't want any `COMMON` from the second document or whether that depends on them having the same value. — Martin Honnen, Jul 03 '22 at 15:00

merge two xml files xslt 3 based on the value of common id element

1 Answers1