Hello again,
As per John's suggestion I am now using the SQL Extension
instead of my own custom XMLReader, and it is nifty. However, I am
running into an interesting issue that I have a feeling would be more
easily solved if I had more experience with XSLT. Maybe you guys can
give me a hint.
I have a table that contains data with a layout similar to the
following:
Name Flag1 Flag2 Special
Dan Y N 123
Dan Y N 456
Dan Y N 789
Bob N N 999
Bob N N 888
Linda N Y 777
Linda N Y 666
Nancy Y Y 555
I need to produce an XML document based on this data similar to the
following:
<UserInfo>
<UserList>
<User name="Dan">
<FlagList>
<Flag Type="1"/>
</FlagList>
<SpecialList>
<Special number="123"/>
<Special number="456"/>
<Special number="789"/>
<SpecialList>
</User>
<User name="Bob">
<FlagList/>
<SpecialList>
<Special number="999"/>
<Special number="888"/>
<SpecialList>
</User>
<User name="Linda">
<FlagList>
<Flag Type="2"/>
</FlagList>
<SpecialList>
<Special number="777"/>
<Special number="666"/>
<SpecialList>
</User>
<User name="Bob">
<FlagList>
<Flag Type="1"/>
<Flag Type="2"/>
</FlagList>
<SpecialList>
<Special number="555"/>
<SpecialList>
</User>
</UserList>
</UserInfo>
So, I need to conditionally add to the SpecialList for the current user
until the user name changes, then I need to switch and start a new User
node. I attempted something using recursion, and came close... here is
something similar to what I was doing:
<xsl:template match="/">
<!-- db setup stuff for SQL and handle connection error-->
<xsl:value-of select="sql:disableStreamingMode($db)" />
<UserInfo>
<UserList>
<!-- execute query to populate $table -->
<xsl:apply-templates select="$table/sql/row-set"/>
</UserList>
</UserInfo>
<!-- close db -->
</xsl:template>
<xsl:template match="row-set">
<xsl:apply-templates select="row"/>
</xsl:template>
<xsl:template match="row">
<User>
<xsl:attribute name="name"><xsl:value-of
select="col[1]"/></xsl:attribute>
<FlagList>
<xsl:if test="col[2] = 'Y'>
<Flag>
<xsl:attribute name="Type">1</xsl:attribute>
</Flag>
</xsl:if>
<xsl:if test="col[3] = 'Y'>
<Flag>
<xsl:attribute name="Type">2</xsl:attribute>
</Flag>
</xsl:if>
</FlagList>
<SpecialList>
<xsl:call-template name="SpecList">
<xsl:with-param name="nxtrow" select="self::node()"/>
</xsl:call-template>
</SpecialList>
</User>
</xsl:template>
<xsl:template name="SpecList">
<xsl:param name="nxtrow"/>
<xsl:param name="prevusr" select="'xxx'"/>
<xsl:if test="$nxtrow">
<xsl:if test="$prevusr = 'xxx' or $prevusr = string($nxtrow/col[1])">
<Special>
<xsl:attribute name="number">
<xsl:value-of select="$nxtrow/col[4]">
</xsl:attribute>
<Special>
<xsl:call-template name="SpecList">
<xsl:with-param name="nxtrow"
select="$nxtrow/following-sibling::node()"/>
<xsl:with-param name="prevusr" select="string($nxtrow/col[1])/>
</xs:call-template>
</xsl:if>
</xsl:if>
</xsl:template>
This produces something close... sort of... something like this:
<UserInfo>
<UserList>
<User name="Dan">
<FlagList>
<Flag Type="1"/>
</FlagList>
<SpecialList>
<Special number="123"/>
<Special number="456"/>
<Special number="789"/>
<SpecialList>
</User>
<User name="Dan">
<FlagList>
<Flag Type="1"/>
</FlagList>
<SpecialList>
<Special number="456"/>
<Special number="789"/>
<SpecialList>
</User>
<User name="Dan">
<FlagList>
<Flag Type="1"/>
</FlagList>
<SpecialList>
<Special number="789"/>
<SpecialList>
</User>
<User name="Bob">
<FlagList/>
<SpecialList>
<Special number="999"/>
<Special number="888"/>
<SpecialList>
</User>
<User name="Bob">
<FlagList/>
<SpecialList>
<Special number="888"/>
<SpecialList>
</User>
...
</UserList>
</UserInfo>
So, my recursion works... kinda... I just lose track of what row I
already processed when I bubble back up to the row-set match... so I
repeat my match on rows I already pulled the special number out of...
I know this email is a bit lengthy, but I am really not sure how to
handle something like this, but I can't imagine I am the first person
that has needed to do this, and I imagine it is a common problem. Anyone
have any pointers? I appreciate it!
-Dan Feather
