Join two xml files based on common id value

Question

I've seen a number of similar answers, but nothing quite like this simple case.

In the example below, how would I join the state name to the city to create the output?

Input file #1

<cities>
  <city>
    <state_id>100<state_id>
    <city_name>Los Angeles</city_name>
  </city>
  <city>
    <state_id>100<state_id>
    <city_name>San Francisco</city_name>
  </city>
  <city>
    <state_id>200<state_id>
    <city_name>Chicago</city_name>
  </city>
</cities>

Input file #2

<states>
  <state>
    <id>100</id>
    <name>California</name>
  </state>
  <state>
    <id>200</id>
    <name>Illinois</name>
  </state>
  <state>
    <id>300</id>
    <name>Texas</name>
  </state>
</states>

Output

<cities>
  <city>
    <state_id>100<state_id>
    <city_name>Los Angeles</city_name>
    <name>California</name>
  </city>
  <city>
    <state_id>100<state_id>
    <city_name>San Francisco</city_name>
    <name>California</name>
  </city>
  <city>
    <state_id>200<state_id>
    <city_name>Chicago</city_name>
    <name>Illinois</name>
  </city>
</cities>

Answer 1

Consider using the XSLT 1.0 document() function which allows relative path referencing across documents, pulling specific nodes. Apply the below XSLT on the City.xml which during transformation pulls data from States.xml, assuming both are located in same directory:

<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output version="1.0" encoding="UTF-8" indent="yes"  method="xml"/>
<xsl:strip-space elements="*"/>

  <xsl:template match="/cities">
    <xsl:copy>
      <xsl:apply-templates select="city"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="city">
    <xsl:copy>
      <xsl:variable name="statevar" select="state_id"/>
      <xsl:copy-of select="state_id|city_name"/>
      <xsl:copy-of select="document('States.xml')/states/state[id=$statevar]/name"/>
    </xsl:copy>
  </xsl:template>

</xsl:transform>