XML with PrimaryKey values to delete rows in database table at a time

Question

XML with PrimaryKey values to delete rows in database table at a time

User7766

SSCommitted

Points: 1969
More actions
October 25, 2010 at 12:28 pm

#104003

Hi,
I'm having a gridview in my aspx page with checkbox, User can select one or more than one record(s) at a time and clicks on delete button, Then i'm constructing an object of my DTO and adding all the checked row's Primary Key (DataKeyNames - GUID's in my case) to a serializable class and generating an XML and sending it as parameter to the Stored Procedure.
My Doubt is, can i bulk delete all the rows from database table those are present in my XML document??
If it works, I can do bulk update with XML only, i already did bulk insertion by generating an XML..
My Another Question is, Will it affect the performance? Each time when i construct an DTO object and adding it to the Generic List??
WHich one is better, I mean ObjectDataSource or SQLDataSource or my XML method??
Can anyone please help me out clarifying my doubts and also XML Delete Query..
My XML will be like this
<root>
<Customer>
<CustomerID>GUID1</CustomerID>
<CustomerID>GUID2</CustomerID>
<CustomerID>GUID3</CustomerID>
...
...
</Customer>
</root>
Will bulk delete happen in the above case?
Thanks,
PAvan

Viewing 5 posts - 1 through 4 (of 4 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

If I understand you correctly, you'll have to shred the XML and then use that to generate a delete statement. That's pretty straightforward.

Which version of SQL Server are you using?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

User7766 SSCommitted Points: 1969 More actions · Answer 2

Hi,

Thanks for the reply..

We are using SQL Server 2008. Which one is the best method to bulk delete the records? Can you please let me know if you havin any idea..

I'm constructing an serialized object in front end and sending it as parameter to the backend..

Thanks,

User7766 SSCommitted Points: 1969 More actions · Answer 3

GSquared (10/25/2010)
If I understand you correctly, you'll have to shred the XML and then use that to generate a delete statement. That's pretty straightforward.
Which version of SQL Server are you using?

Hi,

Here is my SQL Server Stored Procedure.. When i try updating the whole TEMP table values, it is not updating all the ID's.

Only the first TOP ID is getting updated. Can you please let me know how can i bulk update all the ROWS in temp table..

ALTER PROCEDURE [dbo].[usp_archive_news]

AS

DECLARE @idoc int

DECLARE @doc nvarchar(max)

SET @doc ='

</NewsDTO>'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement that uses the OPENXML rowset provider.

SELECT * INTO #temp

FROM OPENXML (@idoc, '/NewsDTO',2)

WITH (NewsId uniqueidentifier)

Update News set news_isonline='N' where news_id=(select NewsID from #temp)

DROP table #temp

Awaiitng your Responses..

Thanks.

GSquared SSC Guru Points: 260824 More actions · Answer 4

Try using XQuery instead of the older methods.

DECLARE @doc XML;

SET @doc ='<NewsDTO>

<NewsId>A4C13A4A-2350-4D21-AB06-9E2946182D39</NewsId>

<NewsId>A3613580-99C3-489D-9988-92E5F24BC8F7</NewsId>

<NewsId>F934D9F9-A73F-4ACF-B225-8A0ED7BAF8C4</NewsId>

</NewsDTO>';

SELECT Doc.Nodes.query('.').value('(/NewsId/text())[1]','varchar(100)')

INTO #temp

FROM @doc.nodes('/NewsDTO/NewsId') Doc(Nodes);

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon