title: SQL XML Column filter based on XML node aggregate function
body: <p>I have XML column like below</p>
  
  <pre><code>&lt;XMLDoc&gt;
      &lt;AAA&gt;
        &lt;Name&gt;Name_A&lt;/Name&gt;
        &lt;Value&gt;Val_A&lt;/Value&gt;
        &lt;dateReported&gt;1/1/2001&lt;dateReported&gt;
      &lt;/AAA&gt;
      &lt;AAA&gt;
        &lt;Name&gt;Name_B&lt;/Name&gt;
        &lt;Value&gt;Val_B&lt;/Value&gt;
        &lt;dateReported&gt;1/1/2014&lt;dateReported&gt;
      &lt;/AAA&gt;
      &lt;AAA&gt;
        &lt;Name&gt;Name_C&lt;/Name&gt;
        &lt;Value&gt;Val_C&lt;/Value&gt;
        &lt;dateReported&gt;1/1/2012&lt;dateReported&gt;
      &lt;/AAA&gt;
      &lt;AAA&gt;
        &lt;Name&gt;Name_D&lt;/Name&gt;
        &lt;Value&gt;Val_D&lt;/Value&gt;
        &lt;dateReported&gt;1/1/2011&lt;dateReported&gt;
      &lt;/AAA&gt;&lt;/x&gt;
      &lt;BB&gt;&lt;ID&gt;12&lt;/ID&gt;&lt;BB&gt;
      &lt;CC&gt;&lt;Type&gt;XML&lt;/Type&gt;&lt;/CC&gt;
  &lt;/XMLDoc&gt;
  </code></pre>
  
  <p>I need to fetch  name and value node for max of dateReported. So far my sQL is like below</p>
  
  <pre><code>SELECT 
     Col1 =  Col.value('(/XMLDoc/x/AAA/Name)[1]', 'varchar(255)'),
     Col2 =  Col.value('(/XMLDoc/x/AAA/Value)[2]', 'varchar(255)'),
     Col3 = Col.value('(/XMLDoc/BB/ID)[1]', 'varchar(255)'),
     Col4 = Col.value('(/XMLDoc/CC/Type)[1]', 'varchar(255)')
  FROM 
     table t
  </code></pre>
  
  <p>Basically I need my output to be <code>Name_B</code>, <code>Val_B</code>, <code>12</code>, <code>XML</code>, since <code>datereported</code> for AAA has max value.</p>