How to read config file key in SQL Server 2008 sto

Question

I have a settings.config file in my c://config

settings.config has this contents:

<filelocation>d://mydata</filelocation>

Now I want to get d://mydata file path in my SQL Server 2008 stored procedure, which will take this location as input for file creation path.

Please suggest!!

Answer 1

Simplified example but you could do something like this (assuming you can read the file from SQL Server):

declare @table table (Value XML)

insert @table
select a.* from openrowset (bulk 'C:\config', single_clob) a

select * from @table

select Value.value('filelocation[1]', 'varchar(100)')
from @table

Extend based on structure of your file.