Converting XML table to XML treeview using XSLT key and previous-sibling

This 开发者_运维问答is my first attempt at XSLT and first post on SO. I need to transform table/row oriented XML to tree oriented XML with unique branches and leafs. I am using XSLT 1.1 and IE8 debugging in VS2008. I have examined XSLT 2nd Edition (M. Kay) and Tennison's article in the Muenchian Grouping Technique which I have tried to incorporate. I have tried to incorporate items related to possible problem areas such as rebuilding the sibling relationships with another variable in order to use previous-sibling to get uniqueness but my previous-sibling is always null and I never see Route 2 or zip 23456. I have examined many postings related to previous-sibling but am unable to get the output I expect. Sorry about the long post but I wanted to make it as clear as possible.

Thanks, Al

My input is:

<?xml version="1.0"?><root><dataset><CITY_DATA>
<ROW count='1'><CITY>BOSTON</CITY><ZIP>12345</ZIP><ROUTE_NM>Route 1</ROUTE_NM></ROW>
<ROW count='2'><CITY>BOSTON</CITY><ZIP>12345</ZIP><ROUTE_NM>Route 1</ROUTE_NM></ROW>
<ROW count='3'><CITY>BOSTON</CITY><ZIP>34567</ZIP><ROUTE_NM>Route 2</ROUTE_NM></ROW>
<ROW count='4'><CITY>LAKEVILLE</CITY><ZIP>01234</ZIP><ROUTE_NM>Route 1</ROUTE_NM></ROW>
<ROW count='5'><CITY>LAKEVILLE</CITY><ZIP>01234</ZIP><ROUTE_NM>Route 1</ROUTE_NM></ROW>
<ROW count='6'><CITY>LAKEVILLE</CITY><ZIP>23456</ZIP><ROUTE_NM>Route 1</ROUTE_NM></ROW>
</CITY_DATA></dataset></root>

My XSL is:

<xsl:transform version="1.1" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:msxsl="urn:schemas-microsoft-com:xslt">
<xsl:output method="xml"  indent="yes" version="1.0"/>
<xsl:key name="CITY" match="ROW" use="CITY" />
<xsl:template match="CITY_DATA">
  <FIRST_BRANCH>
      <xsl:for-each select="ROW[count(. | key('CITY', CITY)[1]) = 1]">
          <xsl:sort select="CITY" />
      <CITY>
      <xsl:value-of select="CITY" />
        <xsl:variable name="routes-list">
            <xsl:for-each select="key('CITY',CITY)">
                <xsl:copy-of select="."/>
            </xsl:for-each>
        </xsl:variable>
        <xsl:for-each select="msxsl:node-set($routes-list)//ROUTE_NM">
          <PRE_ROUTE_NM>
            <xsl:value-of select="preceding-sibling::ROUTE_NM"/> 
          </PRE_ROUTE_NM>
          <VAL_ROUTE_NM>
            <xsl:value-of select="//ROUTE_NM" />
          </VAL_ROUTE_NM>
          <xsl:if test="//ROUTE_NM[not(preceding-sibling::ROUTE_NM[1])]">
            <ROUTE_NM>
            <xsl:value-of select="//ROUTE_NM" />
              <xsl:for-each select="msxsl:node-set($routes-list)//ZIP">
                <xsl:if test="//ZIP[not(preceding-sibling::ZIP[1])]">
                  <ZIP>
                    <xsl:value-of select="//ZIP" />
                  </ZIP>
                </xsl:if>
              </xsl:for-each>
            </ROUTE_NM>
         </xsl:if>
        </xsl:for-each>
      </CITY>
</xsl:for-each>
    </FIRST_BRANCH>
  </xsl:template>
</xsl:transform>

My Bad Output is:

****CURRENT WRONG OUTPUT ****************

