0

To see if all the strings in the xml rows are matched for a particular user.

I was thinking something like this:

SELECT t.userId
    FROM userToInterestsTable t
   WHERE t.interest IN [something like an array i get from the xml string rows]
GROUP BY t.userId
  HAVING COUNT(t.userId) = [number of rows in the xml]

But I can not find a way to parse the xml into something like an array without having a specified number of columns. But the number of rows in the xml is dynamic. It is not known before hand.

I know i can do this with an IN clause if I generate the sql statement dynamically, but I want to use a stored procedure to make it more efficient.

RPichioli
  • 3,245
  • 2
  • 25
  • 29
  • See posting : http://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column – jdweng Dec 12 '16 at 10:50
  • I don't think I can do this because it's going to be very inefficient. I need to somehow convert the xml to a table with a dynamic number of columns first. –  Dec 12 '16 at 10:54
  • The post sample of xml. You would need to parse the entire xml which may be less efficient then the methods shown in the posting. – jdweng Dec 12 '16 at 12:02

0 Answers0