我到处都寻求帮助.
我对这一切都是新手,我发现很难理解它上面的所有文档.
说我有这个XML：
<footballteams>
<team manager="Benitez">
<name>Liverpool</name>
<ground>Anfield</ground>
</team>
<team manager="Mourinho">
<name>Chelsea</name>
<ground>Stamford Bridge</ground>
</team>
<team manager="Wenger">
<name>Arsenal</name>
<ground>Highbury</ground>
</team>
</footballteams>
我想从中获取数据并将其加载到名为footballteams(name,manager,ground)的关系表中.
我想在sql Server 2008中这样做,从我到处读到的一个有用的方法是.nodes()方法,但我只是无法理解如何使用它.
尝试这样的事情：
DECLARE @input XML = '<footballteams>
<team manager="Benitez">
<name>Liverpool</name>
<ground>Anfield</ground>
</team>
<team manager="Mourinho">
<name>Chelsea</name>
<ground>Stamford Bridge</ground>
</team>
<team manager="Wenger">
<name>Arsenal</name>
<ground>Highbury</ground>
</team>
</footballteams>'
SELECT
TeamName = Foot.value('(name)[1]','varchar(100)'),Manager = Foot.value('(@manager)',Ground = Foot.value('(ground)[1]','varchar(100)')
FROM
@input.nodes('/footballteams/team') AS Tbl(Foot)
基本上,对.nodes()的调用创建一个名为Tbl的伪“表”,其中一个名为Foot的XML列将包含每个< team> XML节点作为其值.
然后,您可以从该伪表中进行选择,并从该< team>中提取XML属性(@manager)和元素(name,ground)的各个值. XML片段,并将它们转换为您的选择类型的T-sql数据值.
要将这些值插入表中 – 只需使用基于此的INSERT语句：
;WITH ShreddedData AS
(
SELECT
TeamName = Foot.value('(name)[1]','varchar(100)')
FROM
@input.nodes('/footballteams/team') AS Tbl(Foot)
)
INSERT INTO dbo.FootballTeams(Name,Manager,Ground)
SELECT TeamName,Ground
FROM ShreddedData
