SQL Server XML Parse Question Help

Hello all.  I got some great help with this but I just need one more bit of help.  The following attached code is my syntax for a stored procedure I have.   I also placed in a test string I pass to it for the XML.  This works great if all the data is either updating or inserting.  For example if I pass to this proc the following:

'<Root><Data key1="1" key2="2" key3="1" val="45.00"></Data><Data key1="1" key2="3" key3="1" val="65.00"></Data><Root>'  

the proc will if not exist insert these records into my table.  If I run it again it does the update perfectly.  The problem is when I need to "Insert" new data.  Anything that exists I need to update if not insert.  So if I pass the following after I run the above the first time.


'<Root><Data key1="1" key2="2" key3="1" val="45.00"></Data><Data key1="1" key2="3" key3="1" val="65.00"></Data><Data key1="1" key2="4" key3="1" val="99.00"></Data><Root>'  

I need the first two to update and the last new one to insert.  The proc I have throws an error that it cant insert a duplicate key constraint.  So I believe the issue is the proc will either all Insert or all Update.  I hope this makes sense.  I need the new record to insert and the others to update.  Thanks for any help.  Attached is the proc syntax.

ALTER PROCEDURE [dbo].[spImportDataSummary](@xmlString xml) AS
 
declare @key1 int
declare @key2 int
declare @key3 int
declare @val float
 
;WITH XMLTable AS (
   SELECT D.element.value('@key1', 'int') as key1, D.element.value('@key2', 'int') as key2, 
   D.element.value('@key3', 'int') as key3,
   D.element.value('@val', 'float') as val
 FROM @xmlString.nodes('/Root/Data') AS D(element)
)
select @key1=XMLTable.key1, @key2=XMLTable.key2 , @key3=XMLTable.key3, @val=XMLTable.val from XMLTable
if exists( select * from DataSummary where FundInvestmentPartnerID=@key1 and LineID=@key2 and Section=@key3)
begin
;WITH XMLTable AS (
   SELECT D.element.value('@key1', 'int') as key1, D.element.value('@key2', 'int') as key2,
   D.element.value('@key3', 'int') as key3,
   D.element.value('@val', 'float') as val
 FROM @xmlString.nodes('/Root/Data') AS D(element)
)
UPDATE DataSummary
 SET Amount = XMLTable.val
FROM DataSummary INNER JOIN XMLTable on XMLTable.Key1=DataSummary.FundInvestmentPartnerID
     AND XMLTable.Key2=DataSummary.LineID AND XMLTable.Key3=DataSummary.Section
end
else
begin
;WITH XMLTable AS (
   SELECT D.element.value('@key1', 'int') as key1, D.element.value('@key2', 'int') as key2,
   D.element.value('@key3', 'int') as key3,
   D.element.value('@val', 'float') as val
 FROM @xmlString.nodes('/Root/Data') AS D(element)
)
INSERT INTO DataSummary (FundInvestmentPartnerID, LineID, Section, Amount)
 SELECT XMLTable.key1, XMLTable.key2, XMLTable.key3, XMLTable.val from XMLTable
end
 
---------------------------------------------
ALSO IF YOU NEED THE TABLE TO CREATE HERE IT IS
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DataSummary](
	[FundInvestmentPartnerID] [int] NOT NULL,
	[LineID] [int] NOT NULL,
	[Section] [int] NOT NULL,
	[Amount] [float] NULL,
 CONSTRAINT [PK_DataSummary] PRIMARY KEY CLUSTERED 
(
	[FundInvestmentPartnerID] ASC,
	[LineID] ASC,
	[Section] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Select allOpen in new window