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 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
Leave a comment