Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 9:34am
Edited 19-Mar-13 9:36am
RedDk10.3K
v2
Comments
Prathap Gangireddy at 8-Apr-13 6:03am
   
I have solved it.Thanks.
Rajan Adhikari at 2-Jun-13 6:24am
   
welcome . if solved then you can mark question as solved .

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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 
  Permalink  
v2
Comments
Prathap Gangireddy at 20-Mar-13 7:47am
   
This looks like a good idea.Can you help me with the query.

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

  Print Answers RSS
0 DamithSL 325
1 OriginalGriff 199
2 BillWoodruff 170
3 Zoltán Zörgő 165
4 Maciej Los 123
0 OriginalGriff 7,854
1 DamithSL 5,949
2 Sergey Alexandrovich Kryukov 5,449
3 Maciej Los 5,084
4 Kornfeld Eliyahu Peter 4,539


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 24 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100