April 5, 2006 at 9:54 am
I have a stored procedure that I am using to do bulk inserts and updates I would like to do bulk deletes also using a not in statement but am not sure about the syntax using openXML can someone assist me with that?
I thought the insert statement could just be reversed.
Thanks
Dan
CREATE PROC sp_ADUserUpdateXML @ADData nText
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@ADData
--This code updates old data.
UPDATE AdUsers
SET
AdUsers.Logon = XMLEmployee.Logon,
AdUsers.UserName = XMLEmployee.UserName
AdUsers.Dept = XMLEmployee.Dept
AdUsers.DisplayName = XMLEmployee.DisplayName
FROM OPENXML(@hDoc, 'NewDataSet/ADUsers')
WITH (Logon char(10), UserName varchar(255), Dept varchar(255), DisplayName varchar(255), EmailAddress varchar(255)) XMLADUsers
WHERE AdUsers.Logon = XMLEmployee.Logon
--This code inserts new data.
Insert Into AdUsers
SELECT Logon, Replace(UserName,'.',' ') UserName,Dept,DisplayName,EmailAddress
FROM OPENXML (@hdoc, '/NewDataSet/ADUsers',1)
WITH (Logon char(10), UserName varchar(255), Dept varchar(255), DisplayName varchar(255), EmailAddress varchar(255)) XMLADUsers
Where XMLADUsers.Logon Not IN (Select Logon from AdUsers)
--This code will delete old data.
DELETE FROM AdUsers
SELECT Logon,UserName,Dept,DisplayName,EmailAddress
FROM AdUsers
EXEC sp_xml_removedocument @hDoc
GO
April 5, 2006 at 10:33 am
I do not have XML experience with T-SQL, but from what I can tell, you should be able to join your XML doc and treat it like a table, correct? If so, try this
DELETE AdUsers
FROM AdUsers
LEFT JOIN OPENXML (@hdoc, '/NewDataSet/ADUsers',1)
WITH (Logon char(10), UserName varchar(255), Dept varchar(255), DisplayName varchar(255), EmailAddress varchar(255)) XMLADUsers
ON XMLADUsers.Logon = AdUsers.Logon
WHERE AdUsers.Logon is NULL
This is basically, like you said, a reverse of the insert statement (without the select clause) using an ANSI join.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply