Update rows in Database using XML data

Question

4.20/5 (2 votes)

See more:

SQL-Server-2005

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      
          
    --if(@Op = 'I')        
        
    DELETE FROM SD_ShippingDetails WHERE ShippingID=@ShippingID      
    --BEGIN      
          
    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.

Posted 19-Mar-13 8:34am

Prathap Gangireddy

Updated 19-Mar-13 8:36am

RedDk

v2

Add a Solution

Comments

Prathap Gangireddy 8-Apr-13 6:03am

I have solved it.Thanks.

Surendra Adhikari SA 2-Jun-13 6:24am

welcome . if solved then you can mark question as solved .

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Surendra Adhikari SA · Answer 1 · 2013-03-20T01:11:00

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:

SQL

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