title: Adding a unique row count to a SQL 2008 "for xml path" statement?
body: <p>Ok, not sure if this is possible to do..</p>
  
  <p>I have a query that returns just simple records from the database, but formatted out as XML as follows:</p>
  
  <pre><code>select name, address, dateCreated, flag
  from table
  where name = 'test'
  for xml path('row'), root('rows')
  </code></pre>
  
  <p>ok, no problems, and I get the XML as desired:</p>
  
  <pre><code>&lt;rows&gt;
    &lt;row&gt;
      &lt;name&gt;jddjdjd&lt;/name&gt;
      &lt;address&gt;dkdkdkdkdkd&lt;/address&gt;
      .. and so on...
    &lt;/row&gt;
  &lt;/rows&gt;
  </code></pre>
  
  <p><em><strong>BUT WHAT I AM TRYING TO FIND OUT IS</em></strong></p>
  
  <p>If I can have the XML return as follows:</p>
  
  <pre><code>&lt;rows&gt;
    &lt;row id='@@rowcount'&gt;
      &lt;name&gt;jddjdjd&lt;/name&gt;
      &lt;address&gt;dkdkdkdkdkd&lt;/address&gt;
      .. and so on...
    &lt;/row&gt;
  &lt;/rows&gt;
  </code></pre>
  
  <p>As you can see, I really could do with the  node returning with an attribute of say a @@rowcount or even a field from the select statement (not sure if that is possible!!)</p>
  
  <p>Any help much appreciated!!!
  David.</p>