更新时间:2023-11-25 14:24:04
之前有一位同事解决过类似的问题.这是我们想出的.不直观!
A co-worker had tackled a similar problem before. Here is what we came up with. NOT intuitive!
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)