問題描述
這是我的桌子
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模板網！