hi please any one can help i try to get xml file as ouput from sql procedure getting error i tried in different ways all are giving me same error <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?> my code is SQLprocedure is ALTER PROCEDURE spGenerateXML AS BEGIN Select * from (SELECT * FROM dbo.AppealsDataKPIOverride)XMLVariable FOR XML auto,elements,root('Call'),type END i did follow the steps 1. Create a Package level string variable called "XMLVariable" 2. In Connection Manager, create a New OLEDB Connection called "SQLDB" 3. In Connection Manager, create a New File Connection called "XMLFile" 4. On Control Flow tab a) Add Execute SQL Task b) Add Script Task c) Connect Execute SQL Task (parent) to Script Task (child) 5. Edit Execute SQL Task, General section a) Change Result Set to "XML" b) Set Connection to "SQLDB" c) Set SQLStatement to "EXEC spGenerateXML" 6. Edit Execute SQL Task, Result Set section a) Click Add button b) Set Result Name to 0 c) Set Variable Name to "User::XMLVariable" 7. Edit Script Task, Script Section a) Set ReadOnlyVariables to "XMLVariable" b) Click Design Script button c) Replace "Main" Subroutine with: Public Sub Main() 1. Create a Package level string variable called "XMLVariable" 2. In Connection Manager, create a New OLEDB Connection called "SQLDB" 3. In Connection Manager, create a New File Connection called "XMLFile" 4. On Control Flow tab a) Add Execute SQL Task b) Add Script Task c) Connect Execute SQL Task (parent) to Script Task (child) 5. Edit Execute SQL Task, General section a) Change Result Set to "XML" b) Set Connection to "SQLDB" c) Set SQLStatement to "spGenerateXML" 6. Edit Execute SQL Task, Result Set section a) Click Add button b) Set Result Name to 0 c) Set Variable Name to "User::XMLVariable" 7. Edit Script Task, Script Section a) Set ReadOnlyVariables to "XMLVariable" b) Click Design Script button c) Replace "Main" Subroutine with: Public Sub Main() Dim XMLString As String = Nothing Dim fs As StreamWriter = Nothing 'Dim strfilename As String = DirectCast(Dts.Connections("XMLFile.xml").AcquireConnection(Dts.Transaction), String) 'XMLString = Dts.Variables("XMLVariable").Value.ToString.Replace("<ROOT>", "").Replace("</ROOT>", "") XMLString = Dts.Variables( "XMLVariable").Value.ToString fs = New StreamWriter("test.xml", False) fs.Write(XMLString) fs.Close() Dts.TaskResult = Dts.Results.Success end sub creating xml but with above error. i user different way 2: using xsl file but still xmlvariable has same error

Could be XML Extensions for SQL Server issue. Perhaps need to re-install this part.Arthur My Blog