XML : How do I get Xquery to extract all the matching nodes in an XML Document

I'm very new to XQuery and know that this is probably an easy answer but I just can't wrap my head around this one.

I have an XML file like so:

  <EventLog>    <SongSet>      <Song SongID="S002">        <Title>Band on the Run</Title>        <Composer>Paul McCartney</Composer>        <Duration>1.15</Duration>      </Song>      <Song SongID="S003">        <Title>Come on Over</Title>        <Composer>Shania Twain</Composer>        <Duration>3.15</Duration>      </Song>     </SongSet>     <Contestant Name="Randy Stuss" Hometown="Ottawa">        <Repertoire>          <SongRef>S002</SongRef>          <SongRef>S003</SongRef>        </Repertoire>       </contestant>       <Contestant Name="Fletcher Gee" Hometown="Toronto">        <Repertoire>          <SongRef>S002</SongRef>          <SongRef>S003</SongRef>        </Repertoire      </contestant>    </ContestantSet>  </EventLog>    

I need the output to look like this:

  <songs>     <song>       <name> SONG NAME1 </name>      <composer> COMPOSER NAME </composer>      <singers>        <singer>Singer1 Name  </singer>          <singer>Singer2 Name  </singer>        </singers>    </song>  <songs>    

I can do all of it except for getting the names of the singers together. My output looks like this:

  <Songs    <Song>      <Title>Band on the Run</Title>      <Composer>Paul McCartney</Composer>      <Singers>        <Singer>Fletcher Gee</Singer>      </Singers>    </Song>    <Song>      <Title>Band on the Run</Title>      <Composer>Paul McCartney</Composer>    <Singers>      <Singer>Randy Stuss</Singer>    </Singers>    </Song>  </Songs>    

And Here is my Xquery code:

  for $x in //SongSet/Song   for $y in //ContestantSet/Contestant   where $x/@SongID = $y/Repertoire/SongRef   return <Song><Title>{data($x/Title)}</Title>         <Composer>{data($x/Composer)}</Composer>         <Singers><Singer>{data($y/@Name)}</Singer></Singers></Song>    

Where am I going wrong?

Thank you for your help!!!!

No comments:

Post a Comment