CODEWITHSUNDEEP
c#sql-serversql-server-2005datagridview
Prathap
Prathap

Reputation: 313

Update rows in Database using XML data

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.

Upvotes: 0

Views: 1565

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

You are on SQL Server 2005 so you can use the XML datatype instead of openxml so this answer uses that instead. Using the XML datatype is not necessary for the solution. You can rewrite using openxml if you want to.

You specified in a comments that there is an ID identity field in SD_ShippingDetails (I assume that is the primary key) but you also said that the combination of ShippingID and Weight is unique. That leaves us with a table structure that looks like this.

create table dbo.SD_ShippingDetails
(
  ID int identity primary key,
  ShippingID int not null,
  Weight varchar(20) not null,
  Height varchar(20),
  TotalBoxes varchar(20),
  Price numeric(18,2),
  unique (ShippingID, Weight)
);

The stored procedure first needs to update all rows that already exist in SD_ShippingDetails and after that it needs to insert the rows that are missing.

create procedure dbo.SD_Insert_ShippingDetails
  @PBMXML as xml
as

update dbo.SD_ShippingDetails 
set Height = T.N.value('(Height/text())[1]', 'varchar(20)'),
    TotalBoxes = T.N.value('(TotalBoxes/text())[1]', 'varchar(20)'),
    Price = T.N.value('(Price/text())[1]', 'numeric(18,2)')
from @PBMXML.nodes('Root/ShippingBox') as T(N)
where ShippingID = T.N.value('(ShippingID/text())[1]', 'int') and
      Weight = T.N.value('(Weight/text())[1]', 'varchar(20)');

insert into dbo.SD_ShippingDetails(ShippingID, Weight, Height, TotalBoxes, Price)
select T.N.value('(ShippingID/text())[1]', 'int'),
       T.N.value('(Weight/text())[1]', 'varchar(20)'),
       T.N.value('(Height/text())[1]', 'varchar(20)'),
       T.N.value('(TotalBoxes/text())[1]', 'varchar(20)'),
       T.N.value('(Price/text())[1]', 'numeric(18,2)')
from @PBMXML.nodes('Root/ShippingBox') as T(N)
where not exists (
                 select *
                 from dbo.SD_ShippingDetails
                 where ShippingID = T.N.value('(ShippingID/text())[1]', 'int') and
                       Weight = T.N.value('(Weight/text())[1]', 'varchar(20)')
                 );

SQL Fiddle

Upvotes: 2

Eric J. Price
Eric J. Price

Reputation: 2785

I would populate your XML into a variable table and then use an Update Statement and an Insert with a Not Exists.

If you had SQL 2008 you could replace your delete and insert statements with this...

MERGE   SD_ShippingDetails AS Target
USING  (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)) ) AS source (ShippingID,Weight,Height,TotalBoxes,Price)
        ON (target.ShippingID = source.ShippingID)
WHEN    MATCHED THEN 
        UPDATE  SET Weight = source.Weight,
                    Height = source.Height,
                    TotalBoxes = source.TotalBoxes,
                    Price = source.Price
WHEN    NOT MATCHED THEN    
        INSERT (ShippingID,Weight,Height,TotalBoxes,Price)
        VALUES (source.ShippingID,source.Weight,source.Height,source.TotalBoxes,source.Price);

Upvotes: 0

granadaCoder
granadaCoder

Reputation: 27894

If you have Sql Server 2005, then placing the values in #temp or @variables tables is best.

With 2008 and up, you could piggy back on the MERGE functionality.

http://msdn.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

Here is a good link for xml shredding. Note, you are using the older version of OPENXML. That was a more Sql Server 2000 command. Check Plamen's blog below for 2005 and above syntax.

http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html

Upvotes: 0

Related Questions