Read Text File into String Variable

Reads an entire text file into a string variable.

Download Chilkat FileAccess ActiveX (freeware)

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @fac int
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @fileText nvarchar(4000)

    --  Reads the entire contents of a text file into a string variable.
    --  The 2nd arg specifies the character encoding used for
    --  the contents of the text file.
    EXEC sp_OAMethod @fac, 'ReadEntireTextFile', @fileText OUT, 'hamlet.xml', 'utf-8'


    PRINT @fileText
END
GO