Generating Custom XML from multiple rows in SQL server

ONeil Tomlinson

I have the following table and i would like to generate an XML as shown below. How do i go about doing this please? All help appreciated. Thanks

id school, name, Subject, results, studentID

1 oxford tom Maths 98 1
2 oxford tom English 87 1
3 oxford tom Chemistry 63 1

4 Cambridge john Maths 50 2
5 Cambridge john English 72 2
6 Cambridge john Chemistry 32 2

<School>
<Name>Oxford</Name>
<Students>
<Student>
<Name>tom</Name>
<Subjects>
<Subject>
<Name>Maths<Name>
<grde>98<grde>
<Subject>
<Subject>
<Name>English<Name>
<grde>87<grde>
<Subject>
<Subject>
<Name>Chemistry<Name>
<grde>63<grde>
<Subject>
<Subjects>
<Student>
<Students>
<School>

<School>
<Name>Cambridge</Name>
<Students>
<Student>
<Name>john</Name>
<Subjects>
<Subject>
<Name>Maths<Name>
<grde>50<grde>
<Subject>
<Subject>
<Name>English<Name>
<grde>72<grde>
<Subject>
<Subject>
<Name>Chemistry<Name>
<grde>32<grde>
<Subject>
<Subjects>
<Student>
<Students>
<School>

Lost User

Take a look at these links[^].

Use the best guess

RedDk

Well, there's no "grade" here ... so I'll use "results" instead. But to illustrate only a point:

CREATE TABLE [cpqaAnswers].[cpqa].[cpqa_OT_tblRegister](
[id][int],
[school][nvarchar](15),
[name][nvarchar](38),
[Subject][nvarchar](45),
[results][int],
[studentID][int]
)

That's the table.

BULK INSERT [cpqaAnswers].[cpqa].[cpqa_OT_tblRegister]
FROM 'C:\Users\OT\registerOT(td)_.txt'

Where registerOT(td)_.txt has the posted data tab-delimited.

SELECT * FROM [cpqaAnswers].[cpqa].[cpqa_OT_tblRegister]
WHERE [results] = 72
ORDER BY [id]
FOR XML PATH('Schools'), ELEMENTS, ROOT('Register')

Not what OP is after but perhaps more key than the usual webpage link.

<Register>
<Schools>
<id>5</id>
<school>Cambridge</school>
<name>john</name>
<Subject>English</Subject>
<results>72</results>
<studentID>2</studentID>
</Schools>
</Register>

[edit] Is it possible that THIS is what is sought (notice the closed tags that have been added)?

<Register>
<Schools>
<School>
<Name>Oxford</Name>
<Students>
<Student>
<Name>tom</Name>
<Subjects>
<Subject>
<Name>Maths</Name>
<grde>98</grde>
</Subject>
<Subject>
<Name>English</Name>
<grde>87</grde>
</Subject>
<Subject>
<Name>Chemistry</Name>
<grde>63</grde>
</Subject>
</Subjects>
</Student>
</Students>
</School>
<School>
<Name>Cambridge</Name>
<Students>
<Student>
<Name>john</Name>
<Subjects>
<Subject>
<Name>Maths</Name>
<grde>50</grde>
</Subject>
<Subject>
<Name>English</Name>
<grde>72</grde>
</Subject>
<Subject>
<Name>Chemistry</Name>
<grde>32</grde>
&