How can I query a value in SQL Server XML column
SqlSql ServerXmlXquerySql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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
Solution 3 - Sql
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
Solution 4 - Sql
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%'
Solution 5 - Sql
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.
Solution 6 - Sql
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
Solution 7 - Sql
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'
Solution 8 - Sql
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
Solution 9 - Sql
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