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

Answer 2

SQL Server: just an addition to @marc_s answer - note that xml is case-sensitive, and resulting xml will look like

<Students>
   <Student>
      <ID>1</ID>
      <Name>a</Name>
      <Number>123</Number>
      <Email>[email protected]</Email>
  </Student>
</Students>

and if you'll try to retrieve id, you'll not find anything.

you may want to do something like this:

select
    ID as id,
    Name as name,
    Number as number,
    Email as email
from dbo.Table1
for xml path('Student'), root('Students')

=> sql fiddle example.

C#: you can use WriteXml method:

var ds = new DataSet("Students");
var dt = ds.Tables.Add("Student");
dt.Columns.Add("id", typeof(int));
dt.Columns.Add("name", typeof(string));
dt.Columns.Add("number", typeof(string));
dt.Columns.Add("email", typeof(string));

dt.Rows.Add(1, "a", "123", "[email protected]");
dt.Tables[0].Rows.Add(2, "b", "321", "[email protected]");
dt.Tables[0].Rows.Add(3, "c", "432", "[email protected]");
var stream = new StringWriter();
ds.WriteXml(stream);

or using Linq to XML:

 new XElement("Students", dt.AsEnumerable().Select(r =>
       new XElement("Student",
            new XElement("id", r["id"]),
            new XElement("name", r["name"]),
            new XElement("number", r["number"]),
            new XElement("email", r["email"])
       )));