Merge two xml files on approximate dates and write out tab-delimited file

I'm a newbie to XML and I could use some help, big time.  A vendor that I am dealing with deviated from the interface specs that I had coded to, neglected to notify me of the changes and now I am under the gun to modify my
processing to work ASAP!!!.

Basically, I need to merge two xml input files on an approximate date/timestamp.  Date/timestamps are in ascending order in both input files.

Here is a sample of gps.xml input file:

<?xml version="1.0"?>
<root>
 <header>
  <driver_id></driver_id>
  <vehicle_id>101</vehicle_id>
  <duty_shift_id></duty_shift_id>
  <route_id></route_id>
  <cid_terminal_id>5146</cid_terminal_id>
 </header>
 <header>
  <driver_id></driver_id>
  <vehicle_id>101</vehicle_id>
  <duty_shift_id></duty_shift_id>
  <route_id></route_id>
  <cid_terminal_id>5146</cid_terminal_id>
 </header>
 <record>
  <longitude>-105.111111</longitude>
  <latitude>39.111111</latitude>
  <date_time>2003/12/10.10:55</date_time>
 </record>
 <record>
  <longitude>-106.222222</longitude>
  <latitude>38.555555</latitude>
  <date_time>2003/12/10.11:05</date_time>
 </record>
 <header>
  <driver_id></driver_id>
  <vehicle_id>101</vehicle_id>
  <duty_shift_id></duty_shift_id>
  <route_id></route_id>
  <cid_terminal_id>5146</cid_terminal_id>
 </header>
 <record>
  <longitude>-107.333333</longitude>
  <latitude>37.444444</latitude>
  <date_time>2003/12/10.11:15</date_time>
 </record>
</root>

The gps.xml file can have <header>'s with no <record>'s but the <header>'s are extraneous.  I only care about merging the <record>'s in the gps.xml file to the tran.xml file.

The <longitude> and <latitude> tags in each gps.xml <record> identify a light rail transit stop location.

Here is a sample of the tran.xml input file:

<?xml version="1.0"?>
<root>
 <header>
  <driver_id></driver_id>
  <vehicle_id>101</vehicle_id>
  <duty_shift_id></duty_shift_id>
  <route_id>C</route_id>
  <cid_terminal_id>5141</cid_terminal_id>
 </header>
 <header>
  <driver_id></driver_id>
  <vehicle_id>101</vehicle_id>
  <duty_shift_id></duty_shift_id>
  <route_id>C</route_id>
  <cid_terminal_id>5141</cid_terminal_id>
 </header>
 <record>
  <longitude></longitude>
  <latitude></latitude>
  <date_time>2003/12/10.11:00</date_time>
  <tag_id>1111111111111111</tag_id>
  <stop_location_id></stop_location_id>
  <fare_type_cd>E</fare_type_cd>
  <blacklist_cd></blacklist_cd>
 </record>
 <header>
  <driver_id></driver_id>
  <vehicle_id>101</vehicle_id>
  <duty_shift_id></duty_shift_id>
  <route_id>C</route_id>
  <cid_terminal_id>5141</cid_terminal_id>
 </header>
 <record>
  <longitude></longitude>
  <latitude></latitude>
  <date_time>2003/12/10.11:10</date_time>
  <tag_id>2222222222222222</tag_id>
  <stop_location_id></stop_location_id>
  <fare_type_cd>E</fare_type_cd>
  <blacklist_cd></blacklist_cd>
 </record>
 <record>
  <longitude></longitude>
  <latitude></latitude>
  <date_time>2003/12/10.11:20</date_time>
  <tag_id>3333333333333333</tag_id>
  <stop_location_id></stop_location_id>
  <fare_type_cd>E</fare_type_cd>
  <blacklist_cd></blacklist_cd>
 </record>
 <record>
  <longitude></longitude>
  <latitude></latitude>
  <date_time>2003/12/10.11:20</date_time>
  <tag_id>4444444444444444</tag_id>
  <stop_location_id></stop_location_id>
  <fare_type_cd>E</fare_type_cd>
  <blacklist_cd></blacklist_cd>
 </record>
</root>

The tran.xml file can have multiple <header>'s and multiple <record>'s within/following each <header>.  It can also have <header>'s with no following <record>'s.

Each <date_time> tag value in the tran.xml file needs to be matched to the previous or equal <date_time> tag value in the gps.xml file with the longitude and latitude merged with the tran.xml data, retaining the tran.xml
<date_time> tag value and written to a tab-delimited output file as follows (tabs are represented by ?, four records):

?101??C?5141?-105.111111?39.111111???2003/12/10.11:00?1111111111111111??E??
?101??C?5141?-106.222222?38.555555???2003/12/10.11:10?2222222222222222??E??
?101??C?5141?-107.333333?37.444444???2003/12/10.11:20?3333333333333333??E??
?101??C?5141?-107.333333?37.444444???2003/12/10.11:20?4444444444444444??E??

Here is the xsl script (merge_lrv_gps_and_trans_to_tab_delim.xsl) that worked according to the original specs (matching on exact date/timestamps in the two input files):

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

 <xsl:output method="text"/>

 <xsl:param name="lrv_gps_file"/>

 <xsl:variable name="delim" select="'&#x9;'"/>   <!-- tab     -->
 <xsl:variable name="nl"    select="'&#xA;'"/>   <!-- newline -->
 <xsl:variable name="head">
    <xsl:for-each select="/root/header/*">
        <xsl:value-of select="concat(., $delim)"/>
    </xsl:for-each>
</xsl:variable>

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

 <xsl:template match="record">
     <!--note: if gps.xml is in a different directory, you will need to use the relative path or URL-->
     <!--  select="document('gps.xml')/root/record[date_time = current()/date_time]"/>  -->
     <xsl:variable name="gps"
        select="document($lrv_gps_file)/root/record[date_time = current()/date_time]"/>
             <xsl:variable name="vHeader">
                 <xsl:for-each select="preceding-sibling::header[1]/*">
                     <xsl:value-of select="concat(., $delim)"/>
                 </xsl:for-each>
             </xsl:variable>
             <xsl:value-of select="$vHeader"/>
     <xsl:value-of select="concat($gps/longitude, $delim, $gps/latitude, $delim)"/>
     <xsl:for-each select="*">
     <xsl:value-of select="concat(., $delim)"/>
     </xsl:for-each>
     <xsl:value-of select="$nl"/>
 </xsl:template>

</xsl:stylesheet>

<!--  java com.icl.saxon.StyleSheet -o tab_delim.dat tran.xml  merge_lrv_gps_and_trans_to_tab_delim.xsl  lrv_gps_file=gps.xml  -->


Can anyone help with a solution to this?

TIA

Start Free Trial