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.
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
[1] do in your answer?
[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.
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
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
like? forexample /root/role like .....value('(/root/role)[1]', 'varchar(max)') like '%yourtext%' instead of exists as Leniel explained@Role.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%'
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.
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
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'
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
