Блог

Это моя таблица

 BasketId(int)   BasketName(varchar) BasketFruits(xml)
1       Gold        <FRUITS><FID>1</FID><FID>2</FID><FID>3</FID><FID>4</FID><FID>5</FID><FID>6</FID></FRUITS>
2       Silver      <FRUITS><FID>1</FID><FID>2</FID><FID>3</FID><FID>4</FID></FRUITS>
3       Bronze      <FRUITS><FID>3</FID><FID>4</FID><FID>5</FID></FRUITS>
  

Мне нужно выполнить поиск корзины, которая имеет FID значения 1 и 3
так что в этом случае я бы получил золото и серебро

Хотя я добрался до результата, в котором я могу искать ЕДИНСТВЕННОЕ значение FID, например 1, используя этот код:

 declare @fruitId varchar(10);
set @fruitId=1;
select * from Baskets
WHERE BasketFruits.exist('//FID/text()[contains(.,sql:variable("@fruitId"))]') = 1
  

ЕСЛИ бы это был T-SQL, я бы использовал предложение IN следующим образом

 SELECT * FROM Baskets where FID in (1,3)
  

Приветствуется любая помощь / обходной путь…

Первым вариантом было бы добавить другое предложение exist the where.

 declare @fruitId1 int;
set @fruitId1=1;

declare @fruitId2 int;
set @fruitId2=3;

select *
from @Test
where
  BasketFruits.exist('/FRUITS/FID[.=sql:variable("@fruitId1")]')=1 and
  BasketFruits.exist('/FRUITS/FID[.=sql:variable("@fruitId2")]')=1
  

Другой версией было бы использовать обе переменные в инструкции xquery, подсчитывая совпадения.

 select * 
from @Test
where BasketFruits.value(
  'count(distinct-values(/FRUITS/FID[.=(sql:variable("@fruitId1"),sql:variable("@fruitId2"))]))', 'int') = 2
  

Два приведенных выше запроса будут работать просто отлично, если вы знаете, сколько параметров FID вы собираетесь использовать при написании запроса. Если вы находитесь в ситуации, когда количество идентификаторов FID варьируется, вы могли бы использовать что-то вроде этого вместо этого.

 declare @FIDs xml = '<FID>1</FID><FID>3</FID>'

;with cteParam(FID) as
(
  select T.N.value('.', 'int')
  from @FIDs.nodes('FID') as T(N)
)  
select T.BasketName
from @Test as T
  cross apply T.BasketFruits.nodes('/FRUITS/FID') as F(FID)
  inner join cteParam as p
    on F.FID.value('.', 'int') = P.FID
group by T.BasketName
having count(T.BasketName) = (select count(*) from cteParam)
 
  

Создайте переменную @FIDs в формате XML для хранения значений, которые вы хотите использовать в запросе.

Вы можете протестировать последний запрос здесь:https://data.stackexchange.com/stackoverflow/q/101600/relational-division-with-xquery

Это немного сложнее, чем я надеялся, но это решение работает.

По сути, я использую CTE (общее табличное выражение), которое разбивает таблицу и перекрестно соединяет все значения из <FID> узлов с именами корзин.

Из этого CTE я выбираю те корзины, которые содержат как значение 1 , так и 3 .

 DECLARE @Test TABLE (BasketID INT, BasketName VARCHAR(20), BasketFruits XML)

INSERT INTO @TEST
VALUES(1, 'Gold', '<FRUITS><FID>1</FID><FID>2</FID><FID>3</FID><FID>4</FID><FID>5</FID><FID>6</FID></FRUITS>'),
(2, 'Silver', '<FRUITS><FID>1</FID><FID>2</FID><FID>3</FID><FID>4</FID></FRUITS>'),
(3, 'Bronze', '<FRUITS><FID>3</FID><FID>4</FID><FID>5</FID></FRUITS>')

;WITH IDandFID AS
(
SELECT
    t.BasketID,
    t.BasketName,
    FR.FID.value('(.)[1]', 'int') AS 'FID'
FROM @Test t
CROSS APPLY basketfruits.nodes('/FRUITS/FID') AS FR(FID)
)
SELECT DISTINCT 
    BasketName
FROM 
    IDandFID i1
WHERE 
    EXISTS(SELECT * FROM IDandFID i2 WHERE i1.BasketID = i2.BasketID AND i2.FID = 1)
    AND EXISTS(SELECT * FROM IDandFID i3 WHERE i1.BasketID = i3.BasketID AND i3.FID = 3)
  

Выполняя этот запрос, я получаю ожидаемый результат:

 BasketName
----------
Gold
Silver
  

Это слишком тривиально?

 SELECT * FROM Baskets WHERE BasketFruits LIKE '%<FID>1</FID>%' AND BasketFruits LIKE '%<FID>3</FID>%'