sql-serverssisxmltask

How to export from SQL Server to XML

What is the easiest way to export to XML from SQL Server [Table or view] to XML?

This is what I have done till now

Execute Sql Task

SELECT * FROM Production.Product 
FOR XML AUTO, TYPE, ROOT('Data')

ResultSet XML

in left result set section I have created new variable 0 User::XMLVal

Script Task

 Dim sw As New IO.StreamWriter("D:\Apps\SSIS\test.xml")
    sw.Write(Dts.Variables("User::XMLVal").Value.ToString())


    sw.Dispose()
Solution

  • If you want to use the task components, set up an Execute SQL Task to read the result of a SQL statement or stored procedure into a user-defined SSIS variable. Your statement above is a good example of what it should look like:

    enter image description here

    Then use the XML Task to write the contents of the variable to a file:

    enter image description here