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

Viewing 4 posts - 1 through 5 (of 5 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