You are viewing a plain text version of this content. The canonical link for it is here.
Posted to j-users@xalan.apache.org by Dan Feather <DF...@jackhenry.com> on 2005/06/23 16:48:36 UTC
SQL Extension and conditional recursion...
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
Re: SQL Extension and conditional recursion...
Posted by Advent Information Systems <a-...@ihug.co.nz>.
At 9:48 -0500 23/06/05, Dan Feather wrote:
>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.
[snip]
>I attempted something using recursion, and came close... here is
>something similar to what I was doing:
>
[snip]
>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!
Assuming your data is small enough, you can use a for-each main loop
rather than an apply-templates one. (It won't work for large sets of
data, since for-each builds an entire nodelist at the start.)
Here's some code I used for collecting together all the product rows
with the same ProductId:
<xsl:for-each select="$table/sql/row-set/row">
<xsl:variable name="pos" select="position()"/>
<xsl:variable name="ProductId"
select="col[@column-name='ProductId']/text()" />
<!-- Ignore duplicate ProductIds in this outer loop -->
<xsl:if
test="$pos=1 or $table/sql/row-set/row[$pos - 1]/col
[@column-name='ProductId']/text() != $ProductId" >
<!-- For each non-duplicate Id, process all its rows together -->
<xsl:call-template name="ProcessProduct">
<xsl:with-param name="rows"
select="$table/sql/row-set/row[col
[@column-name='ProductId']/text() = $ProductId]" />
</xsl:call-template>
</xsl:if>
</xsl:for-each>
HTH.
--
AA III SSS | Advent Information Systems Ltd.
A A I S |
AAAA I SS | Richard Grossman, Systems Architect
A A I S |
A A III SSS | e-mail: <A-...@ihug.co.nz>