Search for multiple values in an xml column

[Ogtzzurnh]

Environment: SQL Server 2012. Primary and secondary (value) index is built on xml column.Say I have a table Message with xml column WordIndex. I also have a table Word which has WordId and WordText. Xml for Message.WordIndex has the following schema:\[code\]<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.example.com"> <xs:element name="wi"> <xs:complexType> <xs:sequence> <xs:element maxOccurs="unbounded" name="w"> <xs:complexType> <xs:sequence> <xs:element maxOccurs="unbounded" name="p" type="xs:unsignedByte" /> </xs:sequence> <xs:attribute name="wid" type="xs:unsignedByte" use="required" /> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element></xs:schema>\[/code\]and some data to go with it:\[code\]<wi xmlns="http://www.example.com"> <w wid="1"> <p>28</p> <p>72</p> <p>125</p> </w> <w wid="4"> <p>89</p> </w> <w wid="5"> <p>11</p> </w></wi>\[/code\]I need to search for multiple values in my xml column WordIndex either using OR or AND. What I'm doing is fairly rudimentary, since I'm a n00b in XQuery (taken from debug output, hence real values):\[code\]with xmlnamespaces(default 'http://www.example.com')select m.Subject, m.MessageId, m.WordIndex.query(' let $dummy := 0 return <word_list> { for $w in /wi/w where $w/@wid=64 return <word wid="64" pos="{data($w/p)}"/> } { for $w in /wi/w where $w/@wid=70 return <word wid="70" pos="{data($w/p)}"/> } { for $w in /wi/w where $w/@wid=63 return <word wid="63" pos="{data($w/p)}"/> } </word_list> ') as WordPositionfrom Message as m-- more joins go here ...where -- more conditions go here ... and m.WordIndex.exist('/wi/w[@wid=64]') = 1 and m.WordIndex.exist('/wi/w[@wid=70]') = 1 and m.WordIndex.exist('/wi/w[@wid=63]') = 1\[/code\]How can this be optimized?