xml + Sql Server, подскажите, как лучше реализовать?

Вобщем у меня такая ситуация:
В программе у класса Video есть список ключевых слов - List<string> keywords
В Базе данных тоже есть таблица Video, столбец ключевых слов(тоже keywords) является типом xml.

Нужно написать хранимую процедуру(pSearchVideos), которая принимает на вход значения всех полей класса Video (могут быть null), и ищет соответствующие записи. Все поля, которые не null, должны полностью соответствовать полям искомой записи. И все слова в keywords, должны присутствовать в наборе keywords искомой записи.
формат xml столбца: <Keywords><Keyword>key1</Keyword><Keyword>key2</Keyword></Keywords>

Вопрос:
Как написать запрос, чтобы он искал соответствие всех ключевых слов?

например:
на вход пришло два слова "science, math"
в таблице есть три записи:
У 1-ой keywords: science
У 2-ой keywords: math, science
У 3-ьей keywords: science, biology, math

В результате должы вернуться 2-ая и 3-ья записи.

PS. Структуру БД менять нельзя.

Версия какая?

Sql Server 2005

Может можно это сделать двумя циклами?

Просто я в sql не сильно разбираюсь ((

если кто знает, напишите, пожалуйста, хотя бы в двух словах.

CREATE TABLE [dbo].[Video](
	[VideoID] [int] IDENTITY(1,1) NOT NULL,
	[KeyWords] [xml] NOT NULL,
 CONSTRAINT [PK_Video] PRIMARY KEY CLUSTERED 
(
	[VideoID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO 

INSERT INTO dbo.Video (Keywords)
SELECT CAST(N'<Keywords> <Keyword>aaa</Keyword> <Keyword>bbb</Keyword> </Keywords>' AS XML )
UNION ALL 
SELECT CAST( N'<Keywords> <Keyword>ccc</Keyword> </Keywords>' AS XML )
UNION ALL 
SELECT CAST(N'<Keywords> <Keyword>bbb</Keyword> <Keyword>ddd</Keyword> </Keywords>'   AS XML )
UNION ALL 
SELECT CAST(N'<Keywords> <Keyword>aaa</Keyword><Keyword>ccc</Keyword> </Keywords>'   AS XML )
GO 

DECLARE @temp NVARCHAR(MAX)
SET @temp = N'aaa'
SELECT v.VideoID 
FROM dbo.Video v 
WHERE v.KeyWords.exist(N'/Keywords/Keyword[.= sql:variable("@temp")]') = 1

Так например можно . Чтобы сделать множественный поиск делайте UNION, EXCEPT , INTERSECT между поисками по кейворду.
Посмотрите xml индексы .

У вас будут просто жуткие проблемы. Работать с типом XML больше рекомендуется при небольшом наборе.
Если у вас таблица Video будет большая, то без пляски начнутся жуткие проседания сервера.
Во вторых, у вас полное сканирование таблицы и вам придётся играться с XML индексами.

Судя по всему была создана стратегическая ошибка проектирования, скорее всего от жадной лени. Желательная структура системы:

CREATE TABLE [dbo].[KeyWord](
	 [KeyWord]	Int		IDENTITY
	 CONSTRAINT [PK_Keywords] PRIMARY KEY
	,[Name]		VarChar(250)]	NOT NULL
)
GO
CREATE TABLE [dbo].[Video](
	 [Video]	Int	IDENTITY
	 CONSTRAINT [PK_Video] PRIMARY KEY
	,... Data ...
)
GO
CREATE TABLE [dbo].[VideoKeyWord](
	 [Video]	Int
	 CONSTRAINT [FK_VideoKeyWord_Video] REFERENCE [dbo].[Video] ([Video])
	,[KeyWord]	Int
	 CONSTRAINT [FK_VideoKeyWord_KeyWord] REFERENCE [dbo].[KeyWord] ([KeyWord])
	,CONSTRAINT [PK_VideoKeyWord] PRIMARY KEY (
	 	 [Video]
		,[KeyWord]
	)
	,CONSTRAINT [UK_VideoKeyWord] UNIQUE (
	 	 [KeyWord]
		,[Video]
	)
)
GO

Из-за того что есть такой тип данных как XML, это не значит, что надо им пользоваться где не попадя.

Далее запрос становится очевидным.
Вам осталось выбрать стратегию передачи табличных данных в вашу функцию. Тут можно и XML-ем воспользоваться. Думаю вам не составит труда сериализировать ваш List<string> через XmlSerializer.

А затем что-то типа:

CREATE FUNCTION [dbo].[fnVideoByKeyWords] (
	@KeyWord	XML
) RETURNS TABLE AS RETURN (
	SELECT	 V.Video
		,V. ... Data ...
	FROM	dbo.Video	V
	WHERE	Exists(	SELECT	*
			FROM	     @KeyWord.nodes('/Words/Key')	X(K)
				JOIN dbo.KeyWord			K ON K.Name = X.K.value('@Name')	-- X.K.value('text()')
				JOIN dbo.VideoKeyWord			M ON M.KeyWord = K.KeyWord
			WHERE	V.Video = M.Video)
)
GO

Далее план запроса в зубы для проверки/допиливания.
На синтаксические ошибки не пинайте - не ставил сиквел на ubuntu ... лень. :)

Леша777, Спасибо Большое! =)
Только вот множественный поиск пока не получилось сделать ((
Хотел как-нибудь INTERSECT между ними сделать и проверить количество пересечения с количеством входного параметра, но не знаю как этот INTERSERT для xml сделать ((

Mnior,
Вся проблема в том что менять структуру никак нельзя ((. Была б моя воля я тоже примерно такую схему сделал бы, но нельзя.

На счет индексов уже не мне думать, а тем кто эту схему сделал. Мне только хранимку написать нужно срочно.

PS. Вот исходные Requerments:
spSearchVideos
Searches the videos by the specified criteria such as school ID, whether it's active, title, description, keywords, video type, and external ID. If multiple searching parameters are specified, the "AND" logic will be applied.
INPUT
The criteria such as school ID, whether it's active, title, description, keywords, video type, video provider name and external ID; The limit on the number of result records.
OUTPUT
A list of matching videos. The Video’s information including ID, EntityID, school ID, whether active, title, description, keywords (XML), video type (integer), and external ID.
IMPLEMENTATION NOTES
For title and description, substring matching (“LIKE” statement) should be used. For keywords, the XML should be parsed and for each keyword, exact matching should be used. For all other fields, exact matching should be used.

Вот неоптимальный вариант:

-- IF Exists(SELECT 1 FROM @KeyWords.nodes('/Keywords/Keyword')X(K))

SELECT	V.*
FROM	dbo.Video	V
WHERE	NOT Exists (
		SELECT	*
		FROM	           @KeyWords.nodes('/Keywords/Keyword')X(K)
			LEFT JOIN V.KeyWords.nodes('/Keywords/Keyword')K(W) ON K.W.value('text()') = X.K.value('text()')
		WHERE	K.W IS NULL	-- .value('text()')
	)

Можно ещё через XQuery попробывать.

Оберните запрос в функцию (инлайн или мулистэймент).

Динамический sql использовать можно ? Если да : Под каждый кейворд ее запуск :

SELECT 
FROM dbo.fnSearchKeyWord()
INTERSECT 
SELECT 
FROM dbo.fnSearchKeyWord()
.... 
INTERSECT 
FROM dbo.fnSearchKeyWord()

Или можно временную таблицу или переменную . При поиске 1 раз все что нашлось в нее записываем. При последующих итерациях

DELETE t
FROM @Temp t 
LEFT JOIN  dbo.fnSearchKeyWord(@word) w 
          ON t.VideoID = w.VideoID 
WHERE t.VideoID IS NULL 

А потом джоиним @Temp c основной таблицей .

Ерунду написал про табличную переменную :

t.VideoID IS NULL  заменить на  w.VideoID IS NULL

Или NOT EXISTS можно использовать.