How can I query a value in SQL Server XML column

Question

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.

Moneyer

Answer 1

select
  Roles
from
  MyTable
where
  Roles.value('(/root/role)[1]', 'varchar(max)') like 'StringToSearchFor'

In case your column is not XML, you need to convert it. You can also use other syntax to query certain attributes of your XML data. Here is an example...

Let's suppose that data column has this:

<Utilities.CodeSystems.CodeSystemCodes iid="107" CodeSystem="2" Code="0001F" CodeTags="-19-"..../>

... and you only want the ones where CodeSystem = 2 then your query will be:

select 
  [data] 
from
  [dbo].[CodeSystemCodes_data]
  
where
  CAST([data] as XML).value('(/Utilities.CodeSystems.CodeSystemCodes/@CodeSystem)[1]', 'varchar(max)') = '2'

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

EDIT

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
('<root>
   <role>Alpha</role>
   <role>Gamma</role>
   <role>Beta</role>
</root>')

We can query it 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%'

You can check the SQL Fiddle here: http://sqlfiddle.com/#!18/dc4d2/1/0

Fasces

It answers all my question, what does [1] do in your answer? – Moneyer

Great answer, I vote for this one, but string should be varchar I guess – Barbur

@Moneyer Asking about [1] was a really good question. It means that you pick the first role value from the XML and that means that this will only work for finding Alpha in your sample xml. It will not find the row if you search for Beta. – Miocene

In my case, I had to query nodes with specific attribute value. This answer was the lead to my solution. I just had to put double quotes around the attribute value. – Titleholder

If the XML has a namespace, how do we query it? – Deraign

Getting - "Cannot call methods on nvarchar(max)". Can anyone please suggest – Warden

@AbhilashV you need to convert that to XML: CAST([data] as XML).value... – Carnify

Answer 2

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

Miocene

Answer 3

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

Barbur

this is good, is here any way to search the using like? forexample /root/role like .... – Moneyer

use .value('(/root/role)[1]', 'varchar(max)') like '%yourtext%' instead of exists as Leniel explained – Barbur

Have you tried this? It finds everything, regardless of what you put in @Role. – Miocene

Answer 4

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%'

Comply

We're not supposed to search via string manipulation, as that would result in way too slow searches – Fatness

Answer 5

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.

Jiles

Answer 6

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

Cassandracassandre

Answer 7

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'

Jackfruit

What if XML does not contain Namespace definition ? – Metropolitan

Answer 8

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

Hillman

Answer 9

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

Maggie