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.

Leniel Maccaferri · Accepted Answer · 2020-11-13T16:55:37.860

233

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

edited Nov 13 '20 at 16:55

answered Apr 27 '12 at 04:09

Leniel Maccaferri

100,159
46
371
480

8

It answers all my question, what does `[1]` do in your answer? – Bistro Apr 27 '12 at 04:12
1

Great answer, I vote for this one, but string should be varchar I guess – AaA Apr 27 '12 at 04:13
10

@Bistro 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`. – Mikael Eriksson Apr 27 '12 at 05:37
1

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. – John N Jan 20 '14 at 18:31
If the XML has a namespace, how do we query it? – FMFF May 10 '18 at 22:35
Getting - "Cannot call methods on nvarchar(max)". Can anyone please suggest – Abhilash V Jul 05 '18 at 09:36
@AbhilashV you need to convert that to XML: `CAST([data] as XML).value...` – Mauricio Gracia Gutierrez Nov 13 '20 at 15:51

Mikael Eriksson · Answer 2 · 2014-07-28T09:17:22.470

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

AaA · Answer 3 · 2012-04-27T04:11:14.120

13

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

edited Apr 27 '12 at 04:11

answered Apr 27 '12 at 04:05

AaA

3,600
8
61
86

this is good, is here any way to search the using `like`? `forexample /root/role like ....` – Bistro Apr 27 '12 at 04:06
2

use `.value('(/root/role)[1]', 'varchar(max)') like '%yourtext%'` instead of `exists` as Leniel explained – AaA Apr 27 '12 at 04:16
4

Have you tried this? It finds everything, regardless of what you put in `@Role`. – Mikael Eriksson Apr 27 '12 at 05:40

score 11 · Answer 4 · answered Oct 04 '16 at 19:12

11

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

answered Oct 04 '16 at 19:12

Sagar

579
5
7

1

We're not supposed to search via string manipulation, as that would result in way too slow searches – Malcolm Salvador Aug 02 '19 at 19:08

score 6 · Answer 5 · answered Sep 08 '15 at 23:06

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.

score 2 · Answer 6 · answered Sep 18 '18 at 15:29

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

score 1 · Answer 7 · edited Mar 07 '14 at 12:04

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'

What if XML does not contain Namespace definition ? – Muflix Feb 12 '16 at 11:20 — Muflix, Feb 12 '16 at 11:20

FrankyHollywood · Answer 8 · 2020-07-10T09:05:03.497

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

score 0 · Answer 9 · answered Dec 03 '20 at 18:08

0

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

answered Dec 03 '20 at 18:08

user2365332

21
1

How can I query a value in SQL Server XML column

9 Answers9

Linked

Related