I have XML column like below
<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>
I need to fetch name and value node for max of dateReported. So far my sQL is like below
SELECT Col1 = Col.value('(/XMLDoc/AAA/Name)[1]', 'varchar(255)'),
Col2 = Col.value('(/XMLDoc/AAA/Value)[2]', 'varchar(255)')
from table t
basically i need my output to be Name_B and Val_B as output, since datereported for same has max value.
No comments:
Post a Comment