title: Sql server xml parse
body: <p>Please find below the script snippet </p>
  
  <pre><code>declare @xml xml
  set @xml = '&lt;Message&gt;
  
  &lt;MessageData&gt;
    &lt;MessageDataSet &gt;
    &lt;id&gt; 1 &lt;/id&gt;
    &lt;name&gt;Vasan&lt;/name&gt;     
    &lt;/MessageDataSet&gt;  
     &lt;MessageDataSet &gt;
  
      &lt;id&gt; 2 &lt;/id&gt;
      &lt;name&gt;Vivek&lt;/name&gt;
    &lt;/MessageDataSet&gt;  
  
  &lt;/MessageData&gt;
  
  &lt;/Message&gt;'
  
  SELECT
  t.c.value('(id)[1]','varchar(100)')
  from
  @xml.nodes('/Message/MessageData/MessageDataSet')  AS t(c)
  </code></pre>
  
  <p>I am getting all the 'id' node values, that is '1' &amp; '2'. My need, I have to directly fetch the value of the  second 'id', in this case.it will be '2'. Can anyone let me know the Select query syntax for the same? Thanks.</p>