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.
Add a comment
|
3 Answers
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.
Comments
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
Comments
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
Comments
