SSIS - Export XML - XML destination

In this post, I will share with you on how I have exported xml returned from a Stored procedure as xml file in a folder using SSIS.

Below is my Stored procedure:

CREATE PROCEDURE Get_Versions
AS
BEGIN
SET NOCOUNT ON
DECLARE @Tmp TABLE (ID INT IDENTITY(1,1),Product VARCHAR(100))
INSERT @Tmp SELECT 'SQL Server 2012'
INSERT @Tmp SELECT 'SQL Server 2014'
INSERT @Tmp SELECT 'SQL Server 2016'
SELECT (SELECT * FROM @Tmp FOR XML PATH('Versions')) XmlData
END

 

 Stored Procedure output will be like shown below:

<Versions>

  <ID>1</ID>

  <Product>SQL Server 2012</Product>

</Versions>

<Versions>

  <ID>2</ID>

  <Product>SQL Server 2014</Product>

</Versions>

<Versions>

  <ID>3</ID>

  <Product>SQL Server 2016</Product>

</Versions>

 

 

 

 

I have added Data Flow Task on Control Flow tab and

OLE DB Source and Flat File Destination on Data Flow tab and Data Conversion task between source & destination.

 

Below are the respective task settings:

 

 

 

 

 

 

 

 

  

See Also:

• My SSIS articles
• My SQL Server XML articles