How to aggregate XML snippets in GROUP BY operation

Question

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!

Chanukya · Accepted Answer · 2017-07-20 13:14:46Z

4

Just i tried 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;

edited Jul 20, 2017 at 13:14

answered Jul 20, 2017 at 12:40

Chanukya

5,8911 gold badge24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kannan Kandasamy Over a year ago

Root('Players')

Kannan Kandasamy Over a year ago

Also join condition is missing inside subquery 'WHERE X.TeamId = T.TeamId'

Chanukya Over a year ago

@KannanKandasamy Thanks:)

user833115xxx Over a year ago

That was almost exactly right - but there's an extra <PLAYER> element around each player - do you know how to eliminate that? Thanks for your help so far :)

Gottfried Lesigang · Accepted Answer · 2017-07-20 16:38:47Z

1

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;

The alias AS [*] tells the engine to insert the given element as-is. This avoids extra name levels. The empty PATH('') also avoids an additional name level.

answered Jul 20, 2017 at 16:38

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Collectives™ on Stack Overflow

How to aggregate XML snippets in GROUP BY operation

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related