title: Which XML index should I choose?
body: <p>I have to search for a Postcode in my XML type column. I have created a primary XML index on my table. </p>
  
  <p>I am following <a href="https://msdn.microsoft.com/en-us/library/ms191497(v=sql.110).aspx" rel="nofollow">this</a> article to understand the concepts. </p>
  
  <p>My query looks like this </p>
  
  <pre><code>select *some fields* from table_name where
  [xml_column].exist(''/Session/Entries/Entry[@DataItemId="Address.Postcode" and contains(upper-case(.), "@Postcode")]'') = 1
  </code></pre>
  
  <p>@Postcode is the input parameter and user can enter full or partial postcode</p>
  
  <p>Now according to the link, </p>
  
  <blockquote>
    <p>If your workload uses path expressions significantly on XML columns, the PATH secondary XML index is likely to speed up your workload. The most common case is the <strong>use of the exist() method on XML columns in the WHERE clause of Transact-SQL.</strong></p>
  </blockquote>
  
  <p>But then I get little confused with the following statement </p>
  
  <blockquote>
    <p>If your workload retrieves multiple values from individual XML
    instances by using path expressions, clustering paths within each XML
    instance in the PROPERTY index may be helpful. This scenario typically
    occurs in a property bag scenario when properties of an object are
    fetched and its primary key value is known.</p>
  </blockquote>
  
  <p>I am using <code>exist()</code> method in my <code>where</code> clause but I am returning multiple records.</p>
  
  <p>I am confused as to which secondary index should I create as per my query. I am inclining more towards the PROPERTY index.</p>
  
  <p>Can I create all 3 and let SQL handle which one to choose ?? Will this increase the size of the node table and hamper the speed of the database  ??</p>
  
  <p>Part of my XML looks like this</p>
  
  <pre><code>&lt;Session&gt;
  &lt;Entries&gt;    
  &lt;Entry DataItemId="AccountNumber" Type="Pattern" Source="SessionStore"&gt;
        &lt;Value&gt;212312311&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="SortCode" Type="Pattern" Source="SessionStore"&gt;
        &lt;Value&gt;10-20-30&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="AccountName" Type="Free" Source="SessionStore"&gt;
        &lt;Value&gt;XXXXXXXXX&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="Let" Type="Set" Source="SessionStore"&gt;
        &lt;Value&gt;LET&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="Relative" Type="Boolean" Source="SessionStore"&gt;
        &lt;Value&gt;False&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="LtdCompany" Type="Boolean" Source="SessionStore"&gt;
        &lt;Value&gt;False&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="Address.Postcode" Type="Pattern" Source="SessionStore"&gt;
        &lt;Value&gt;GL8 1TT&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="Address.Line1" Type="Free" Source="SessionStore"&gt;
        &lt;Value&gt;Askdaskdaoiksd&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="Address.Line2" Type="Free" Source="SessionStore"&gt;
        &lt;Value&gt;Ojasuiodjaisjdsa&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="PropertyType" Type="Set" Source="SessionStore"&gt;
        &lt;Value&gt;DH&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="Tenure" Type="Set" Source="SessionStore"&gt;
        &lt;Value&gt;1&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="BuiltThisYear" Type="Boolean" Source="SessionStore"&gt;
        &lt;Value&gt;False&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="YearBuilt" Type="Integer" Source="SessionStore"&gt;
        &lt;Value&gt;1960&lt;/Value&gt;
      &lt;/Entry&gt;
      &lt;Entry DataItemId="StdConstruction" Type="Boolean" Source="SessionStore"&gt;
  &lt;/Entries&gt;
  &lt;/Session&gt;
  </code></pre>
  
  <p><strong>UPDATE</strong> </p>
  
  <p>I did some more digging and then had a chat with senior DBA friend of mine and who advised me to created a PATH index. The query seems to be working way quicker than before. </p>