How can I query a value in SQL Server XML column

I have following XML stored in a XML column (called Roles) in a SQL Server database.


I’d like to list all rows that have a specific role in them. This role passed by parameter.

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

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


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

We can query it like this:

select * from 

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

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

where RoleName like '%ga%'

You can check the SQL Fiddle here:!3/ae0d5/13


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s