XML output from SQL Server based on header and details

Question

I have these table values:

ID	MARKS	TOTALMARKS	SUBJECT	CODE
3789	15	40	Science	SC
3789	25	40	Math	M
3932	14	48	Science	SC
3932	34	48	Social	SO
3957	42	42	Math	M
4871	11	31	Eng	E
4871	20	31	Science	SC

I need the output in XML format, in the following way:

<REPORT>
    <DETAILS>
        <S_HEADER>
            <ID>3789</ID>
            <TOTALMARKS>40</TOTALMARKS>
        </S_HEADER>
        <S_DETAILS>
            <SUBJECT>Science</SUBJECT>
            <MARKS>15</MARKS>
            <CODE>SC</CODE>
        </S_DETAILS>
        <S_DETAILS>
            <SUBJECT>Maths</SUBJECT>
            <MARKS>25</MARKS>
            <CODE>M</CODE>
        </S_DETAILS>
    </DETAILS>
    <DETAILS>
        <S_HEADER>
            <ID>3932</ID>
            <TOTALMARKS>48</TOTALMARKS>
        </S_HEADER>
        <S_DETAILS>
            <SUBJECT>Science</SUBJECT>
            <MARKS>14</MARKS>
            <CODE>SC</CODE>
        </S_DETAILS>
        <S_DETAILS>
            <SUBJECT>Social</SUBJECT>
            <MARKS>34</MARKS>
            <CODE>SO</CODE>
        </S_DETAILS>
    </DETAILS>
    <DETAILS>
        <S_HEADER>
            <ID>3957</ID>
            <TOTALMARKS>42</TOTALMARKS>
        </S_HEADER>
        <S_DETAILS>
            <SUBJECT>Maths</SUBJECT>
            <MARKS>42</MARKS>
            <CODE>M</CODE>
        </S_DETAILS>
    </DETAILS>
    <DETAILS>
        <S_HEADER>
            <ID>4871</ID>
            <TOTALMARKS>31</TOTALMARKS>
        </S_HEADER>
        <S_DETAILS>
            <SUBJECT>eng</SUBJECT>
            <MARKS>11</MARKS>
            <CODE>E</CODE>
        </S_DETAILS>
        <S_DETAILS>
            <SUBJECT>science</SUBJECT>
            <MARKS>20</MARKS>
            <CODE>SC</CODE>
        </S_DETAILS>
    </DETAILS>
</REPORT>

Header contains the group values and individual details will comes under <Details> element.

I had tried with FOR XML PATH, but GROUP BY is causing an error.

SELECT(SELECT(
     SELECT
     ID
    ,TOTALMARKS

    FOR XML PATH('S_HEADER') ,type
    ),

    (
    SELECT
      SUBJECT
     ,MARKS
     ,CODE

     FOR XML PATH('S_DETAILS'),TYPE
    )  FROM TRACK   FOR XML PATH('DETAILS'),ROOT('REPORT'))AS xml_out

In the query i tried to use ORDER BY and Group by ...But giving error. Is there any way to achieve this? Please help me with the SQL query needed to achieve this output.

Answer 1

It is possible when you combine FOR XML with PATH Mode, XPath as column names and nested FOR XML queries:

SELECT id AS [S_HEADER/ID], SUM(marks) AS [S_HEADER/TOTALMARKS], (
    SELECT subject AS [SUBJECT], marks AS [MARKS], code AS [CODE]
    FROM t AS x
    WHERE id = t.id
    FOR XML PATH('S_DETAILS'), TYPE
)
FROM t
GROUP BY id
FOR XML PATH('DETAILS'), ROOT('REPORT')

Result:

<REPORT>
  <DETAILS>
    <S_HEADER>
      <ID>3789</ID>
      <TOTALMARKS>40</TOTALMARKS>
    </S_HEADER>
    <S_DETAILS>
      <SUBJECT>Science</SUBJECT>
      <MARKS>15</MARKS>
      <CODE>SC</CODE>
    </S_DETAILS>
    <S_DETAILS>
      <SUBJECT>Math</SUBJECT>
      <MARKS>25</MARKS>
      <CODE>M</CODE>
    </S_DETAILS>
  </DETAILS>
  <DETAILS>
    <S_HEADER>
      <ID>3932</ID>
      <TOTALMARKS>48</TOTALMARKS>
    </S_HEADER>
    <S_DETAILS>
      <SUBJECT>Science</SUBJECT>
      <MARKS>14</MARKS>
      <CODE>SC</CODE>
    </S_DETAILS>
    <S_DETAILS>
      <SUBJECT>Social</SUBJECT>
      <MARKS>34</MARKS>
      <CODE>SO</CODE>
    </S_DETAILS>
  </DETAILS>
  <DETAILS>
    <S_HEADER>
      <ID>3957</ID>
      <TOTALMARKS>42</TOTALMARKS>
    </S_HEADER>
    <S_DETAILS>
      <SUBJECT>Math</SUBJECT>
      <MARKS>42</MARKS>
      <CODE>M</CODE>
    </S_DETAILS>
  </DETAILS>
  <DETAILS>
    <S_HEADER>
      <ID>4871</ID>
      <TOTALMARKS>31</TOTALMARKS>
    </S_HEADER>
    <S_DETAILS>
      <SUBJECT>Eng</SUBJECT>
      <MARKS>11</MARKS>
      <CODE>E</CODE>
    </S_DETAILS>
    <S_DETAILS>
      <SUBJECT>Science</SUBJECT>
      <MARKS>20</MARKS>
      <CODE>SC</CODE>
    </S_DETAILS>
  </DETAILS>
</REPORT>

DB<>Fiddle