使用XML数据更新数据库中的行 [英] Update rows in Database using XML data

问题描述

这是我第一次使用XML将数据插入表中。我正在从前端保存数据（所有Datagridview）行）到xml文件并发送到数据库以插入表SD_ShippingDetails.Below是用于读取XML数据和保存数据的查询。从查询中我可以看到我删除了相关的ShippingID细节并再次插入。（DELETE FROM SD_ShippingDetails WHERE ShippingID = @ ShippingID）。我们可以通过从XML获取数据来更新SD_ShippingDetails中已有的行。如果是，请帮我查询。

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.

推荐答案

你可以使用游标逐个读取xml数据行。

使用主键检查已存在的行

如果存在行则更新该行。

否则插入行





按照以下步骤操作：

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 

这篇关于使用XML数据更新数据库中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！