0 votes
1 view
in SQL by (20.3k points)

I have following XML stored in an 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.

1 Answer

0 votes
by (40.4k points)

Use the below code:






  Roles.value('(/root/role)[1]', '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())[1]', '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

Welcome to Intellipaat Community. Get your technical queries answered by top developers !