CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008
Ehsan Tavakoli
Ehsan Tavakoli

Reputation: 454

Finding and Reading a XML file using MS SQL

I have an SQL script which I want to run on multiple databases. The script runs a couple of update and insert statements and I also want to open and parse an xml file on different paths.
The issue I have is that I know where the file I want to open is (the directory) but I don't know its name (the name is random) but the extension is always .profile (which is a xml file) and there is only one file in each directory.
I wonder how I can open a XML/profile file without knowing its exact name using MS SQL.

Upvotes: 1

Views: 151

Answers (3)

Gidil
Gidil

Reputation: 4137

Try something along the lines of this:

DECLARE @output NVARCHAR(MAX) 

CREATE TABLE #OUTPUT 
  ( 
     OUTPUT VARCHAR(255) NULL 
  ) 

INSERT #OUTPUT 
EXEC @output = XP_CMDSHELL 
  'DIR "C:\temp\*.profile" /B ' 

SELECT * 
FROM   #OUTPUT 

DROP TABLE #OUTPUT

Upvotes: 1

Kahn
Kahn

Reputation: 1660

As explained here (and that's just one way), you can access disk contents from SQL Server, provided your permissions are working fine.

IIRC, the following options need to be enabled. However, you need them anyway to access files from SQL Server.

EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO

Upvotes: -1

David Brabant
David Brabant

Reputation: 43619

As far as I understand your question correctly:

declare @files table (ID int IDENTITY, fileName varchar(max))
insert into @files execute xp_cmdshell 'dir <yourdirectoryhere>\*.profile /b'
declare @fileName varchar(max)
select top 1 @fineName = fileName * from @files

does what you want but is based on calling xp_cmdshell and it's usually a very bad idea to use it.

Upvotes: 2

Related Questions