如何将sql查询结果生成xml文件

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

我有一个sql查询，从表中选择一些数据。

ID    Name    Number    Email
1      a        123       a@a.com
2      b        321       b@b.com
3      c        432       c@c.com

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

我从表中得到这些数据。我想从数据中创建一个xml文件。像这样

<Students>
  <Student>
      <id>1</id>
      <name>a</name>
      <number>123</number>
      <email>a@a.com</email>
  </Student>
  <Student>
      <id>2</id>
      <name>b</name>
      <number>321</number>
      <email>b@b.com</email>
  </Student>
  <Student>
      <id>3</id>
      <name>c</name>
      <number>432</number>
      <email>c@c.com</email>
  </Student>
</Students>

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

我如何在c#和SQL Server上执行它?

5 个解决方案

#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)

它应该返回您正在查找的XML(假设您有SQL Server 2005或更新的)

阅读更多关于如何在TechNet上使用XML路径及其功能的信息

#2

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

SQL Server:只是对@marc_s答案的补充——注意，xml是区分大小写的，结果的xml看起来是这样的

<Students>
   <Student>
      <ID>1</ID>
      <Name>a</Name>
      <Number>123</Number>
      <Email>a@a.com</Email>
  </Student>
</Students>

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

如果您试图检索id，您将找不到任何东西。

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.

= > sql小提琴的例子。

C#: you can use WriteXml method:

您可以使用WriteXml方法:

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", "a@a.com");
dt.Tables[0].Rows.Add(2, "b", "321", "b@b.com");
dt.Tables[0].Rows.Add(3, "c", "432", "c@c.com");
var stream = new StringWriter();
ds.WriteXml(stream);

or using Linq to XML:

或者使用Linq到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"])
       )));

#3

Though the solution provided by marc is exactly what you need, you may want to have a deeper look at various options in the article Using the FOR XML Clause to Return Query Results as XML.

尽管marc提供的解决方案正是您所需要的，但是您可能希望更深入地了解本文中使用FOR XML子句返回查询结果作为XML的各种选项。

#4

1) Create class called student

1)创建名为student的类

[Serializable]
public class Student
{
  public int ID { get; set; }
  public string Name { get; set; }
  public int Number { get; set; }
  public string Email { get; set; }
}

2) Get data into List called StudentListfrom Database

2)将数据导入名为StudentListfrom数据库的列表

3)Then open or create xml file and add values

然后打开或创建xml文件并添加值

using (XmlWriter writer = XmlWriter.Create("Student.xml"))
{
    writer.WriteStartDocument();
    writer.WriteStartElement("Students");

    foreach (Student student in StudentList)
    {
    writer.WriteStartElement("Student");

    writer.WriteElementString("id", student.ID.ToString());
    writer.WriteElementString("name", student.Name.ToString());
    writer.WriteElementString("number", student.Number.ToString());
    writer.WriteElementString("email", student.Email.ToString());

    writer.WriteEndElement();
    }

    writer.WriteEndElement();
    writer.WriteEndDocument();
}

#5

You can use DataSet.GetXml() function for getting result in XML format file

可以使用DataSet.GetXml()函数在XML格式文件中获取结果

#1