Using openxml to delete multiple records

Question

Using openxml to delete multiple records

david James Naylor

Ten Centuries

Points: 1026
More actions
October 16, 2006 at 10:00 am

#68448

Would anyone be able to help delete some records from a table using an xmldocument passed into a stored procedure.
basically i want to insert some records to an archive table using openxml. this works fine and can be seen in the procedure below.
i then want to use that same document to delete the records from my live table as they now exists in the archive table. the problem is, the code i have in my SP below, deletes all contents from my live table when executed.
any help would be great.
here is a sample of the xml i use
#########################################Inner text valueinner text value##########################################
here is the stored procedure
#########################################
ALTER PROC [dbo].[BugTrack_InsertBugsToArchiveTable]
(
@doc nvarchar(max)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
insert into BugTrack_archive
select *
from OpenXml(@idoc, '/ROOT/bug', 0)
with (ID int '@ID',
submittedBy nvarchar(50) '@submittedBy',
timeLogged nvarchar(50) '@timeLogged',
bugType int '@bugType',
example nvarchar(50) '@example',
description nvarchar(3500) '.',
status int '@status',
fixedBy nvarchar(50) '@fixedBy',
fixedDate nvarchar(50) '@fixedDate',
severity nvarchar(50) '@severity',
released bit '@released',
lastModified nvarchar(50) '@lastModified',
projectID nvarchar(50) '@projectID',
archive nvarchar(50) '@archive')
EXEC sp_xml_removedocument @idoc
DECLARE @jdoc int
EXEC sp_xml_preparedocument @jdoc OUTPUT, @doc
--delete from live table now
delete from dbo.BugTrack
SELECT *
FROM dbo.BugTrack AS o
JOIN
OPENXML (@jdoc, '/ROOT/Bug',1) WITH (ID int) AS x
ON o.ID = x.ID
EXEC sp_xml_removedocument @jdoc
END
##########################################

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

CyberMirage Valued Member Points: 50 More actions · Answer 1

Since you now have the records in the Archive table how about deleting from the main table where matching records exist in the archive table?

DELETE FROM MainTable WHERE Exists(SELECT * FROM ArchiveTable WHERE ArchiveTable.UniqueField = MainTable.UniqueField)

Ian Ashworth
Applications Developer
Mirage Software

david James Naylor Ten Centuries Points: 1026 More actions · Answer 2

Thankyou very much, i think i was trying to over complicate things, that works a treat.

Regards