Use the below code:
Roles.value('(/root/role)', 'varchar(max)') like 'StringToSearchFor'
These below links will show you how to query XML in T-SQL:
Querying XML fields using t-sql
Flattening XML Data in SQL Server
After playing with it a little bit more, I ended up with this amazing query that uses CROSS APPLY. This will search every row (role) for the value you put in your like expression.
If the table structure is like this:
create table MyTable (Roles XML)
insert into MyTable values
Then, you can use the query like this:
select * from
pref.value('(text())', 'varchar(32)') as RoleName
MyTable CROSS APPLY
Roles.nodes('/root/role') AS Roles(pref)
) as Result
where RoleName like '%ga%'
For more information, you can check the SQL Fiddle here: http://sqlfiddle.com/#!3/ae0d5/13
Industry-recognized Microsoft SQL Server database certifications can help you master SQL statements, queries and become proficient in SQL queries .