3

In a table schema like below

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

  • How would i get Employees having C#(case insensitive) programming skills assuming the xml saved in the Skills columns is as below.

  • Could you advice on other functions would help me filter, sort when using xml data type columns

<Skills><Skill>C#</Skill><Skill>ASP.NET</Skill><Skill>VB.NET</Skill></Skills>

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
Deeptechtons
  • 10,945
  • 27
  • 96
  • 178

1 Answers1

3

The comparison is case sensitive so you need to compare against both c# and C#. In SQL Server 2008 you can use upper-case.

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:

This will also work.

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#%'
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • is the comparison case sensitive by default?,is it possible to use `contains` function in where clause? – Deeptechtons Jan 05 '12 at 10:45
  • @Deeptechtons - Yes, it is case sensitive by default and you can use contains like this. `'/Skills/Skill[contains(upper-case(.), "C#")]'` – Mikael Eriksson Jan 05 '12 at 10:53
  • like this? `select * from Employee where Skills.query('/Skills/Skill[contains(upper-case(.), "C#")]')` weird i also get error `There is no function '{http://www.w3.org/2004/07/xpath-functions}:upper-case()' ` – Deeptechtons Jan 05 '12 at 10:59
  • @Deeptechtons Close. Use `exist` instead of `query` just as in the query in the answer. – Mikael Eriksson Jan 05 '12 at 11:00
  • @Deeptechtons Sorry about that. upper-case was introduced in SQL Server 2008. – Mikael Eriksson Jan 05 '12 at 11:02
  • guess xml is not childs play, i am constantly being hit with errors. the docs at msdn are too complex even to understand how the syntax works duuuhh `select * from employee where skills.exist('contains(/Skills/Skill,"C#")')` throws this `An expression of non-boolean type specified in a context where a condition is expected, near ')'.` at my face – Deeptechtons Jan 05 '12 at 11:13
  • @Deeptechtons - You are missing the final `= 1` in the where clause. – Mikael Eriksson Jan 05 '12 at 11:14
  • 1
    `select * from employee where skills.exist('/Skills/Skill[contains(., "C#")]') = 1` – Mikael Eriksson Jan 05 '12 at 11:16
  • Yes it is, finally got hold of the syntax and how they work.Thanks once again Mikael if you don't mind what is the basis of your work a DB Administrator? u seem to xml expert to me :) – Deeptechtons Jan 05 '12 at 11:21
  • @Deeptechtons - I don't mind :). I'm a developer/architect developing software for the Health Care industry using Delphi and SQL Server. – Mikael Eriksson Jan 05 '12 at 11:32