在如下所示的表架构中
CREATE TABLE [dbo].[Employee](
[EmployeeId] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Location] [nvarchar](50) NOT NULL,
[Skills] [xml] NOT NULL
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
<Skills><Skill>C#</Skill><Skill>ASP.NET</Skill><Skill>VB.NET</Skill></Skills>
比较区分大小写,因此您需要与 c# 和 C# 进行比较。在 SQL Server 2008 中您可以使用大写 http://msdn.microsoft.com/en-us/library/cc645590.aspx.
declare @T table
(
ID int identity,
Skills XML
)
insert into @T values
('<Skills><Skill>C#</Skill><Skill>ASP.NET</Skill><Skill>VB.NET</Skill></Skills>')
insert into @T values
('<Skills><Skill>CB.NET</Skill><Skill>ASP.NET</Skill><Skill>c#</Skill></Skills>')
insert into @T values
('<Skills><Skill>F#</Skill><Skill>ASP.NET</Skill><Skill>VB.NET</Skill></Skills>')
select ID
from @T
where Skills.exist('/Skills/Skill[contains(., "C#") or contains(., "c#")]') = 1
Result:
ID
-----------
1
2
Update:
这也会起作用。
select T.ID
from @T as T
cross apply T.Skills.nodes('/Skills/Skill') as X(N)
where X.N.value('.', 'nvarchar(50)') like '%C#%'
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)