How to join two XML files using XSLT?

3
votes

i am trying to join two xml files which contains details about each other that can be linked, but i am unsure how to.

"movies.xml"

<movies>
    <movie>
      <title></title>
      <studio><studio>
      <date></date>
    </movie>
</movies>

"studios.xml"

<studios>
    <studio>
      <name></name>
      <founded></founded>
      <location></location>
    </studio>
</studios>

Output

<studios>
  <studio>
    <name></name>
    <founded></founded>
    <location></location>
    <movie>
        <name></name>
    </movie>
    <movie>
        <name></name>
    </movie>
  </studio>
</studios>

I have simplified the files but still stands. My idea was using studios as the base just get the value and insert, however when it comes to joining movie to the correct studio i am confused.

I currently attempt to loop through each movie until i find the same matching name, in movies it being studio and in studios it being name

But current attempts don't work, is this way possible?

Edits: There is one studio but it can have many movies, in studios there are several studio and in movies there are several movies. If a studio has more than one movie than it can be added as name so it is possible each studio has several movies elements .

What I am attempting to do is, get all the movies and add them to the correct studio.

Dummy data: Movies.xml

<movies>
<movie>
  <title>Lord of XSLT</title>
  <studio>Great XML<studio>
  <date>1-1-2014</date>
</movie>
<movie>
  <title>On the XML</title>
  <studio>XML2.0<studio>
  <date>5-1-2004</date>
</movie>
<movie>
  <title>On the XPath</title>
  <studio>Great XML<studio>
  <date>5-5-2013</date>
</movie>
</movies>

Studios.xml

<studios>
<studio>
  <name>Great XML</name>
  <founded>1-1-1999</founded>
  <location>USA</location>
</studio>
<studio>
  <name>XML2.0</name>
  <founded>9-9-1998</founded>
  <location>ENGLAND</location>
</studio>
</studios>

Output.xml

<studios>
<studio>
   <name>Great XML</name>
   <founded>1-1-1999</founded>
   <location>ENGLAND</location>
   <movie>
    <name>Lord of XSLT</name>
   </movie>
   <movie>
    <name>On the XPath</name>
   </movie>
 </studio>
  <studio>
   <name>XML2.0</name>
   <founded>9-9-1998</founded>
   <location>ENGLAND</location>
    <movie>
     <name>On the XML</name>
    </movie>
  </studio>
</studios>

Attempt

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

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

<xsl:template match="/studios">

<xsl:for-each select="studio">
<studio>
    <name><xsl:value-of select="name"/></name>
    <founded><xsl:value-of select="founded"/></founded>
    <location><xsl:value-of select="location"/></location>

    <xsl:for-each select="document('movies.xml')/movies/movie">
        <movies>

        </movies>
    </xsl:for-each>

</studio>

</xsl:for-each>     

</xsl:template>

</xsl:stylesheet>
2
xmlxsltxpath
You mentioned two files. Are there only two files? Does your example correctly represent your problem? Is each movie in a separate file, or do you have something like <movies>...<movie>...</movie></movies>? - helderdarocha
Can you have multiple movies per studio? Multiple studios per movie? If so, I suggest you edit your code and include an example which represents these problems. Take some time to elaborate them well. Show how the files are related. An example that matches, one that doesn't. Include text and/or attributes in the nodes, etc. - helderdarocha
You've simplified too much. Can you add some content to the examples? Also, you've stated you've tried some approaches, can you show us your closest attempt to improve on that? - user764357
Ok, so we have dummy data. Where is your code? - user764357
Okay added code, my join fail isn't there (i deleted it instead of commenting it out) but the idea was Loop through all movies and look at the name of the studio and compare that with the studio's name using current(). - user3651107

2 Answers

3
votes

You have the right idea. I'd recommend using xsl:apply-templates instead of xsl:for-each and have the identity transform handle most of the work for you. (Push instead of pull.)

Example...

Studios.xml

<studios>
    <studio>
        <name>Great XML</name>
        <founded>1-1-1999</founded>
        <location>USA</location>
    </studio>
    <studio>
        <name>XML2.0</name>
        <founded>9-9-1998</founded>
        <location>ENGLAND</location>
    </studio>
</studios>

Movies.xml (fixed to be well-formed)

<movies>
    <movie>
        <title>Lord of XSLT</title>
        <studio>Great XML</studio>
        <date>1-1-2014</date>
    </movie>
    <movie>
        <title>On the XML</title>
        <studio>XML2.0</studio>
        <date>5-1-2004</date>
    </movie>
    <movie>
        <title>On the XPath</title>
        <studio>Great XML</studio>
        <date>5-5-2013</date>
    </movie>
</movies>

XSLT 1.0

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

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

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

    <xsl:template match="studio">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()|$movies/*/movie[studio=current()/name]/title"/>
        </xsl:copy>        
    </xsl:template>

    <xsl:template match="movie/title">
        <movie>
            <name><xsl:value-of select="."/></name>
        </movie>
    </xsl:template>

</xsl:stylesheet>

Output

<studios>
   <studio>
      <name>Great XML</name>
      <founded>1-1-1999</founded>
      <location>USA</location>
      <movie>
         <name>Lord of XSLT</name>
      </movie>
      <movie>
         <name>On the XPath</name>
      </movie>
   </studio>
   <studio>
      <name>XML2.0</name>
      <founded>9-9-1998</founded>
      <location>ENGLAND</location>
      <movie>
         <name>On the XML</name>
      </movie>
   </studio>
</studios>
2
votes

Here's a relatively simple way to look at it:

<?xml version="1.0" encoding="UTF-8"?>
<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:template match="/studios">
    <studios>
        <xsl:for-each select="studio">
            <studio>
                <xsl:copy-of select="*"/>
                <movies>
                    <xsl:for-each select="document('movies.xml')/movies/movie[studio=current()/name]">
                        <xsl:copy-of select="."/>
                    </xsl:for-each>
                </movies>
            </studio>
        </xsl:for-each>     
    </studios>
</xsl:template>

</xsl:stylesheet>

Of course, you'll have to fix the movies.xml document (close the tags properly) first.