在 SQL 中搜索 xml 列中的多个值

这是我的桌子

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 这样在这种情况下我会得到Gold and Silver

虽然我已经达到了可以搜索单个 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)

任何帮助/解决方法表示赞赏...

第一个选项是添加另一个存在的 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 https://data.stackexchange.com/stackoverflow/q/101600/relational-division-with-xquery