How to get individual identity in an XML Insert?

Question

I haven't worked much with XML in SQL so I'll try to ask the question the best I can.

Say I receive some structured XML in SQL for insertion.

The structure might be:

<Team>
   <Player>
     <Name>Player1</Name>
     <Games>
       <Game>
         <Date>9/7/2009</Date>
         <MinutesPlayed>90</MinutesPlayed>
       </Game>
     </Games>
   </Player>
   <Player>
     <Name>Player2</Name>
     <Games>
       <Game>
         <Date>9/7/2008</Date>
         <MinutesPlayed>87</MinutesPlayed>
       </Game>
     </Games>
   </Player>
</Team>

I am able to insert the -Team- record and get the identity, since in this case I only have 1 record and accessing SCOPE_IDENTITY() will match the identity for that record, and that identity I will use to insert the Player nodes.

In the Game table, I do have a PlayerID field. How can I obtain the identity for each Player inserted and then use that identity to insert the Game records?

Since doing a SELECT like SELECT FROM @ReceivedXML.nodes('Player') would return several values, and all the Player nodes would be inserted in batch.

Can anyone please point me to any guides on how to do this, or maybe share how you solved this problem? Thanks, really appreciated.

---

Edit - maybe I didn't explain myself correctly.

Team has a TeamID field. Players have a PlayerID Field, as wel as TeamID Field. Games have a GameID and a PlayerID field.

From the XML, how would I go about inserting the Team, getting the newly identity TeamID, then insert Player one into PLayers table, getting its newly generated PlayerID identity, then inserting the Game record, using the previously obtained PLayerID.

After I'm done with Player1, I'd do the same for Player2. Insert the Player record, get identity PlayerID< insert Game, etc.

Answer 1

Load players table, as below.

Then, join parsed XML with Player table on name to get the IDs for the next insert into Game etc

DECLARE @foo XML

SELECT @foo = N'
<Team>
   <Player>
     <Name>Player1</Name>
     <Games>
       <Game>
         <Date>9/7/2009</Date>
         <MinutesPlayed>90</MinutesPlayed>
       </Game>
     </Games>
   </Player>
   <Player>
     <Name>Player2</Name>
     <Games>
       <Game>
         <Date>9/7/2008</Date>
         <MinutesPlayed>87</MinutesPlayed>
       </Game>
     </Games>
   </Player>
</Team>
'


INSERT Players
    (PlayerName)
SELECT
    CAST(y.item.query('data(Name)') AS varchar(30))
FROM
    @foo.nodes('/Team') x(item)
    CROSS APPLY
    x.item.nodes('./Player') AS y(item)


INSERT Game
    (PlayerID, Date, MinutesPlayed)
SELECT
    P.PlayerID,
    CAST(z.item.query('data(Date)') AS varchar(30)),
    CAST(z.item.query('data(MinutesPlayed)') AS varchar(30))
FROM
    @foo.nodes('/Team') x(item)
    CROSS APPLY
    x.item.nodes('./Player') AS y(item)
    CROSS APPLY
    y.item.nodes('./Games/Game') AS z(item)
    JOIN
    Players P ON CAST(y.item.query('data(Name)') AS varchar(30)) = P.PlayerName