Place XML Child nodes under multiple parent nodes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Place XML Child nodes under multiple parent nodes

Author

Topic

snots34
Starting Member

12 Posts

Posted - 2014-02-20 : 21:54:04

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources