Taking out items from XML C# and performing bulk delete

Question

I have a piece of code which looks like this:

string filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"\DeletedItems\" + "DeletedItems" + ".xml";
            XDocument xmlDoc = XDocument.Load(filePath);
            var list = xmlDoc.Root.Elements("ItemID")
                                       .Select(element => element.Value)
                                       .ToList();
            var idsList = FormItemIdList(list);

            ctx.zsp_deleteEndedItems(idsList);
            ctx.zsp_deleteEndedItemsTransactions(idsList);

This part of the code loads up all items from the XML file:

var list = xmlDoc.Root.Elements("ItemID")
                 .Select(element => element.Value)
                 .ToList();

Where the XML file looks like this:

<?xml version="1.0" encoding="utf-8"?>
<ItemsToDelete>
  <ItemID>113347292264</ItemID>
  <ItemID>113334066205</ItemID>
  <ItemID>113331816848</ItemID>
  <ItemID>113191634415</ItemID>
  <ItemID>183480362055</ItemID>
  <ItemID>113303425739</ItemID>
  <ItemID>112533425202</ItemID>
  <ItemID>112007496785</ItemID>
  <ItemID>111956371906</ItemID>
  <ItemID>112016647700</ItemID>
</ItemsToDelete>

Once the items are loaded into list from C# I form a string which looks like following:

113347292264,113334066205... etc etc

The string is then passed to the stored procedure which looks like following:

create procedure [dbo].[zsp_deleteEndedItems]
(
@ItemIDList nvarchar(max)
)
as
delete from 
SearchedUserItems
WHERE EXISTS (SELECT 1 FROM dbo.SplitStringProduction(@ItemIDList,',') S1 WHERE ItemID=S1.val)

Then the SplitStringProduction function is called to break down the passed string and delete the records in DB based on the passed item ID:

create FUNCTION [dbo].[SplitStringProduction]
(
  @string nvarchar(max),
  @delimiter nvarchar(5)
) RETURNS @t TABLE
(
  val nvarchar(500)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@string,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select 
    r.value('.','varchar(500)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END

Now, all this works fine, but there are some problems that I'm aware of and that I would like to avoid:

The list from the XML file can be very long, often containing 100000 + records inside

Now to avoid this I was thinkng to do the following:

Perform deletion in batches of 5000 records let's say

So the steps would be:

Pull 5000 items from the XML file
Pass those 5000 items into the procedure
Remove the 5000 ItemID's from the XML file

Can someone help me out with this, I'm not sure how to do it ?

There is a lot of text there, and questions like `But I'm not sure what is the best way to do it here?` is likely to result in being closed as 'primarily opinion-based'. It might help if you simplify the question a little. — mjwills, Nov 15 '18 at 11:11
@mjwills I'd simply want to code out those steps at the end that I wrote.. that's pretty much it — User987, Nov 15 '18 at 11:21
If the first step is not an issue then you can use [this](https://stackoverflow.com/a/419063/7324631) to create batches of 5000 of the current list and then loop through the lists and send them in. Else you have to use XmlReader to not load everything into memory and do some more work but the idea would be the same. — H.Mikhaeljan, Nov 15 '18 at 11:27

score 1 · Accepted Answer · answered Nov 15 '18 at 11:28

It sounds like you just want to split the list into smaller chunks:

var list = xmlDoc.Root.Elements("ItemID")
                                   .Select(element => element.Value)
                                   .ToList();
while(list.Any())
{
    var subList = list.Take(5000);
    var idsList = FormItemIdList(subList);

    ctx.zsp_deleteEndedItems(idsList);
    ctx.zsp_deleteEndedItemsTransactions(idsList);

    list.RemoveRange(subList);
}

Taking out items from XML C# and performing bulk delete

1 Answers1