How can I query a value in SQL Server XML column

select  Rolesfrom  MyTablewhere  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 RoleNamefrom        MyTable CROSS APPLY       Roles.nodes('/root/role') AS Roles(pref))  as Resultwhere RoleName like '%ga%'

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

declare @T table(Roles xml)insert into @T values('<root>   <role>Alpha</role>   <role>Beta</role>   <role>Gamma</role></root>')declare @Role varchar(10)set @Role = 'Beta'select Rolesfrom @Twhere Roles.exist('/root/role/text()[. = sql:variable("@Role")]') = 1

If you want the query to work as where col like '%Beta%' you can use contains

declare @T table(Roles xml)insert into @T values('<root>   <role>Alpha</role>   <role>Beta</role>   <role>Gamma</role></root>')declare @Role varchar(10)set @Role = 'et'select Rolesfrom @Twhere Roles.exist('/root/role/text()[contains(., sql:variable("@Role"))]') = 1

if your field name is Roles and table name is table1 you can use following to search

DECLARE @Role varchar(50);SELECT * FROM table1WHERE Roles.exist ('/root/role = sql:variable("@Role")') = 1