3

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()
Zerotoinfinity
  • 6,290
  • 32
  • 130
  • 206

2 Answers2

5

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

Yuck
  • 49,664
  • 13
  • 105
  • 135
  • But i am not gettin XML datatype. I am using 2005. I tried with Nvarchar but it fails **[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".** – Zerotoinfinity Feb 10 '12 at 19:47
  • 2
    @Zerotoinfinite Make sure your `Execute SQL Task` has its `ConnectionType` property set to `ADO.NET`, `ResultSet = None`, and use `OUTPUT` parameters to get the values from a stored procedure call. – Yuck Feb 10 '12 at 19:54
1

You can also use the query in a data flow source adapter and use the Export Column Transformation --- Using SSIS to extract a XML representation of table data to a file

This saves you the trouble of managing your own scripts and what not, it's all out of the box functionality.

Community
  • 1
  • 1
billinkc
  • 59,250
  • 9
  • 102
  • 159