我正在尝试使用 XML 中的选择将行插入到表中。我想我已经很接近了。我哪里错了?
declare @xmldata xml;
set @xmldata = '<Database>
<PurchaseDetails>
<PurchaseDetail>
<Upc>72594206916</Upc>
<Quantity>77</Quantity>
<PurchaseDate>9/2010</PurchaseDate>
<PurchaseCity>Dallas</PurchaseCity>
<PurchaseState>TX</PurchaseState>
</PurchaseDetail>
<PurchaseDetail>
<Upc>72594221854</Upc>
<Quantity>33</Quantity>
<PurchaseDate>12/2013</PurchaseDate>
<PurchaseCity>Nashville</PurchaseCity>
<PurchaseState>TN</PurchaseState>
</PurchaseDetail>
</PurchaseDetails>
</Database>'
insert into PurchaseDetails
(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
select
x.Rec.value('Upc','char(11)'),
x.Rec.value('Quantity','int'),
x.Rec.value('PurchaseDate','varchar(7)'),
x.Rec.value('PurchaseCity','varchar(50)'),
x.Rec.value('PurchaseState','char(2)')
from @xmlData.nodes('//Database/PurchaseDetails/PurchaseDetail') as x(Rec)
之前有个同事也解决过类似的问题。这就是我们的想法。不直观!
insert into PurchaseDetails
(Upc, Quantity, PurchaseDate, PurchaseCity, PurchaseState)
select
pd.value('Upc[1]','char(11)'),
pd.value('Quantity[1]','int'),
pd.value('PurchaseDate[1]','varchar(7)'),
pd.value('PurchaseCity[1]','varchar(50)'),
pd.value('PurchaseState[1]','char(2)')
from @xmlData.nodes('//Database/PurchaseDetails') as x(Rec)
cross apply @xmlData.nodes('//Database/PurchaseDetails/PurchaseDetail') as i(pd)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)