title: Fast data retrieval from XML type field in MS SQL Server
body: <p>I have a <strong>tbl_Data</strong> table in MS SQL Server 2008, which having two fields, <em>ID</em> is primary  Key and <em>CustomFields</em> column can have XML Data. </p>
  
  <blockquote>
  <pre> [ID] [uniqueidentifier] [CustomFields] [xml] NULL </pre>
  </blockquote>
  
  <p>Average nodes in XML - 25 nodes in each record (some records having null, some having up to 70 nodes)
  Total Record count in tbl_Data : 80000 rows.</p>
  
  <p>Below is a sample data for CustomFields Column.</p>
  
  <pre><code>&lt;Fields&gt;
  &lt;Field Id="StatusName" Value="Approve" /&gt;
  &lt;Field Id="LogNumber" Value="70470" /&gt;
  &lt;Field Id="SubmittedDate" Value="Feb  7 2012 12:00AM" /&gt;
  ..
  &lt;Fields&gt;
  </code></pre>
  
  <p>I am trying to execute below query for retrieve records which having LogNumber 70470, This Query taking 1 Minute to generate result.</p>
  
  <blockquote>
  <pre> SELECT ID FROM [tbl_Data] WHERE
  CustomFields.exist('/Fields/Field[@Title = "LogNumber"]') = 1
       AND CustomFields.exist('/Fields/Field[@Value="70470"]') = 1 </pre>
  </blockquote>
  
  <p>I have tried to apply XML Index, but seems was not helpful. </p>
  
  <p>Your valuable suggestion or advice is most welcome which you think for improve data retrieval performance i.e. Do I need to change XML structure? </p>
  
  <p>Thanks
  Suresh</p>