[xsl] xml/xslt 'merge' query

Subject: [xsl] xml/xslt 'merge' query
From: "Laurie Knight" <arcn70@xxxxxxxxxxxxx>
Date: Wed, 26 May 2004 17:18:48 +0100
Hi,

I have some XML something like (this is a subset of the full data file):-

<?xml version='1.0' encoding='UTF-8'?>
<stockdata>
  <sql_stocklist>
    <sql_stock>
      <stockcode>BARC</stockcode>
      <owned>1000</owned>
      <paid>2000</paid>
      <commission>1200</commission>
    </sql_stock>
    <sql_stock>
      <stockcode>ANL</stockcode>
      <owned>1000</owned>
      <paid>450</paid>
      <commission>1200</commission>
    </sql_stock>
  </sql_stocklist>
  <lmk_stocklist>
    <lmk_stock>
      <lmk_stockcode>BARC</lmk_stockcode>
      <lmk_direction>+</lmk_direction>
      <lmk_changepercent>1.2</lmk_changepercent>
      <lmk_change>6</lmk_change>
      <lmk_midprice>504</lmk_midprice>
      <lmk_stockname>BARCLAYS</lmk_stockname>
      <lmk_volume>23451404</lmk_volume>
      <lmk_trades>2722</lmk_trades>
      <lmk_dirstat>up</lmk_dirstat>
    </lmk_stock>
    <lmk_stock>
      <lmk_stockcode>ANL</lmk_stockcode>
      <lmk_direction>+</lmk_direction>
      <lmk_changepercent>0.28</lmk_changepercent>
      <lmk_change>1.25</lmk_change>
      <lmk_midprice>451</lmk_midprice>
      <lmk_stockname>ABBEY NAT.</lmk_stockname>
      <lmk_volume>4119329</lmk_volume>
      <lmk_trades>1109</lmk_trades>
      <lmk_dirstat>up</lmk_dirstat>
    </lmk_stock>
  </lmk_stocklist>
</stockdata>

Notice that /stockdata/sql_stocklist/sql_stock[1]/stockcode =
/stockdata/lmk_stocklist/lmk_stock[1]/lmk_stockcode and the same with
the second item in each set. This continues down the file, the n'th
sql_stock/stockcode always equals the n'th lmk_stock stockcode.. 

However I would ideally like to remove duplicates from the
lmk_stocklist section. (meaning that I would have to cycle through the
sql_stocks one at a time and then match the first lmk_stock where its
stockcode = the current sql_stock/stockcode...

I need to transform this into:-

<?xml version='1.0' encoding='UTF-8'?>
<stockdata>
  <sql_stocklist>
    <sql_stock>
      <stockcode>BARC</stockcode>
      <owned>1000</owned>
      <paid>2000</paid>
      <commission>1200</commission>
      <lmk_stockcode>BARC</lmk_stockcode>
      <lmk_direction>+</lmk_direction>
      <lmk_changepercent>1.2</lmk_changepercent>
      <lmk_change>6</lmk_change>
      <lmk_midprice>504</lmk_midprice>
      <lmk_stockname>BARCLAYS</lmk_stockname>
      <lmk_volume>23451404</lmk_volume>
      <lmk_trades>2722</lmk_trades>
      <lmk_dirstat>up</lmk_dirstat>
    </sql_stock>
    <sql_stock>
      <stockcode>ANL</stockcode>
      <owned>1000</owned>
      <paid>450</paid>
      <commission>1200</commission>
      <lmk_stockcode>ANL</lmk_stockcode>
      <lmk_direction>+</lmk_direction>
      <lmk_changepercent>0.28</lmk_changepercent>
      <lmk_change>1.25</lmk_change>
      <lmk_midprice>451</lmk_midprice>
      <lmk_stockname>ABBEY NAT.</lmk_stockname>
      <lmk_volume>4119329</lmk_volume>
      <lmk_trades>1109</lmk_trades>
      <lmk_dirstat>up</lmk_dirstat>
    </sql_stock>
  </sql_stocklist>
</stockdata>

"Merging" the sql_stock with the lmk_stock chunk for each one...

I've tried doing this with XSLT but I'm new to this and I can't get my
head around it. I think I need to cycle through each sql_stock and
then try to match the lmk_stock which goes with it, and I've tried to
do this but I can't get it to work. I have the following XSLT so far,
which attempts to output a merged HTML table. I since realised that I
need to merge the data into an intermediate XML structure as stated
above. But if I use this it transforms the SQL half of the data and I
cannot get it to combine in the LMK half of the data...

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

<xsl:template match="stockdata">
    <table border="1">
        <tr bgcolor="#F4F4F4">
            <th class="txt">Code</th>
            <th class="txt">Shares Owned</th>
            <th class="txt">Price Paid</th>
            <th class="txt">Commission Paid</th>
            <th class="x">debug stockcode</th>
            <th class="txt">stockname</th>
            <th class="txt">midprice</th>
            <th class="txt">trades</th>
            <th class="txt">volume</th>
        </tr>
        <xsl:apply-templates select="sql_stocklist" />
    </table>
</xsl:template>

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

<xsl:template match="sql_stock">
    <tr bgcolor="#F4F4F4">
        <td class="txt"><xsl:value-of select="stockcode" /></td>
        <td class="txt"><xsl:value-of select="owned" /></td>
        <td class="txt"><xsl:value-of select="paid" /></td>
        <td class="txt"><xsl:value-of select="commission" /></td>
        <xsl:call-template name="lmk">
            <xsl:with-param name="sqlstockcode" select="stockcode" />
        </xsl:call-template>
    </tr>
</xsl:template>

<xsl:template name="lmk" match="/stockdate/lmk_stocklist/lmk_stock">
    <xsl:param name="sqlstockcode" />
    <td class="txt"><xsl:value-of select="$sqlstockcode" /></td>
    <xsl:if test="$sqlstockcode = lmk_stockcode">
        <td class="txt"><xsl:value-of select="lmk_stockname" /></td>
        <td class="txt"><xsl:value-of select="lmk_midprice" /></td>
        <td class="txt"><xsl:value-of select="lmk_trades" /></td>
        <td class="txt"><xsl:value-of select="lmk_volume" /></td>
    </xsl:if>
</xsl:template>
</xsl:stylesheet>

I can easily change this to output XML in the required format but the
same problem will occur, I'll only get half of the output. Because of
the XSL:IF TEST bit in the LMK Template not working.. If I can solve
this little problem I can sort out the rest of it!

I suspect I could be doing the XSLT completely wrong but I have no
idea how to go about this and can find no examples of something
similar... So any pointers would be very much appreciated!

TIA

Laurie
--

Current Thread