c# - How to make sql query result to xml file

Question

I have an sql query, selects some data from a table.

ID    Name    Number    Email
1      a        123       [email protected]
2      b        321       [email protected]
3      c        432       [email protected]

I get these datas from the table. I want create a xml file from the data. Like this

<Students>
  <Student>
      <id>1</id>
      <name>a</name>
      <number>123</number>
      <email>[email protected]</email>
  </Student>
  <Student>
      <id>2</id>
      <name>b</name>
      <number>321</number>
      <email>[email protected]</email>
  </Student>
  <Student>
      <id>3</id>
      <name>c</name>
      <number>432</number>
      <email>[email protected]</email>
  </Student>
</Students>

How can I do it on C# and SQL Server ?

Answer 1

Try this:

SELECT *
FROM dbo.YourStudentTable
FOR XML PATH('Student'), ROOT ('Students')

This should return exactly the XML you're looking for (assuming you have SQL Server 2005 or newer)

Read more about how to use FOR XML PATH and its capabilities on TechNet