Geek Answers Handbook

Finding multiple values ​​in an xml column in SQL

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 find a basket with FID values ​​1 and 3 so in this case I get Gold and Silver

Although I achieved a result where I can find the SINGLE FID value, for example 1 using this code:

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

If T-SQL I would use IN Clause , like this

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

Any help / workaround is appreciated ...

+4
source share
3 answers

The first option is to add another existing 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 is to use both variables in an xquery statement, counting 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 fine if you know how many FID parameters you are going to use when writing the query. If you are in a situation where the FID number changes, you can use something like this.

 declare @FIDs xml = '<FID>1</FID><FID>3</FID>' ;with cteParam(FID) as ( select TNvalue('.', '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)

Create the @FIDs variable as XML to store the values ​​you want to use in the request.

Here you can test the last query: http://data.stackexchange.com/stackoverflow/q/101600/relational-division-with-xquery

+2
source

This is a bit more than I had hoped, but this solution works.

Basically, I use CTE (Common Table Expression), which splits the table and traverses all the values ​​from the <FID> nodes in the basket names.

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

By running this query, I get the expected output:

 BasketName ---------- Gold Silver
+1
source

Is this too trivial?

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

More articles:


All Articles