SSIS – Passing XML parameter in a Stored Procedure and Retrieve the Result in Table

Introduction

My friend circle is great and they do not want me to take a break. They continuously provide me queries and I tried to solve it. Sometimes it is beyond my limit. But I tried to provide my best as I can.

This article is one of my friends request. It is a wonderful scenario to passes XML parameters into a stored procedure and the output result must store in a Table object. Here I am taking a mid level temporary table to store records before the actual save in table object. If you want you can use temporary table concept with SSIS also.

Hope it will be interesting.

What the Scenario is

Scenario is simple one. We have a stored procedure named [dbo].[proc_ReadXMLDocument]

This procedure has single parameters named @p_XmlDoc is data type XML.

When we passes the XML parameters value in the stored procedure it returns us a result set and we have to store the result set into a Table object. That’s all.

Hope you understand the Scenario.

Let’s See the Definition of Stored Procedure

IF OBJECT_ID(N'[dbo].[proc_ReadXMLDocument]', N'P')IS NOT NULL

   BEGIN

       DROP PROCEDURE [dbo].[proc_ReadXMLDocument];

   END

GO

CREATE PROCEDURE [dbo].[proc_ReadXMLDocument]

   (

     @p_XmlDoc   XML = NULL

   )

AS

BEGIN

    DECLARE @handel INT;

    EXEC sp_xml_preparedocument @handel OUTPUT, @p_XmlDoc;

    SELECT  StdRoll, StdName, TotalMarks

    FROM    OPENXML (@handel, '/root/row', 1)

            WITH

            (

                StdRoll       INT,

                StdName       VARCHAR (50),

                TotalMarks    INT

            );

      EXEC sp_xml_removedocument @handel;

END

GO

Execute the Stored Procedure From Microsoft SQL Server Management Studio

EXEC [dbo].[proc_ReadXMLDocument]

     @p_XmlDoc = '<root>

<row StdRoll="1" StdName="Joydeep Das" TotalMarks="220" />

<row StdRoll="2" StdName="Deepasree Das" TotalMarks="180" />

<row StdRoll="3" StdName="Shipra Roy Chowdhury" TotalMarks="210" />

</root>';

Output:

StdRoll                   StdName                             TotalMarks

1                              Joydeep Das                         220

2                              Deepasree Das                     180

3                              Shipra Roy Chowdhury       210

How we do it in SSIS

Step – 1 [ Control Flow of Package ]

Step – 2 [ The Variable Laist ]

We have several package level variables.

Variable Name	Data Type	Comments
v_XMLParam	String	Store the XML Staring to Pass in Stored Procedure
v_Result	Object
v_Roll	Int32
v_Name	String
v_Marks	Int32
v_Count	Int32

Step – 3 [ Execute SQL Task ]

We have a mid level temporary table here we just TRUNCATE the table.

TRUNCATE TABLE tempResultSet;

Step – 4 [ Execute SQL Task where we call the Stored Procedure ]

Step – 5 [ ForEach Loop Container ]

Step – 6 [ Execute SQL Task to Insert into Temp Table ]

Step – 7 [ Data Flow Task ]

Step – 8 [ Run the Package and Observe ]

Hope you like it.

Posted by: MR. JOYDEEP DAS