I have a database query that pulls multiple candidates under one race and places them into an XML file. My problem I have ran into, is that some races have more than 2 candidates. My template for the XML data can only take 2 candidates at a time. It is an Election Ticker. So far, I have the query setup so it pulls at least 4 candidates. Naturally, if a race has less than 4 candidates, it omits the NULL data and still outputs the 2 candidates for that race. I have now even ran into races that my have 18+ candidates. This is turning into quite a chore. here is my query:
select rc.[race number] AS RaceNumber,
max(case when seqnum = 1 then title1 end) as title1,
max(case when seqnum = 1 then [precinct percent] end) as PrecintPercent,
max(case when seqnum = 1 then [candidate num] end) as Winner,
max(case when seqnum = 1 then Votes end) as WinningVotes,
max(case when seqnum = 1 then party end) as WinningParty,
max(case when seqnum = 1 then leader end) as Winner1,
max(case when seqnum = 1 then CAST(winner AS tinyint) end) as WinnerSelected,
max(case when seqnum = 1 then [leader percent] end) as WinnerPercent,
max(case when seqnum = 2 then [candidate num] end) as Loser,
max(case when seqnum = 2 then Votes end) as LosingVotes,
max(case when seqnum = 2 then party end) as LosingParty,
max(case when seqnum = 2 then leader2 end) as Loser2,
max(case when seqnum = 2 then [leader2 percent] end) as LoserPercent,
max(case when seqnum = 2 then CAST(winner AS tinyint) end) as LoserSelected,
max(case when seqnum = 3 then [candidate num] end) as Winner3,
max(case when seqnum = 3 then Votes end) as Winner3Votes,
max(case when seqnum = 3 then party end) as Winner3Party,
max(case when seqnum = 3 then [first name]+[last name]end) as Winner3,
max(case when seqnum = 3 then CAST(winner AS tinyint) end) as Winner3Selected,
max(case when seqnum = 4 then [candidate num] end) as Loser4,
max(case when seqnum = 4 then Votes end) as Loser4Votes,
max(case when seqnum = 4 then party end) as Loser4Party,
max(case when seqnum = 4 then [first name]+ [last name]end) as Loser4,
max(case when seqnum = 4 then CAST(winner AS tinyint) end) as Loser4Selected
from
(
select
r.title1,
r.[precinct percent],
rc.[race number],
rc.[candidate num],
rc.[Votes],
rc.[winner],
c.[party],
r.[leader],
r.[leader percent],
r.[leader2],
r.[leader2 percent],
c.[first name],
c.[last name],
row_number() over (partition by rc.[race number] order by votes desc) as seqnum
from dbo.[RACE CANDIDATES] rc
inner join dbo.[CANDIDATE] c on rc.[candidate num] = c.[candidate number]
inner join dbo.[RACE] r
on rc.[race number] = r.[race number]
) rc
group by rc.[race number]
FOR XML PATH ('ELECTION'), ROOT('root')
Again, this outputs at least 4 candidates, if there are 4. Here is a snippet from the xml document:
<root>
<ELECTION>
<RaceNumber>101</RaceNumber>
<title1>President</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5083</Winner>
<WinningVotes>999877</WinningVotes>
<WinningParty>D</WinningParty>
<Winner1>Barack Obama</Winner1>
<WinnerSelected>1</WinnerSelected>
<WinnerPercent>53</WinnerPercent>
<Loser>5077</Loser>
<LosingVotes>888888</LosingVotes>
<LosingParty>R</LosingParty>
<Loser2>Mitt Romney</Loser2>
<LoserPercent>47</LoserPercent>
<LoserSelected>0</LoserSelected>
</ELECTION>
<ELECTION>
<RaceNumber>102</RaceNumber>
<title1>U.S. Congress Dist. 1</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5085</Winner>
<WinningVotes>216879</WinningVotes>
<WinningParty>D</WinningParty>
<Winner1>Bruce Braley</Winner1>
<WinnerSelected>1</WinnerSelected>
<WinnerPercent>57</WinnerPercent>
<Loser>5086</Loser>
<LosingVotes>159657</LosingVotes>
<LosingParty>R</LosingParty>
<Loser2>Ben Lange</Loser2>
<LoserPercent>42</LoserPercent>
<LoserSelected>0</LoserSelected>
</ELECTION>
<ELECTION>
<RaceNumber>133</RaceNumber>
<title1>DesMoines County Board of Supervisors</title1>
<PrecintPercent>100</PrecintPercent>
<Winner>5154</Winner>
<WinningVotes>11629</WinningVotes>
<WinningParty>D</WinningParty>
<Winner1>Bob Beck</Winner1>
<WinnerSelected>1</WinnerSelected>
<WinnerPercent>34</WinnerPercent>
<Loser>5155</Loser>
<LosingVotes>11323</LosingVotes>
<LosingParty>D</LosingParty>
<Loser2>Jim Cary</Loser2>
<LoserPercent>33</LoserPercent>
<LoserSelected>1</LoserSelected>
<Winner3>5156</Winner3>
<Winner3Votes>7018</Winner3Votes>
<Winner3Party>R</Winner3Party>
<Winner3>DarwinBunger</Winner3>
<Winner3Selected>0</Winner3Selected>
<Loser4>5157</Loser4>
<Loser4Votes>4415</Loser4Votes>
<Loser4Party>R</Loser4Party>
<Loser4>JamesSeaberg</Loser4>
<Loser4Selected>0</Loser4Selected>
</ELECTION>
If you notice, 133, has 4 candidates in it. What I am trying to do is get 133 to show the first 2 candidates, then create a duplicate node with the remaining candidates names/votes etc in it.
Here is the XSL file I am parsing my election XML data into the ticker with:
<xsl:transform version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:template match="/">
<tickerfeed version="2.4">
<!-- START ELECTION CAROUSEL -->
<playlist type="flipping_carousel" name="ELECTION2" target="carousel">
<defaults>
<outputchannels>
<active>ABC</active>
<active>MYNTV</active>
</outputchannels>
<gui-color>#CCFF99</gui-color>
</defaults>
<xsl:for-each select="root/ELECTION">
<xsl:element name="element">
<template>ELECTION_RESULTS</template>
<xsl:element name="field">
<xsl:attribute name="name">50</xsl:attribute><xsl:value-of select="title1" />
</xsl:element>
<xsl:element name="field">
<xsl:attribute name="name">51</xsl:attribute><xsl:value-of select="PrecinctPercent" />
</xsl:element>
<xsl:element name="field">
<xsl:attribute name="name">52</xsl:attribute><xsl:value-of select="WinnerSelected" />
</xsl:element>
<xsl:element name="field">
<xsl:attribute name="name">53</xsl:attribute><xsl:value-of select="Winner1" />
</xsl:element>
<xsl:element name="field">
<xsl:attribute name="name">54</xsl:attribute><xsl:value-of select="WinnerPercent" />
</xsl:element>
<xsl:element name="field">
<xsl:attribute name="name">55</xsl:attribute><xsl:value-of select="WinningVotes" />
</xsl:element>
<xsl:element name="field">
<xsl:attribute name="name">56</xsl:attribute><xsl:value-of select="LoserSelected" />
</xsl:element>
<xsl:element name="field">
<xsl:attribute name="name">57</xsl:attribute><xsl:value-of select="Loser2" />
</xsl:element>
<xsl:element name="field">
<xsl:attribute name="name">58</xsl:attribute><xsl:value-of select="LoserPercent" />
</xsl:element>
<xsl:element name="field">
<xsl:attribute name="name">59</xsl:attribute><xsl:value-of select="LosingVotes" />
</xsl:element>
<xsl:element name="field">
<xsl:attribute name="name">60</xsl:attribute><xsl:value-of select="WinningParty" />
</xsl:element>
<xsl:element name="field">
<xsl:attribute name="name">61</xsl:attribute><xsl:value-of select="LosingParty" />
</xsl:element>
</xsl:element>
</xsl:for-each>
</playlist>
<!-- END ELECTION CAROUSEL -->
I suppose my question is, can this be accomplished?
Should I do the operation in the XSL file or the SQL Query?
My current Query is the only way I have been able to get multiple canadidates to output. I would just like to figure out how to duplicate the Race Number, especially the RACETITLE with the remaining candidates, since my ticker template can only show 2 candidates at a time.
If someone could tell me yes or no, that would be ideal, but I have to get it figured out one way or another. Thanks everyone for your help.
EDIT**
I just want races that have more than 2 candidates to split into their own nodes, rather than outputting into one single node, with all 4 candidates information, such as RACE 133, I would like to have 2 -RACE 133 nodes, the first one with the first set of candidates and the second node with the second set of candidates. Assuming that the race has 4 candidates. I don't know...I am getting to a point where I feel this is not even possible.
I really am trying to figure out if I should focus more on the query or try to get the desired output using a more complex XSL file. Pulling my hair out on this one.
