| Below example will Helps you to get the data from XML datatype in sql. Here in below example i have created a variable table and add a XML datatype field in it then i have create a query to fetch the records.
---here i have declare a variable table
declare @tempTable table(XMlFieldCol xml)
--belwo query will insert some value in the table
insert into @tempTable values
('
Software Engineer
DBA
Software Tester
')
--now we apply exist statement to above variable table
declare @RoleField varchar(50)
set @RoleField='DBA'
---Now write query to find if it exist or not
select roleField from @tempTable where roleField.exist('/rootField/roleField/text()[. = sql:variable("@RoleField")]') = 1
we can also used like operator in above query for this we need to use contains keyword | | |