Let us say we want to delete data from a table based on the id's above. Below we create a stored procedure that receives the list of ID's, in the form of an XML document as an input parameter.
The proc then parses the XML document using sp_xml_preparedocument and OPENXML rowset provider , and finally deletes record based on ID's.
CREATE PROCEDURE [dbo].[DeleteDataBasedOnID]
DECLARE @handle INT
EXEC sp_xml_preparedocument @Dochandle OUTPUT, @XMLDoc
ID IN (
SELECT * FROM OPENXML (@Dochandle, '/ids/id') WITH (id INT '.')
EXEC sp_xml_removedocument @Dochandle
To decrease the size of XML document ,try to reduce the length of element/attribute names in the XML tags as small as possible. A smaller document could improve parsing time and will take lesser time to travel over the network.