XML : SQL to XML transfer of data

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

  `<lawyerItems>    <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.

  `<lawyerItems>    <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.

No comments:

Post a Comment