Stored Procedure for inserting/updating database from XML

Chú ý:

+ XMLdata phải là string dưới dạng không chứa khai báo "<?xml version="1.0" encoding="UTF-8"?>"

+ Có thể dùng temp table để lấy dữ liệu từ XMLdata. sau đó thao tác trên temp table đó

Ví dụ: Chạy đoạn script bên dưới để thấy kết quả

DECLARE @XMLdata XML;
SELECT @XMLdata = '
<Garage>
<WareHouse>
<WareHouseID>195</WareHouseID>
<Name>CC Cuts</Name>
<Location>Boston</Location>
<ItemGroup>
<WareHouseID>195</WareHouseID>
<ItemGroupID>235</ItemGroupID>
<Name>C Parts</Name>
<Item>
<ItemID>645</ItemID>
<ItemGroupID>235</ItemGroupID>
<Color>Red</Color>
<Location>B Wing</Location>
<Price>165.00</Price>
<Manufacture>Danish igc</Manufacture>
<Supplier>TransEuro</Supplier>
<SerialNumber>1645570683</SerialNumber>
</Item>
<Item>
<ItemID>646</ItemID>
<ItemGroupID>235</ItemGroupID>
<Color>Red</Color>
<Location>B Wing</Location>
<Price>165.00</Price>
<Manufacture>Danish igc</Manufacture>
<Supplier>TransEuro</Supplier>
<SerialNumber>1645570684</SerialNumber>
</Item>
</ItemGroup>
</WareHouse>
</Garage>
';

SELECT xmlAlias.a.value('ItemGroupID[1]', 'nvarchar(50)') ,
Color = xmlAlias.a.value('Color[1]', 'nvarchar(50)') ,
Location = xmlAlias.a.value('Location[1]', 'nvarchar(50)') ,
Price = xmlAlias.a.value('Price[1]', 'nvarchar(50)') ,
Manufacture = xmlAlias.a.value('Manufacture[1]', 'nvarchar(100)') ,
Supplier = xmlAlias.a.value('Supplier[1]', 'nvarchar(100)') ,
SerialNumber = xmlAlias.a.value('SerialNumber[1]', 'nvarchar(100)')
FROM @XMLdata.nodes('/Garage/WareHouse/ItemGroup/Item') AS xmlAlias ( a );