CODEWITHSUNDEEP
sqlsql-serverxml
user3010406
user3010406

Reputation: 579

SQL Syntax Error When Selecting From Config File

I am using the following T-SQL in order to select a value from a config file on my local machine. However, on the line,

SELECT xmlData.Col.value('Setting','varchar(max)')

I receive a syntax error. I get the config file correctly, I can see that in the first select, however cannot correctly get the value from the specific node in the config file (in this case, a key within the appsettings of the config file). 

declare @table table (Value XML)

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

select * from @table

DECLARE @TEMPSTRING AS nvarchar(100)
SET @TEMPSTRING = (select Value.value('filelocation[1]', 'varchar(100)')
from @table)

Select @TEMPSTRING  
DECLARE @XML AS XML
SET @XML = CAST(@TEMPSTRING as XML)
SELECT xmlData.Col.value('I AM TRYING TO GET THE SPECIFIC VALUE HERE','varchar(max)')
FROM @XML.nodes('//configuration/appSettings') 
xmlData(Col);

Upvotes: 0

Views: 156

Answers (1)

Jose Basilio
Jose Basilio

Reputation: 51518

If you are trying to extract a single value from the appSettings node, you would need to write it like this:

SELECT xmlData.Col.value('@value','varchar(max)') FROM @XML.nodes('//configuration/appSettings/add[@key="THE SETTING NAME"]') xmlData(Col);

Furthermore you can get all the keys and values with this select statement SELECT xmlData.Col.value('@key','varchar(max)')[Key], xmlData.Col.value('@value','varchar(max)')[Value] FROM @XML.nodes('//configuration/appSettings/add')xmlData(Col);

Upvotes: 1

Related Questions