title: SQL query FOR XML Path Root
body: <p>I want to display column called SampleDate from table SampleTable where column SampleAgencyName = something and below the date I want to display the value of a column called SampleNumber. </p>
  
  <p>I mean the results should be something like this.</p>
  
  <pre><code> &lt;tr&gt;
      &lt;td&gt; 
      Date1
      SampleNumber1
      &lt;/td&gt;
      &lt;td&gt; 
      Date2
      SampleNumber2
      &lt;/td&gt;
      &lt;/tr&gt;
  </code></pre>
  
  <p>I want to do this using FOR XML Path Root in SQL SERVER.</p>
  
  <p>I can get just the dates to work easily.</p>
  
  <pre><code>SELECT SampleDate 'td' from SampleTable T where SampleAgencyName = 'something' for xml path('tr'), root('table')
  </code></pre>
  
  <p>My issue how to get the value for the SampleNumber column under the date.</p>