How to combine XML fragments in GROUP BY operations

Question

I work with some strings that contain XML fragments.

My lines in the current state are as follows:

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>

In my query, I want to group the strings TeamIdand combine these XML fragments into a parent element <Players>, 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 it? Many thanks!

Answer 1

I just tried it like this

CREATE TABLE #A
    (
    TEAMID INT,
    PLAYER XML 
    )
    INSERT INTO #A VALUES
    (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>')


SELECT  T.TEAMID,
        (   SELECT  TEAMID ,
                    PLAYER
            FROM    #A AS X
            WHERE X.TeamId = T.TeamId
            FOR XML PATH('PLAYER'), TYPE, ROOT('PLAYERS')

        ) AS XML_FORMAT_STRING
FROM    #A AS T
GROUP BY T.TEAMID;

Answer 2

Try it like this:

SELECT  tbl.TeamId,
        (   SELECT Player AS [*]
            FROM   YourTable AS X
            WHERE X.TeamId = tbl.TeamId
            FOR XML PATH(''), TYPE, ROOT('Players')
        ) AS [*]
FROM    YourTable AS tbl
GROUP BY tbl.TeamId;

AS [*] . . PATH('') .