SQL query for Dynamic nodes in XML

Question

Our table is like

StudentNo  Name  Subject  Mark   Grade
1          John   English  41     A
1          John   Hindi    42     B 

We want an XML format from this table as follows.

<Student>
    <Name>John</Name>
  <Subject>
    <English>
          <Mark>41</Mark>
          <Grade>A</Grade>
    </English>
    <Hindi>
          <Mark>42</Mark>
          <Grade>B</Grade>
    </Hindi>
 </Subject>
<Student>

Here the subject name nodes should be generated dynamically.

Answer 1

This is very similar to SQL Data as XML Element - so much so that I think it might be a duplicate - but I want to explain a bit more for your context why this isn't the best idea. In my answer to that question, I show a really hacky way that you could do this, but it's not the best idea.

Your XML will be nearly impossible to create a schema for. Any consumer of that XML will never be able to be sure what values might appear as elements. Rather than try to create dynamic elements, you should probably use attributes of some sort. You could even use xsi:type to create an abstract type in your XML of sorts (although in my example I'm just using a plain old attribute - you could pick whatever attribute will make the most sense for your consumers). The Query for that XML would be:

declare @subjects TABLE(studentno int, name varchar(10), subjecT varchar(10), mark int, grade char(1))

INSERT @subjects
VALUES
(1, 'John','English',  41,'A'),
(1, 'John','Hindi',    42,'B')

select 
    s.Name

    ,(SELECT
        s2.Subject as '@type'
        ,s2.Mark
        ,s2.Grade
    FROM @subjects s2 
    WHERE s2.studentno = s.studentno
    FOR XML PATH('Subject'), ROOT('Subjects'), TYPE)
from @subjects s
GROUP BY s.name, s.studentno
FOR XML PATH('Student')

produces:

<Student>
  <Name>John</Name>
  <Subjects>
    <Subject type="English">
      <Mark>41</Mark>
      <Grade>A</Grade>
    </Subject>
    <Subject type="Hindi">
      <Mark>42</Mark>
      <Grade>B</Grade>
    </Subject>
  </Subjects>
</Student>

This XML will be possible to make sense of by consumers, where they can, for example, iterate the subjects without knowing what subjects might be there (and without needing to resort to assuming that every direct child of Subjects is in fact a subject and not some other type of node that got added in a new version of the schema).

---

If you really need that output, I'd prefer to use XSLT to transform the output above to your format, e.g.:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" encoding="UTF-8" indent="yes" />

    <xsl:template match="node()|@*">
     <xsl:copy>
         <xsl:apply-templates />
     </xsl:copy>
    </xsl:template>

    <xsl:template match="Subject">
        <xsl:element name="{@type}">
            <xsl:apply-templates />
        </xsl:element>
    </xsl:template>

    <xsl:template match="Subjects">
        <xsl:element name="Subject">
            <xsl:apply-templates />
        </xsl:element>
    </xsl:template>
</xsl:transform>

gets you

<?xml version="1.0" encoding="UTF-8"?>
<Student>
  <Name>John</Name>
  <Subject>
      <English>
         <Mark>41</Mark>
         <Grade>A</Grade>
      </English>
      <Hindi>
         <Mark>42</Mark>
         <Grade>B</Grade>
      </Hindi>
  </Subject>
</Student>

Note you can't do this completely with SQL Server though - you'd have to resort to building the XML string and casting it as XML, as in my other answer.