Read a file using transact-sql.

Introduction

To read the content of a file (for example read the text data from a *.txt, doc, rtf etc file) using transact-sql statement. We are familiar with the use of the seven extended stored procedures[^], especially when we want to read / write file and ole automation as well. However; I would like to show you how easy to read a file without using of the seven extended stored procedures. We will use only the master.dbo.xp_cmdshell stored procedure for doing this. For more information about master.dbo.xp_cmdshell stored procedure can be found at this link.[^]

A sample sql script is given below:

Script

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--      Author:	Md. Marufuzzaman.
-- Create date: 
-- Description:	Get the content from files. 
-- =============================================
-- EXEC [dbo].[spGetText] @Path = 'C:\MyTextFile.txt'

ALTER PROCEDURE [dbo].[spGetText] 
(
	@Path	VARCHAR(500)
)
AS
BEGIN

DECLARE @Command VARCHAR(255)
SELECT @Path = ISNULL(@Path,'C:\Windows\System32\license.rtf') 
PRINT 'Path: ' + @Path

CREATE TABLE #Xml(dataRow VARCHAR(MAX))
	IF @Path IS NOT NULL AND LEN(@Path) > 10
		BEGIN
			SELECT @Command = 'type ' + @Path
			INSERT INTO [dbo].[#Xml] 
					EXEC master.dbo.xp_cmdshell @Command
		END
	IF @@ROWCOUNT <> 0
		BEGIN
			SELECT * FROM [dbo].[#Xml]
		END
			
DROP TABLE #Xml

END
GO