June 20, 2011 at 4:37 am
How can I save the rusults of a query that pruduces XML to a file? Currently I just select @myXML then I open it in the results window and do a file save as. I have seen articles referencing xp_Cmdshell, but that is not available to me.
Thank you!
June 20, 2011 at 9:02 pm
Here is the code for a stored procedure that I use to write to text files on disk. I "borrowed" the code from another forum post.
-----------------------
CREATE PROCEDURE PC_AppendToFile(@FileName varchar(255), @Text1 varchar(2000)) AS
DECLARE @FS int, @OLEResult int, @FileID int
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'
--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'WriteLine'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
-----------------------
It takes as parameters the full UNC path to an existing text file and also up to 2000 ASCII characters. It can easily be modified to Varchar(8000). While that still may not be large enough for your file, there may be a way you can take "chunks" of the XML and write it to the text file in pieces. The code will APPEND to the end of the file.
If you do a bit of research on the 'Scripting.FileSystemObject' you will discover how to create a text file from scratch. I am have to get back to work now. I hope this bit of information helps you.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply