在 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 的篮子所以在这种情况下我会得到金和银

I need to search for the basket which has FID values 1 and 3 so that in this case i would get Gold and Silver

虽然我已经达到了可以搜索像 1 这样的 SINGLE FID 值的结果使用此代码:

Although i've reached to the result where i can search for a SINGLE FID value like 1 using this code:

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

如果是 T-SQL，我会像这样使用 IN 子句

HAD it been T-SQL i would have used the IN Clause like this

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

感谢任何帮助/解决方法...

Any help/workaround appreciated...

推荐答案

第一个选项是添加另一个存在的 where 子句.

First option would be to add another exist the where clause.

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 语句中使用这两个变量，计算点击次数.

Another version would be to use both variables in the xquery statement, counting the hits.

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

如果您知道在编写查询时将使用多少 FID 参数，上面的两个查询就可以正常工作.如果您处于 FID 数量不同的情况，您可以使用类似的方法.

The two queries above will work just fine if you know how many FID parameters you are going to use when you write the query. If you are in a situation where the number of FID's vary you could use something like this instead.

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 以保存要在查询中使用的值.

Build the @FIDs variable as an XML to hold the values you want to use in the query.

您可以在这里测试最后一个查询:https:///data.stackexchange.com/stackoverflow/q/101600/relational-division-with-xquery

You can test the last query here: https://data.stackexchange.com/stackoverflow/q/101600/relational-division-with-xquery

这篇关于在 SQL 中的 xml 列中搜索多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！