title: SQL Server XML query
body: <p>I haven't dealt with XML documents since college. I'm looking for some assistance. I need to write a XML query in SQL Server that will output the data as such.</p>
  
  <p>Please note that the data is being pulled from just one table in SQL Server.</p>
  
  <pre><code>&lt;EnrollmentRequest&gt;
      &lt;Vendor id="string"/&gt;
      &lt;Members&gt;
          &lt;Member&gt;  -- 1st Member Record
              &lt;PayerPatientId&gt;string&lt;/PayerPatientId&gt;
              &lt;PayerInsuranceId&gt;string&lt;/PayerInsuranceId&gt;
              &lt;RequestTypeId&gt;6427&lt;/RequestTypeId&gt;
              &lt;LastName&gt;string&lt;/LastName&gt;
              &lt;FirstName&gt;string&lt;/FirstName&gt;
              &lt;Gender&gt;string&lt;/Gender&gt;
              &lt;DOB&gt;string&lt;/DOB&gt;
              &lt;Zip&gt;string&lt;/Zip&gt;
              &lt;Phone&gt;string&lt;/Phone&gt;
              &lt;SSN&gt;string&lt;/SSN&gt;
              &lt;City&gt;string&lt;/City&gt;
              &lt;State&gt;string&lt;/State&gt;
              &lt;DateOfServiceRange/&gt;
              &lt;PatientConsent&gt;string&lt;/PatientConsent&gt;
          &lt;/Member&gt;
          &lt;Member&gt; -- 2nd Member Record
              &lt;PayerPatientId&gt;string&lt;/PayerPatientId&gt;
              &lt;PayerInsuranceId&gt;string&lt;/PayerInsuranceId&gt;
              &lt;RequestTypeId&gt;-2065&lt;/RequestTypeId&gt;
              &lt;LastName&gt;string&lt;/LastName&gt;
              &lt;FirstName&gt;string&lt;/FirstName&gt;
              &lt;Gender&gt;string&lt;/Gender&gt;
              &lt;DOB&gt;string&lt;/DOB&gt;
              &lt;Zip&gt;string&lt;/Zip&gt;
              &lt;Phone&gt;string&lt;/Phone&gt;
              &lt;SSN&gt;string&lt;/SSN&gt;
              &lt;City&gt;string&lt;/City&gt;
              &lt;State&gt;string&lt;/State&gt;
              &lt;DateOfServiceRange/&gt;
              &lt;PatientConsent&gt;string&lt;/PatientConsent&gt;
          &lt;/Member&gt;
      &lt;/Members&gt;
  &lt;/EnrollmentRequest&gt;
  </code></pre>
  
  <p>I tried using   </p>
  
  <pre><code>FOR XML PATH('Member'),  
  ROOT('EnrollmentRequest')
  </code></pre>
  
  <p>But I am unable to get the 'Members" element piece. It's my understanding there cannot be more than one root, so how can I get the 'Members' tag to function like the "EnrollmentRequest' tag and how can I get the vendor id tag to appear only once in the file?</p>