Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
  • SQL : How to pass an array of parameters to stored procedure using XML?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 499
    Comment on it

    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 :


    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
       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.

 0 Comment(s)

Sign In

Sign up using

Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: