For XML Explicit Syntax to Normalize a table

[Banyardi]

I am trying to write a FOR XML EXPLICIT query for the following table:

GameName Count1 Count2 CountTotal Percent1 Percent2 PercentTotal
---------- ----------- ----------- ----------- ----------- ----------- ------------
Clinker 44 55 99 45 55 100
Skullcap 12 88 100 10 90 100


What I want to create is:

<root>
<games GameName="Clinker">
<count Count1="44" Count2="55" CountTotal="99" />
<percent Percent1="45" Percent2="55" PercentTotal="100" />
</games>
<games GameName="Skullcap">
<count Count1="12" Count2="88" CountTotal="100"/>
<percent Percent1="10" Percent2="90" PercentTotal="100"/>
</games>
</root>

Using the following SQL query:
Select 1 As Tag,
Null As Parent,
GameName As [Games!1!GameName],
Null AS [Games!2!Count1],
Null AS [Games!2!Count2],
Null AS [Games!2!CountTotal],
Null AS [Games!3!Percent1],
Null As [Games!3!Percent2],
Null AS [Games!3!PercentTotal]
From Games
UNION ALL
Select
2,
1,
Null,
Count1,
Count2,
CountTotal,
Null,
Null,
Null
From Games
UNION ALL
Select
3,
2,
Null,
Null,
Null,
Null,
Percent1,
Percent2,
PercentTotal
From Games
FOR XML Explicit



What I am getting is:


<Games GameName="Clinker" />
<Games GameName="Skullcap">
<Games Count1="44" Count2="55" CountTotal="99" />
<Games Count1="12" Count2="88" CountTotal="100" />
<Games Percent1="45" Percent2="55" PercentTotal="100" />
<Games Percent1="10" Percent2="90" PercentTotal="100" />
</Games>

Which of course isn't well formed XML.

Can anyone give me a clue on how to write this query?

Thanks!

[e-mail]