Need help with xquery - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Need help with xquery

Author

Topic

lazyme
Starting Member

8 Posts

Posted - 2009-11-29 : 15:26:14

I have 3 xml files; team.xml, batters.xml and bowlers.xml

team.xml
 
<Teams>
  <Team>
  <Lost>59</Lost>
  <Name>NewYork</Name>
  <Won>103</Won>
  </Team>
  <Team>
  ..
  ..
  </Team>
  ..
  ..
<Teams>

batter.xml
 
<Batters>
  <Batter>
  <Age>24</Age>
  <Team>NewYork</Team>
  <Salary>165000</Salary>
  </Batter>
  <Batter>
  ..
  ..
  </Batter>
  ..
  ..
<Batters>

bowlers.xml
 
<Bowlers>
  <Bowler>
  <Age>24</Age>
  <Team>NewYork</Team>
  <Salary>180000</Salary>
  </Bowler>
  <Bowler>
  ..
  ..
  </Bowler>
  ..
  ..
<Bowlers>

Each team has two types of players, batters and bowlers. I want to write an xquery that displays the average salaries of the players(batters and bowlers combined) in the team. If a team has no batters nor bowlers, that team should not be displayed.

This is what I tried

xquery version "1.0";

<query>
{
	for $x in doc("team.xml")/Teams/Team/Team
	let $y := doc("batter.xml")/Batters/Batter[Team=$x]
	let $z := doc("bowler.xml")/Bowlers/Bowler[Team=$x]
	
	let $avgsal := avg($y/Salary) + avg($z/Salary)
	
	return
	if ( count($y) > 0 and count($z) > 0 ) then
	(
		<Team>
		<Name>{data($x)}</Name>
		<AvgSal>{$avgsal}</AvgSal>
		</Team>
	)
	else()
}
</query>

However, I realize that I am calculating average salaries for batters and bowlers separately and then adding them which is wrong.

Can someone please help me with this query.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources