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]

Open in new window

sbornstein2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Try it this way:

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)
      Where      EXISTS(
                  select      1
                  from      DataSummary
                  where      FundInvestmentPartnerID = D.element.value('@key1', 'int')
                              and LineID = D.element.value('@key2', 'int')
                              and Section = D.element.value('@key3', 'int'))
      )
      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)
      Where      NOT EXISTS(
                  select      1
                  from      DataSummary
                  where      FundInvestmentPartnerID = D.element.value('@key1', 'int')
                              and LineID = D.element.value('@key2', 'int')
                              and Section = D.element.value('@key3', 'int'))

)
      INSERT      DataSummary (FundInvestmentPartnerID, LineID, Section, Amount)
      SELECT      XMLTable.key1, XMLTable.key2, XMLTable.key3, XMLTable.val from XMLTable
--end

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sbornstein2Author Commented:
I will try this tomorrow AC as the dev sql box is down this evening.  I can't wait to test it out, it would be a huge help if this works.  I will get back to you soon on this.
sbornstein2Author Commented:
sorry for the delay ac, this was completely perfect.  Thanks again
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.