<XMLDoc>
<AAA>
  <Name>Name_A</Name>
  <Value>Val_A</Value>
  <dateReported>1/1/2001<dateReported>
</AAA>
<AAA>
  <Name>Name_B</Name>
  <Value>Val_B</Value>
  <dateReported>1/1/2014<dateReported>
</AAA>
<AAA>
  <Name>Name_C</Name>
  <Value>Val_C</Value>
  <dateReported>1/1/2012<dateReported>
</AAA>
<AAA>
  <Name>Name_D</Name>
  <Value>Val_D</Value>
  <dateReported>1/1/2011<dateReported>
</AAA>
</XMLDoc>

SELECT Col1 =  Col.value('(/XMLDoc/AAA/Name)[1]', 'varchar(255)'),
       Col2 =  Col.value('(/XMLDoc/AAA/Value)[2]', 'varchar(255)')
from table t