you can use cursor to read xml data row one by one.
check for already existing row using primary key
if row exists then update that row.
else insert row
Do as Follows:
CREATE PROCEDURE SD_Insert_ShippingDetails
@PBMXML as varchar(Max),
@ShippingID as INT
AS
DECLARE @i int
DECLARE @C int
DECLARE @ShippingID_x int
DECLARE @Weight_x varchar(20)
DECLARE @Height_x varchar(20)
DECLARE @TotalBoxes_x varchar(20)
DECLARE @Price_x numeric(18,2)
DECLARE @cur_string varchar(Max)
BEGIN
exec sp_xml_preparedocument @i output,@PBMXML
@cur_string ='DECLARE cursor cur_xml for
SELECT ShippingID,Weight,Height,TotalBoxes,Price FROM OPENXML('+@i+',''Root/ShippingBox'',2)
WITH (
ShippingID int,Weight varchar(20),Height varchar(20),TotalBoxes varchar(20),Price numeric(18,2))'
EXEC(@cur_string)
open cur_xml
fetch next from cur_xml into @ShippingID_x,@Weight_x,@Height_x,@TotalBoxes_x,@Price_x
while (@@FETCH_STATUS = 0)
begin
select @c=count(*) from SD_ShippingDetails where shippingID=@ShippingID_x
if @c=0
begin
INSERT INTO SD_ShippingDetails(ShippingID,Weight,Height,TotalBoxes,Price)
values ( @ShippingID_x,@Weight_x,@Height_x,@TotalBoxes_x,@Price_x)
end
else
begin
update SD_ShippingDetails set
[Weight]=@Weight_x,Height=@Height_x,TotalBoxes=@TotalBoxes_x,Price=@Price_x
where ShippingID=@ShippingID_x
end
fetch next from cur_xml into @ShippingID_x,@Weight_x,@Height_x,@TotalBoxes_x,@Price_x
end
close cur_xml
deallocate cur_xml
exec sp_xml_removedocument @i
END