Actually you're close to your goal, you just need to use nodes() method to split your rows and then get values:

select
    s.SqmId,
    m.c.value('@id', 'varchar(max)') as id,
    m.c.value('@type', 'varchar(max)') as type,
    m.c.value('@unit', 'varchar(max)') as unit,
    m.c.value('@sum', 'varchar(max)') as [sum],
    m.c.value('@count', 'varchar(max)') as [count],
    m.c.value('@minValue', 'varchar(max)') as minValue,
    m.c.value('@maxValue', 'varchar(max)') as maxValue,
    m.c.value('.', 'nvarchar(max)') as Value,
    m.c.value('(text())[1]', 'nvarchar(max)') as Value2
from sqm as s
    outer apply s.data.nodes('Sqm/Metrics/Metric') as m(c)

sql fiddle demo

select
  Roles
from
  MyTable
where
  Roles.value('(/root/role)[1]', 'varchar(max)') like 'StringToSearchFor'

In case your column is not XML, you need to convert it. You can also use other syntax to query certain attributes of your XML data. Here is an example...

Let's suppose that data column has this:

<Utilities.CodeSystems.CodeSystemCodes iid="107" CodeSystem="2" Code="0001F" CodeTags="-19-"..../>

... and you only want the ones where CodeSystem = 2 then your query will be:

select 
  [data] 
from
  [dbo].[CodeSystemCodes_data]
  
where
  CAST([data] as XML).value('(/Utilities.CodeSystems.CodeSystemCodes/@CodeSystem)[1]', 'varchar(max)') = '2'

These pages will show you more about how to query XML in T-SQL:

Querying XML fields using t-sql

Flattening XML Data in SQL Server

EDIT

After playing with it a little bit more, I ended up with this amazing query that uses CROSS APPLY. This one will search every row (role) for the value you put in your like expression...

Given this table structure:

create table MyTable (Roles XML)

insert into MyTable values
('<root>
   <role>Alpha</role>
   <role>Gamma</role>
   <role>Beta</role>
</root>')

We can query it like this:

select * from 

(select 
       pref.value('(text())[1]', 'varchar(32)') as RoleName
from 
       MyTable CROSS APPLY

       Roles.nodes('/root/role') AS Roles(pref)
)  as Result

where RoleName like '%ga%'

You can check the SQL Fiddle here: http://sqlfiddle.com/#!18/dc4d2/1/0

Given that the XML field is named 'xmlField'...

SELECT 
[xmlField].value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
[xmlField].value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]

I believe the reason you're getting these results is because the namespace for the xml is not the same as in the select statements. I've altered your example to briefly explain each case.

declare @xmlMessage xml

set @xmlMessage ='<pmsg:Messages xmlns:pmsg="pmsg">
    <pmsg:Message Info="blah"/>
    <pmsg:Message Info="blah"/>
</pmsg:Messages>'

;WITH XMLNAMESPACES('pmsg' as pmsg)
SELECT msgs.msg.value('@Info', 'nvarchar(max)') FROM @xmlMessage.nodes('pmsg:Messages/pmsg:Message') msgs(msg); 
-- return twos rows 'blah'

The nodes() expression provides a set of two pmsg:Message nodes, and the select reads the value of @Info from each row.

;WITH XMLNAMESPACES('pmsg' as pmsg)
SELECT @xmlMessage.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)'); 
--Returns a single rowset of one row containing 'blah'

This will only return a single row 'blah' because the [1] in the query restricts to the first item found. Actually in this form the [1] is required. SQL will throw exeception of you dont include it becuase the expression must evaluate to a single value.

;WITH XMLNAMESPACES('pmsg' as pmsg)
SELECT t.x.value('(/pmsg:Messages/pmsg:Message/@Info)[1]', 'nvarchar(max)') as INFO FROM @xmlMessage.nodes('/*') t(x);
--Returns a rowset of one row containing 'blah'

Like the previous example, the xpath query evaluates to a single item and the nodes() expresson provides a row set on the top level node: