SQL to XML transfer of data

Question

Ok have been trying to learn SQL to XML the last few days and this is what I have been able to teach my self thus far.

`SELECT distinct StudentItem.foldername AS "foldername", StudentItem.status, StudentItem.vhrid, StudentItem.firstname, StudentItem.middleinitial, StudentItem.lastname,
dbo.getEnumDescript(StudentType, 'StudentType') AS title,
StudentItem.email, 
dbo.getEnumDescript(OfficeLocation, 'OfficeLocation') AS Office,
practices.id as 'StudentItem/practices/practice/id',
practices.name as 'StudentItem/practices/practice/name',
schoolItem.Name as 'StudentItem/bio/schools/schoolItem/schoolname',
schoolItem.schoolYear as 'lawyerItem/bio/schools/schoolItem/schoolyear'
FROM [dbo].[Student] as lawyerItem
LEFT JOIN [dbo].[StudentGroups] as aprac on StudentItem.vhrid = aprac.vhrid
INNER JOIN [dbo].[PracticeGroups] as practices on aprac.PracticeGroupID = practices.ID
LEFT JOIN [dbo].[StudentEducation] as schoolItem on StudentItem.vhrid = schoolItem.vhrid
where StudentItem.vhrid='50330'
FOR XML path, ROOT ('StudentItem'), ELEMENTS;`  

What I get is this

`<StudentItems>
  <row>
    <foldername>susan.wissink</foldername>
    <status>1</status>
    <vhrid>50330</vhrid>
    <firstname>Susan</firstname>
    <middleinitial>M.</middleinitial>
    <lastname>Wissink</lastname>
    <title>Student leader</title>
    <email>swissink@blank.com</email>
    <Office>Phoenix</Office>
    <StudentItem>
      <practices>
        <practice>
          <id>681</id>
          <name>Real Estate Finance and Lending</name>
        </practice>
      </practices>
      <bio>
        <schools>
          <schoolItem>
            <schoolname>&lt;i&gt;Best in America®&lt;/i&gt;, ASU</schoolname>
            <schoolyear>2016</schoolyear>
          </schoolItem>
        </schools>
      </bio>
    </StudentItem>
  </row>
  <row>
    <foldername>susan.wissink</foldername>
    <status>1</status>
    <vhrid>50330</vhrid>
    <firstname>Susan</firstname>
    <middleinitial>M.</middleinitial>
    <lastname>Wissink</lastname>
    <title>Student leader</title>
    <email>swissink@blank.com</email>
    <Office>Phoenix</Office>
    <StudentItem>
      <practices>
        <practice>
          <id>681</id>
          <name>Real Estate Finance and Lending</name>
        </practice>
      </practices>
      <bio>
        <schools>
          <schoolItem>
            <schoolname>&lt;i&gt;Best in America®&lt;/i&gt;, UOP</schoolname>
            <schoolyear>2011-2015</schoolyear>
          </schoolItem>
        </schools>
      </bio>
    </StudentItem>
  </row>`

But I'm trying to get the all the practices and schools to show up as one entry for the guy that. More or less I'm trying to get it to look like below.

`<StudentItems>
  <row>
    <foldername>susan.wissink</foldername>
    <status>1</status>
    <vhrid>50330</vhrid>
    <firstname>Susan</firstname>
    <middleinitial>M.</middleinitial>
    <lastname>Wissink</lastname>
    <title>Student leader</title>
    <email>swissink@blank.com</email>
    <Office>Phoenix</Office>
    <StudentItem>
      <practices>
        <practice>
          <id>681</id>
          <name>Real Estate Finance and Lending</name>
          <id>683</id>
          <name>Business and Finance</name>
        </practice>
      </practices>
      <bio>
        <schools>
          <schoolItem>
            <schoolname>&lt;i&gt;Best in America®&lt;/i&gt;, UOP</schoolname>
            <schoolyear>2011-2015</schoolyear>
            <schoolname>&lt;i&gt;Best in America®&lt;/i&gt;, ASU</schoolname>
            <schoolyear>2016</schoolyear>
          </schoolItem>
        </schools>
      </bio>
    </StudentItem>
  </row>`

Any help would be welcome. Thank You.

Answer 1

Without sample data, it's difficult to write code and test for. But generally what you need to do is to create sub-queries to create your practice and schoolItem XML nodes. Something like this:

SELECT distinct StudentItem.foldername AS "foldername", 
    StudentItem.status, 
    StudentItem.vhrid, 
    StudentItem.firstname, 
    StudentItem.middleinitial, 
    StudentItem.lastname,
    dbo.getEnumDescript(StudentType, 'StudentType') AS title,
    StudentItem.email, 
    dbo.getEnumDescript(OfficeLocation, 'OfficeLocation') AS Office,
    (
        select practices.id, practices.name
        from [dbo].[StudentGroups] as aprac
        INNER JOIN [dbo].[PracticeGroups] as practices 
        on aprac.PracticeGroupID = practices.ID 
        where StudentItem.vhrid = aprac.vhrid
        FOR XML path(''), type
    ) 'StudentItem/practices/practice',
    (
        select Name schoolname, schoolYear
        from [dbo].[StudentEducation] schoolItem
        where StudentItem.vhrid = schoolItem.vhrid
        FOR XML path(''), type
    ) 'StudentItem/bio/schools/schoolItem'
FROM [dbo].[Student] as StudentItem
where StudentItem.vhrid='50330'
FOR XML path, ROOT ('StudentItem');