How can I query a value in SQL Server XML column

Question

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

<root>

   <role>Alpha</role>

   <role>Beta</role>

   <role>Gamma</role>

</root>

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

Answer 1

Use the below code:

select

  Roles

from

  MyTable

where

  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

EDIT

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

('<root>

   <role>Alpha</role>

   <role>Gamma</role>

   <role>Beta</role>

</root>')

Then, you can use the query like this:

select * from 

(select 

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

from 

       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 .