I have following XML stored in a XML column (called
select Roles from MyTable where Roles.value('(/root/role)', 'varchar(max)') like 'StringToSearchFor'
These pages will show you more about how to query XML in T-SQL:
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())', '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/#!3/ae0d5/13