XSLT- Copy node from other XML file, based on matching node value

Question

Hello i have two different xml and i want to merge them copying one node from target xml, based on matching source and target xml node value

first xml like this:

<PRODUCTS>
  <PRODUCT>
    <NAME>PRODUCT 1</NAME>
    <MAINCATID>38</MAINCATID>
    <SUBCATID>39</SUBCATID>
  </PRODUCT>
</PRODUCTS>

Second XML is like this

 <CATEGORIES>
  <MAINCATEGORY>
   <MAINCATID>38</MAINCATID>
   <MAINCATNAME>CATEGORY 1</MAINCATNAME>
  </MAINCATEGORY>
  <MAINCATEGORY>
   <MAINCATID>37</MAINCATID>
   <MAINCATNAME>CATEGORY 2</MAINCATNAME>
  </MAINCATEGORY>
  <SUBCATEGORY>
   <SUBCATID>39</SUBCATID>
   <SUBCATNAME>SUB CATEGORY 1</SUBCATNAME>
  </SUBCATEGORY>
  <SUBCATEGORY>
   <SUBCATID>40</SUBCATID>
   <SUBCATNAME>SUB CATEGORY 2</SUBCATNAME>
  </SUBCATEGORY>
</CATEGORIES>

my result xml should be like

<PRODUCTS>
  <PRODUCT>
    <NAME>PRODUCT 1</NAME>
    <MAINCATID>38</MAINCATID>
    <MAINCATNAME>CATEGORY 1</MAINCATNAME>
    <SUBCATID>39</SUBCATID>
    <SUBCATNAME>SUB CATEGORY 1</SUBCATNAME>
  </PRODUCT>
</PRODUCTS>

there are many nodes like this. I want to get category names from second xml based on main and subcagetory ids.

Could you please help about this XSLT transformation?

Answer 1

Using a key to lookup data from another document is a bit awkward in XSLT 1.0, but it is still the best method, IMHO, both in terms of performance and code clarity. Try:

XSLT 1.0

<xsl:stylesheet version="1.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:variable name="lookup-source" select="document('file2.xml')" />

<xsl:key name="MAINCATID" match="MAINCATNAME" use="../MAINCATID" />
<xsl:key name="SUBCATID" match="SUBCATNAME" use="../SUBCATID" />

<!-- identity transform -->
<xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="MAINCATID | SUBCATID">
    <xsl:copy-of select="."/>
    <xsl:variable name="key-name" select="local-name()"/>
    <xsl:variable name="key-value" select="."/>
    <!-- switch context to the other file for the actual lookup -->
    <xsl:for-each select="$lookup-source">
        <xsl:copy-of select="key($key-name, $key-value)" />
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

We are assuming here that your "first xml" document is the one that's being processed.

Answer 2

The key to using more than one XML document in XSLT is document() Here is a quick and dirty example that demonstrates bringing in categories.xml while processing templates against products.xml (edited based on @michael.hor257k's comment and some tweaking):

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


<xsl:variable name="categories" select="document('categories.xml')" />


<xsl:template match="MAINCATID">
    <xsl:variable name="maincatid" select="." />
    <MAINCATID><xsl:value-of select="$maincatid" /></MAINCATID>
    <MAINCATNAME><xsl:value-of select="$categories//MAINCATEGORY[MAINCATID=$maincatid]/MAINCATNAME" /></MAINCATNAME>
</xsl:template>

<xsl:template match="SUBCATID">
    <xsl:variable name="subcatid" select="." />
    <SUBCATID><xsl:value-of select="$subcatid" /></SUBCATID>
    <SUBCATNAME><xsl:value-of select="$categories//SUBCATEGORY[SUBCATID=$subcatid]/SUBCATNAME" /></SUBCATNAME>
</xsl:template>


<xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()" />
    </xsl:copy>
</xsl:template>


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

</xsl:stylesheet>

In this example, <xsl:variable name="categories" select="document('categories.xml') /> brings in the categories. Now other templates can dip into your second XML by using the $categories variable. This is demonstrated with

<xsl:value-of select="$categories//MAINCATEGORY[MAINCATID=$maincatid]/MAINCATNAME" />

Running your first XML through this example (with your second XML saved as a file named 'categories.xml') produced the following result:

<PRODUCTS>
  <PRODUCT>
    <NAME>PRODUCT 1</NAME>
    <MAINCATID>38</MAINCATID>
    <MAINCATNAME>CATEGORY 1</MAINCATNAME>
    <SUBCATID>39</SUBCATID>
    <SUBCATNAME>SUB CATEGORY 1</SUBCATNAME>
  </PRODUCT>
<PRODUCTS>