How to get a particular attribute from XML element in SQL Server

Try using the .value function instead of .query:

SELECT 
  xmlCol.value('(/container/param[@name="paramB"]/@value)[1]', 'varchar(50)') 
FROM  
  LogTable

The XPath expression could potentially return a list of nodes, therefore you need to add a [1] to that potential list to tell SQL Server to use the first of those entries (and yes – that list is 1-based – not 0-based). As second parameter, you need to specify what type the value should be converted to – just guessing here.

Marc