如何在XML插入中获取个人身份? [英] How to get individual identity in an XML Insert?

问题描述

我在SQL中使用XML的工作还很少，所以我将尽我所能问这个问题.

说我收到了一些用于插入的SQL结构化XML.

结构可能是:

<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>

我能够插入-Team-记录并获得标识，因为在这种情况下，我只有1条记录，并且访问SCOPE_IDENTITY()将与该记录的标识匹配，并且该标识将用于插入Player节点.

在游戏"表中，我确实有一个PlayerID字段.如何获得插入的每个玩家的身份，然后使用该身份插入游戏记录?

由于执行SELECT FROM @ ReceivedXML.nodes('Player')之类的SELECT操作，将返回多个值，并且所有Player节点都将被批量插入.

任何人都可以请我指出如何执行此操作的任何指南，或者分享您如何解决此问题的方法?谢谢，非常感谢.

---

编辑-也许我没有正确解释自己.

团队有一个TeamID字段.玩家有一个PlayerID字段，与TeamID字段一样.游戏有一个GameID和一个PlayerID字段.

如何从XML中插入团队，获取新的标识TeamID，然后将Player 1插入PLayers表中，获取其新生成的PlayerID标识，然后使用先前获得的PLayerID插入Game记录. /p>

在完成Player1的操作后，我将对Player2进行相同的操作.插入播放器记录，获得身份PlayerID<插入游戏等.

解决方案

加载玩家表格，如下所示.

然后，将解析的XML与Player表的名称连接起来，以获取下一次插入Game等的ID.

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

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.

解决方案

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

这篇关于如何在XML插入中获取个人身份?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！