<?xml version="1.0" encoding="utf-8"?>
<FIRST_BRANCH xmlns:msxsl="urn:schemas-microsoft-com:xslt">
<CITY>BOSTON
<PRE_ROUTE_NM></PRE_ROUTE_NM>
<VAL_ROUTE_NM>Route 1</VAL_ROUTE_NM>
<ROUTE_NM>Route 1
    <ZIP>12345</ZIP>
    <ZIP>12345</ZIP>
    <ZIP>12345</ZIP>
</ROUTE_NM>
<PRE_ROUTE_NM></PRE_ROUTE_NM>
<VAL_ROUTE_NM>Route 1</VAL_ROUTE_NM>
<ROUTE_NM>Route 1
    <ZIP>12345</ZIP>
    <ZIP>12345</ZIP>
    <ZIP>12345</ZIP>
</ROUTE_NM>
<PRE_ROUTE_NM></PRE_ROUTE_NM>
<VAL_ROUTE_NM>Route 1</VAL_ROUTE_NM>
<ROUTE_NM>Route 1
    <ZIP>12345</ZIP>
    <ZIP>12345</ZIP>
    <ZIP>12345</ZIP>
</ROUTE_NM>
</CITY>
<CITY>LAKEVILLE
<PRE_ROUTE_NM></PRE_ROUTE_NM>
<VAL_ROUTE_NM>Route 1</VAL_ROUTE_NM>
<ROUTE_NM>Route 1
    <ZIP>01234</ZIP>
    <ZIP>01234</ZIP>
    <ZIP>01234</ZIP>
</ROUTE_NM>
<PRE_ROUTE_NM></PRE_ROUTE_NM>
<VAL_ROUTE_NM>Route 1</VAL_ROUTE_NM>
<ROUTE_NM>Route 1
    <ZIP>01234</ZIP>
    <ZIP>01234</ZIP>
    <ZIP>01234</ZIP>
</ROUTE_NM>
<PRE_ROUTE_NM></PRE_ROUTE_NM>
<VAL_ROUTE_NM>Route 1</VAL_ROUTE_NM>
<ROUTE_NM>Route 1
    <ZIP>01234</ZIP>
    <ZIP>01234</ZIP>
    <ZIP>01234</ZIP>
</ROUTE_NM>
</CITY>
</FIRST_BRANCH>

My Expected Output is:

***BELOW IS EXPECTED OUTPUT**********************

<?xml version="1.0" encoding="utf-8"?>
<FIRST_BRANCH xmlns:msxsl="urn:schemas-microsoft-com:xslt">
<CITY>BOSTON
<PRE_ROUTE_NM></PRE_ROUTE_NM>
<VAL_ROUTE_NM>Route 1</VAL_ROUTE_NM>
<ROUTE_NM>Route 1
    <ZIP>12345</ZIP>
</ROUTE_NM>
<PRE_ROUTE_NM>Route 1</PRE_ROUTE_NM>
<VAL_ROUTE_NM>Route 1</VAL_ROUTE_NM>
<PRE_ROUTE_NM>Route 1</PRE_ROUTE_NM>
<VAL_ROUTE_NM>Route 2</VAL_ROUTE_NM>
<ROUTE_NM>Route 2
    <ZIP>34567</ZIP>
</ROUTE_NM>
</CITY>
<CITY>LAKEVILLE
<PRE_ROUTE_NM></PRE_ROUTE_NM>
<VAL_ROUTE_NM>Route 1</VAL_ROUTE_NM>
<ROUTE_NM>Route 1
    <ZIP>01234</ZIP>
    <ZIP>23456</ZIP>
</ROUTE_NM>
<PRE_ROUTE_NM>Route 1</PRE_ROUTE_NM>
<VAL_ROUTE_NM>Route 1</VAL_ROUTE_NM>
<PRE_ROUTE_NM>Route 1</PRE_ROUTE_NM>
<VAL_ROUTE_NM>Route 1</VAL_ROUTE_NM>
</CITY>
</FIRST_BRANCH>