In this article we will see how to pass an array of parameters to a stored procedure using xml. For illustration purpose we will be passing the below list of id's :

<ids>
    <id>1</id>
    <id>2</id>
    <id>3</id>
    <id>4</id>
</ids>

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]
(
    @XMLDoc XML
)
AS
BEGIN

   DECLARE @handle INT

   EXEC sp_xml_preparedocument @Dochandle OUTPUT, @XMLDoc

   DELETE FROM
       TABLE
   WHERE
       ID IN (
           SELECT * FROM OPENXML (@Dochandle, '/ids/id') WITH (id INT '.') 
       )
   EXEC sp_xml_removedocument @Dochandle
END
GO

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.