Search for multiple values in xml column in SQL

Question

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

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

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...

Community · Accepted Answer · 2021-01-18 12:30:34Z

2

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

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

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)

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

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

edited Jan 18, 2021 at 12:30

CommunityBot

11 silver badge

answered May 29, 2011 at 21:15

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nav Over a year ago

Excellent work!! Was looking for this answer for a long long time. Great work Mikael Eriksson. You're the man!

marc_s · Accepted Answer · 2011-05-28 21:14:33Z

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.

From that CTE, I select those baskets that contain both a value of 1 and 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

answered May 28, 2011 at 21:14

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

2 Comments

nav Over a year ago

This solution works without a doubt. Great Job! However, if the number of values to be matched for increases like now if i want to search for Baskets with FruitIds 1,2 and 3, another EXISTS statement will have to be concatenated using dynamic queries. Or i guess, i'd need to call this stored procedure in a while loop recursively...?

marc_s Over a year ago

@nav: yes, I agree - the solution isn't perfect in that it's not "extensible" - but I cannot think of any other way just right now

Kyberias · Accepted Answer · 2011-05-28 20:13:56Z

0

Is this too trivial?

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

answered May 28, 2011 at 20:13

Kyberias

1,2611 gold badge15 silver badges23 bronze badges

Collectives™ on Stack Overflow

Search for multiple values in xml column in SQL

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related