[sql] 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.

<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.

You could do the following

declare @role varchar(100) = 'Alpha'
select * from xmltable where convert(varchar(max),xmlfield) like '%<role>'+@role+'</role>%'

Obviously this is a bit of a hack and I wouldn't recommend it for any formal solutions. However I find this technique very useful when doing adhoc queries on XML columns in SQL Server Management Studio for SQL Server 2012.

I used the below statement to retrieve the values in the XML in the Sql table

with xmlnamespaces(default 'http://test.com/2008/06/23/HL.OnlineContract.ValueObjects')
select * from (
select
            OnlineContractID,
            DistributorID,
            SponsorID,
    [RequestXML].value(N'/OnlineContractDS[1]/Properties[1]/Name[1]', 'nvarchar(30)') as [Name]
   ,[RequestXML].value(N'/OnlineContractDS[1]/Properties[1]/Value[1]', 'nvarchar(30)') as [Value]
     ,[RequestXML].value(N'/OnlineContractDS[1]/Locale[1]', 'nvarchar(30)') as [Locale]
from [OnlineContract]) as olc
where olc.Name like '%EMAIL%' and olc.Value like '%EMAIL%' and olc.Locale='UK EN'

if your field name is Roles and table name is table1 you can use following to search

DECLARE @Role varchar(50);
SELECT * FROM table1
WHERE Roles.exist ('/root/role = sql:variable("@Role")') = 1

You can query the whole tag, or just the specific value. Here I use a wildcard for the xml namespaces.

declare @myDoc xml
set @myDoc = 
'<Root xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://stackoverflow.com">
    <Child>my value</Child>
 </Root>'

select @myDoc.query('/*:Root/*:Child') -- whole tag
select @myDoc.value('(/*:Root/*:Child)[1]', 'varchar(255)') -- only value

In case you want to find other node besides "Alpha", the query should be something like this:

select Roles from MyTable where Roles.exist('(/*:root/*:role[contains(.,"Beta")])') = 1

declare @T table(Roles xml)

insert into @T values
('<root>
   <role>Alpha</role>
   <role>Beta</role>
   <role>Gamma</role>
</root>')

declare @Role varchar(10)

set @Role = 'Beta'

select Roles
from @T
where Roles.exist('/root/role/text()[. = sql:variable("@Role")]') = 1

If you want the query to work as where col like '%Beta%' you can use contains

declare @T table(Roles xml)

insert into @T values
('<root>
   <role>Alpha</role>
   <role>Beta</role>
   <role>Gamma</role>
</root>')

declare @Role varchar(10)

set @Role = 'et'

select Roles
from @T
where Roles.exist('/root/role/text()[contains(., sql:variable("@Role"))]') = 1

I came up with a simple work around below which is easy to remember too :-)

select * from  
(select cast (xmlCol as varchar(max)) texty
 from myTable (NOLOCK) 
) a 
where texty like '%MySearchText%'

Useful tip. Query a value in SQL Server XML column (XML with namespace)

e.g.

Table [dbo].[Log_XML] contains columns Parametrs (xml),TimeEdit (datetime)

e.g. XML in Parametrs:

<ns0:Record xmlns:ns0="http://Integration"> 
<MATERIAL>10</MATERIAL> 
<BATCH>A1</BATCH> 
</ns0:Record>

e.g. Query:

select
 Parametrs,TimeEdit
from
 [dbo].[Log_XML]
where
 Parametrs.value('(//*:Record/BATCH)[1]', 'varchar(max)') like '%A1%'
 ORDER BY TimeEdit DESC