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
It answers all my question, what does
[1]
do in your answer?Great answer, I vote for this one, but string should be varchar I guess
@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 findingAlpha
in your sample xml. It will not find the row if you search forBeta
.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.
If the XML has a namespace, how do we query it?