title: How to convert SQL into XML schema of one row per ID (FOR XML RAW)
body: <p>I have to convert SQL into an XML file using a schema that only makes one row per ID. The query converts an ID's "documents" and puts them all into a one row schema. Instead of making a new row for each document it should add additional documents to the existing ID row using the node "documentData"</p>
  
  <p>Create and INSERT statements for test pop:</p>
  
  <pre><code>CREATE TABLE person  
  (
  birthDate varchar(255),
  lastName varchar(255),
  firstName varchar(255), 
  externalStudentId3 varchar(255),
  externalStudentId2 varchar(255),
  externalStudentId1 varchar(255),
  socialSecurityNumber varchar(255),  
  documentRequirementStatusChangeOnDate datetime, 
  reason varchar(255),    
  scopeValue varchar(255),     
  documentScope varchar(255), 
  status varchar(255),    
  documentName varchar(255),  
  externalDocumentId varchar(255),    
  dateReviewed datetime,
  dateReceived datetime,
  dateCreated datetime,
  documentRequirementExternalId varchar(255)
  )
  
  INSERT INTO person
  VALUES ('19540309','Smith','Jon','100000166','3014925','3014925','999999999',NULL,NULL,NULL,'PERSON','UNSATISFIED','ApplicationUpdate','Application_Update','20190222','20190223','20190224','01309757'),
  ('19540309','Smith','Jon','100000166','3014925','3014925','999999999','20190220','Document added to wrong year requirement.','2020','AwardYear','SATISFIED','CertOne','Cert_one','20190221','20190220','20190220','01294555')
  </code></pre>
  
  <p>Sample Data:</p>
  
  <p><a href="https://i.stack.imgur.com/PJM70.png" rel="nofollow noreferrer">excel screenshot of sample data</a></p>
  
  <pre><code>birthDate           lastName    firstName   externalStudentId3  externalStudentId2  externalStudentId1  socialSecurityNumber    documentRequirementStatusChangeOnDate   reason                                      scopeValue  documentScope   status      documentName        externalDocumentId  dateReviewed                    dateReceived                    dateCreated                     documentRequirementExternalId
  1954-03-09-05:00    Smith       Jon         100000166           3014925             3014925             999999999               NULL                                    NULL                                        NULL        PERSON          UNSATISFIED ApplicationUpdate   Application_Update  2019-12-05T08:40:17.737-05:00   2019-12-05T08:40:17.737-05:00   2019-12-05T08:40:17.737-05:00   01-309757
  1954-03-09-05:00    Smith       Jon         100000166           3014925             3014925             999999999               2019-02-20T10:54:08.670-05:00
  </code></pre>
  
  <p>Current Code that pulls multiple rows per ID:</p>
  
  <pre><code>SELECT
     DISTINCT
            s.dateOfBirth as birthDate
            ,s.lastName
            ,s.firstName
            ,s.externalId3 as externalStudentId3
            ,s.externalId2 as externalStudentId2
            ,s.externalid1 as externalStudentId1
            ,s.socialSecurityNumber as socialSecurityNumber
            ,s.message as reason
            ,s.documentScopeCode as documentScope
            ,s.documentRequirementStatusCode as status
            ,s.name as documentName
            ,s.externalId as externalDocumentId
            ,s.revieweddate as dateReviewed
            ,s.receiveddate as dateReceived
            ,s.createddate as dateCreated
            ,s.documentRequirementExternalId as documentRequirementExternalId
  
  FROM dbo.[person] s
  FOR XML RAW ('student'), ROOT ('students');`
  </code></pre>
  
  <p>Current Output:</p>
  
  <p><a href="https://i.stack.imgur.com/27VW0.png" rel="nofollow noreferrer">current output screenshot</a></p>
  
  <pre><code>&lt;persons&gt;
  &lt;person birthDate="1954-03-09-05:00" lastName="Smith" firstName="Jon" externalStudentId3="100000166" 
  externalStudentId2="3014925" externalStudentId1="3014925" socialSecurityNumber="999999999" 
  documentScope="PERSON" status="UNSATISFIED" documentName="ApplicationUpdate" 
  externalDocumentId="Application_Update" dateReceived="2019-12-05T08:40:17.737-05:00" 
  dateCreated="2019-12-05T08:40:17.737-05:00" documentRequirementExternalId="01-309757" /&gt;
  &lt;person birthDate="1954-03-09-05:00" lastName="Smith" firstName="Jon" externalStudentId3="100000166" 
  externalStudentId2="3014925" externalStudentId1="3014925" socialSecurityNumber="999999999" 
  documentRequirementStatusChangeOnDate="2019-02-20T10:54:08.670-05:00" reason="Document added to 
  wrong year requirement." scopeValue="2020" documentScope="AwardYear" status="SATISFIED" 
  documentName="CertOne" externalDocumentId="Cert_one" dateReviewed="2019-02-20T10:54:08.670-05:00" 
  dateReceived="2019-02-19T13:35:03.143-05:00" dateCreated="2019-02-15T14:19:02.417-05:00" 
  documentRequirementExternalId="01-294517" /&gt;
  &lt;/person&gt;
  </code></pre>
  
  <p>Desired Output:</p>
  
  <p><a href="https://i.stack.imgur.com/XHcpE.png" rel="nofollow noreferrer">desired output screenshot</a></p>
  
  <pre><code>&lt;?xml version='1.0' encoding='UTF-8'?&gt;
  &lt;persons&gt;
    &lt;person birthDate="1954-03-09-05:00" lastName="Smith" firstName="Jon" externalStudentId3="100000166" externalStudentId2="3014925" externalStudentId1="3014925" socialSecurityNumber="999999999"&gt;&lt;documentData documentScope="PERSON" status="UNSATISFIED" documentName="ApplicationUpdate" externalDocumentId="Application_Update" dateReceived="2019-12-  05T08:40:17.737-05:00" dateCreated="2019-12-05T08:40:17.737-05:00" documentRequirementExternalId="01-309757" /&gt;&lt;documentData documentRequirementStatusChangeOnDate="2019-02-20T10:54:08.670-05:00" reason="Document added to  wrong year requirement." scopeValue="2020" documentScope="AwardYear" status="SATISFIED" documentName="CertOne" externalDocumentId="Cert_one" dateReviewed="2019-02-20T10:54:08.670-05:00" dateReceived="2019-02-19T13:35:03.143-05:00" dateCreated="2019-02-15T14:19:02.417-05:00" documentRequirementExternalId="01-294517" /&gt;
    &lt;/person&gt;
  &lt;/persons&gt;
  </code></pre>