I have two tables, \[code\]XMLtable\[/code\] and \[code\]filterTable\[/code\]. I need all the \[code\]XMLtable.ID\[/code\] values from \[code\]XMLtable\[/code\] where the data in \[code\]Col_X\[/code\] contains \[code\]MyElement\[/code\], the contents of which matches \[code\]filterColumn\[/code\] in \[code\]filterTable\[/code\]. The XML for each row in \[code\]Col_X\[/code\] may contain multiple \[code\]MyElement\[/code\]'s, and I want that \[code\]ID\[/code\] in case ANY of those elements match ANY of the values in \[code\]filterColumn\[/code\].The problem is that those columns are actually of \[code\]varchar(max)\[/code\] datatype, and the table itself is huge (like 50GB huge). So this query needs to be as optimized as possible.Here's an example for where I am now, which merely returns the row where the first matching element equals one of the ones I'm looking for. Due to a plethora of different error messages I can't seem to be able to change this to compare to all of the same named elements as I want to.\[code\]SELECT ID, CAST(Col_X AS XML).value('(//*[local-name()=''MyElement''])', N'varchar(25)') FROM XMLtable\[/code\]...and then compare the results to \[code\]filterTable\[/code\]. This already takes 5+ minutes.What I'm trying to achieve is something like:\[code\]SELECT IDFROM XMLtableWHERE CAST(Col_X AS XML).query('(//*[local-name()=''MyElement''])') IN (SELECT filterColumn FROM filterTable)\[/code\]The only way I can currently achieve this is to use the LIKE operator, which takes like a thousand times longer.Now, obviously it's not an option to start changing the datatypes of the columns or anything else. This is what I have to work with. 