Solving Problems by Querying XML - 9.5 Joins

Problem

You want to relate elements in a document to other elements in the same or different document.

Solution

A join is the process of considering all pairs of element as being related (i.e., a Cartesian product) and keeping only those pairs that meet the join relationship (usually equality).

To demonstrate, I have adapted the supplier parts database found in Date’s An Introduction to Database Systems (Addison Wesley, 1986) to XML:

  <database>
    <suppliers>
      <supplier id="S1" name="Smith" status="20" city="London"/>
      <supplier id="S2" name="Jones" status="10" city="Paris"/>
      <supplier id="S3" name="Blake" status="30" city="Paris"/>
      <supplier id="S4" name="Clark" status="20" city="London"/>
      <supplier id="S5" name="Adams" status="30" city="Athens"/>
    </suppliers>
    <parts>
      <part id="P1" name="Nut" color="Red" weight="12" city="London"/>
      <part id="P2" name="Bult" color="Green" weight="17" city="Paris"/>
      <part id="P3" name="Screw" color="Blue" weight="17" city="Rome"/>
      <part id="P4" name="Screw" color="Red" weight="14" city="London"/>
      <part id="P5" name="Cam" color="Blue" weight="12" city="Paris"/>
      <part id="P6" name="Cog" color="Red" weight="19" city="London"/>
    </parts>
    <inventory>
      <invrec sid="S1" pid="P1" qty="300"/>
      <invrec sid="S1" pid="P2" qty="200"/>
      <invrec sid="S1" pid="P3" qty="400"/>
      <invrec sid="S1" pid="P4" qty="200"/>
      <invrec sid="S1" pid="P5" qty="100"/>
      <invrec sid="S1" pid="P6" qty="100"/>
      <invrec sid="S2" pid="P1" qty="300"/>
      <invrec sid="S2" pid="P2" qty="400"/>
      <invrec sid="S3" pid="P2" qty="200"/>
      <invrec sid="S4" pid="P2" qty="200"/>
      <invrec sid="S4" pid="P4" qty="300"/>
      <invrec sid="S4" pid="P5" qty="400"/>
    </inventory>
  </database>

The join to be performed will answer the question, “Which suppliers and parts are in the same city (co-located)?”

You can use two basic techniques to approach this problem in XSLT. The first uses nestedfor-eachloops:

  <xsl:template match="/">
    <result>
      <xsl:for-each select="database/suppliers/*">
        <xsl:variable name="supplier" select="."/>
        <xsl:for-each select="/database/parts/*[@city=current()/@city]">
        <colocated>
          <xsl:copy-of select="$supplier"/>
          <xsl:copy-of select="."/>
        </colocated>
        </xsl:for-each>
      </xsl:for-each>
    </result>
  </xsl:template>

The second approach usesapply-templates:

  <xsl:template match="/">
    <result>
      <xsl:apply-templates select="database/suppliers/supplier" />
    </result>
  </xsl:template>

  <xsl:template match="supplier">
    <xsl:apply-templates select="/database/parts/part[@city = current()/@city]">
      <xsl:with-param name="supplier" select="." />
    </xsl:apply-templates>
  </xsl:template>

  <xsl:template match="part">
    <xsl:param name="supplier" select="/.." />
    <colocated>
      <xsl:copy-of select="$supplier" />
      <xsl:copy-of select="." />
    </colocated>
  </xsl:template>

Next: Joins With Many Members >>
 

More XML Tutorials Articles
More By O'Reilly Media