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?
 
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
0
 
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.
0
 
sbornstein2Author Commented:
sorry for the delay ac, this was completely perfect.  Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.