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
    • 232
    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 :

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

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
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: