Relational division with xquery

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow на русском

Q&A for программистов

-- Relational division with xquery

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>')

declare @FIDs xml = '<FID>1</FID><FID>3</FID>'

DECLARE @tb TABLE (iCount INT);


;with cteParam(FID) as
(
  select T.N.value('.', 'int')
  from @FIDs.nodes('FID') as T(N)
)  
INSERT @tb
select T.BasketID
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.BasketID
having count(T.BasketName) = (select count(*) from cteParam)

select COUNT(*) from @tb

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results