Bistro Bistro - 1 year ago 105
SQL Question

How can I query a value in SQL Server XML column

I have following XML stored in a XML column (called

) in a SQL Server database.


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

Answer Source
  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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download