Update rows in Database using XML data

Posted by Nkkppp under Sql Server on 3/19/2013 | Points: 10 | Views : 4238 | Status : [Member] | Replies : 1
Hi,

This is the first time I am using XML to insert data into a table.I am saving the data from the front end(all the Datagridview rows) into an xml file and sending it to database to insert into table SD_ShippingDetails.Below is the Query for reading the XML data and saving data.As you can see from the Query I am deleting the related ShippingID details and inserting again.(DELETE FROM SD_ShippingDetails WHERE ShippingID=@ShippingID).Can we update already existing rows in the SD_ShippingDetails by getting the data from XML.If Yes,Please help me with the query.

CREATE PROCEDURE SD_Insert_ShippingDetails    
@PBMXML as varchar(Max),
@ShippingID as INT

AS
BEGIn


declare @i int

exec sp_xml_preparedocument @i output,@PBMXML


DELETE FROM SD_ShippingDetails WHERE ShippingID=@ShippingID

INSERT INTO SD_ShippingDetails(ShippingID,Weight,Height,TotalBoxes,Price)
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 sp_xml_removedocument @i

END


Thanks.




Responses

Posted by: Jayakumars on: 3/19/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
refer this

http://forums.asp.net/t/1467844.aspx/1

Mark as Answer if its helpful to you

Nkkppp, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response