This is my table
这是我的桌子
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>
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
我需要搜索FID值为1和3的篮子,这样在这种情况下我会获得金牌和银币
Although i've reached to the result where i can search for a SINGLE FID value like 1 using this code:
虽然我已达到结果,我可以使用此代码搜索像1这样的单个FID值:
declare @fruitId varchar(10);
set @fruitId=1;
select * from Baskets
WHERE BasketFruits.exist('//FID/text()[contains(.,sql:variable("@fruitId"))]') = 1
HAD it been T-SQL i would have used the IN Clause like this
如果是T-SQL,我会像这样使用IN Clause
SELECT * FROM Baskets where FID in (1,3)
Any help/workaround appreciated...
任何帮助/解决方案赞赏...
3 个解决方案
#1
2
First option would be to add another exist the where clause.
第一个选项是添加另一个存在的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
Another version would be to use both variables in the xquery statement, counting the hits.
另一个版本是在xquery语句中使用这两个变量,计算命中数。
select *
from @Test
where BasketFruits.value(
'count(distinct-values(/FRUITS/FID[.=(sql:variable("@fruitId1"),sql:variable("@fruitId2"))]))', 'int') = 2
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.
如果您知道在编写查询时要使用多少个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)
Build the @FIDs variable as an XML to hold the values you want to use in the query.
将@FIDs变量构建为XML以保存要在查询中使用的值。
You can test the last query here: http://data.stackexchange.com/*/q/101600/relational-division-with-xquery
您可以在此处测试上一个查询:http://data.stackexchange.com/*/q/101600/relational-division-with-xquery
#2
1
It is a bit more involved than I hoped it would be - but this solution works.
它比我希望的要多一些 - 但这个解决方案有效。
Basically, I'm using a CTE (Common Table Expression) which breaks up the table and cross joins all values from the <FID>
nodes to the basket names.
基本上,我正在使用CTE(公用表表达式)来分解表并交叉连接从
From that CTE, I select those baskets that contain both a value of 1
and 3
.
从那个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)
Running this query, I do get the expected output of:
运行此查询,我得到预期的输出:
BasketName
----------
Gold
Silver
#3
0
Is this too trivial?
这太微不足道了吗?
SELECT * FROM Baskets WHERE BasketFruits LIKE '%<FID>1</FID>%' AND BasketFruits LIKE '%<FID>3</FID>%'
#1
2
First option would be to add another exist the where clause.
第一个选项是添加另一个存在的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
Another version would be to use both variables in the xquery statement, counting the hits.
另一个版本是在xquery语句中使用这两个变量,计算命中数。
select *
from @Test
where BasketFruits.value(
'count(distinct-values(/FRUITS/FID[.=(sql:variable("@fruitId1"),sql:variable("@fruitId2"))]))', 'int') = 2
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.
如果您知道在编写查询时要使用多少个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)
Build the @FIDs variable as an XML to hold the values you want to use in the query.
将@FIDs变量构建为XML以保存要在查询中使用的值。
You can test the last query here: http://data.stackexchange.com/*/q/101600/relational-division-with-xquery
您可以在此处测试上一个查询:http://data.stackexchange.com/*/q/101600/relational-division-with-xquery
#2
1
It is a bit more involved than I hoped it would be - but this solution works.
它比我希望的要多一些 - 但这个解决方案有效。
Basically, I'm using a CTE (Common Table Expression) which breaks up the table and cross joins all values from the <FID>
nodes to the basket names.
基本上,我正在使用CTE(公用表表达式)来分解表并交叉连接从
From that CTE, I select those baskets that contain both a value of 1
and 3
.
从那个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)
Running this query, I do get the expected output of:
运行此查询,我得到预期的输出:
BasketName
----------
Gold
Silver
#3
0
Is this too trivial?
这太微不足道了吗?
SELECT * FROM Baskets WHERE BasketFruits LIKE '%<FID>1</FID>%' AND BasketFruits LIKE '%<FID>3</FID>%'