XML : TSQL query that outputs as XML

I'm working with a TSQL query that outputs results as XML. I need help figuring out how to out get my output to look like:

  <goals>     <program>        <track type="Year">2015</track>       <track type="Degree">MBA</track>       <track type="Major">ACCT</track>     </program>     <program>        <track type="Year">2015</track>       <track type="Degree">MBA</track>       <track type="Minor">ECON</track>     </program>     <program>        <track type="Year">2015</track>       <track type="Degree">MBA</track>       <track type="Concentration">SPAN</track>     </program>   </goals>     

But so far I have only figured out how to get this:

  <goals>     <program>        <track type="Year">2015</track>       <track type="Degree">MBA</track>       <track type="Major">ACCT</track>     </program>   </goals>    

My code so far is the following:

  select catalog as [track/@year], degree as [track/@degree], major as [track/@major] from StudentInfo   for xml path('track'), type).query('     <goals>     {       for $program in /track           return              <program>                 {$program/Name}                  <track type="year">{data($program/track/@year)}</track>                  <track type="degree">{data($program/track/@degree)}</track>                  <track type="major">{data($program/track/@major)}</track>              </program>      }     </goals>')    

Any help that you could provide is much appreciated! Thank you in advance!

No comments:

Post a Comment