How to aggregate XML snippets in GROUP BY operation

Viewed 70

I'm working with some rows that contain XML snippets.

My rows in their current state look like this:

TeamId     Player
----------------------------------------------------------------------------
1   |  <Player><FirstName>Bob</FirstName><LastName>Smith</LastName></Player>
1   |  <Player><FirstName>Sam</FirstName><LastName>Jones</LastName></Player>
2   |  <Player><FirstName>David</FirstName><LastName>White</LastName></Player>
2   |  <Player><FirstName>James</FirstName><LastName>Black</LastName></Player>

With my query I'm looking to group the rows by TeamId and aggregate those XML snippets into a parent <Players> element, so the output will look like this:

1   | <Players>
        <Player><FirstName>Bob</FirstName><LastName>Smith</LastName></Player>
        <Player><FirstName>Sam</FirstName><LastName>Jones</LastName></Player>
      </Players>  
2   | <Players>
        <Player><FirstName>David</FirstName><LastName>White</LastName></Player>
        <Player><FirstName>James</FirstName><LastName>Black</LastName></Player>
      </Players>

How should I do this? Many thanks!

2 Answers
Related