C# 如何将sql查询结果制作成xml文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18654157/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make sql query result to xml file
提问by user2749990
I have an sql query, selects some data from a table.
我有一个 sql 查询，从表中选择一些数据。
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
我从表中得到这些数据。我想从数据创建一个 xml 文件。像这样
<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 ?
我怎样才能在 C# 和 SQL Server 上做到这一点？
采纳答案by marc_s
Try this:
尝试这个：
SELECT *
FROM dbo.YourStudentTable
FOR XML PATH('Student'), ROOT ('Students')
This should return exactly the XML you're looking for (assumingyou have SQL Server 2005or newer)
这应该准确返回您正在寻找的 XML（假设您有 SQL Server 2005或更新版本）
Read more about how to use FOR XML PATHand its capabilities on TechNet
回答by Giannis Paraskevopoulos
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 返回一文中的各种选项。
回答by thilipan
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) 获取数据到StudentList从数据库调用的列表中
3)Then open or create xml file and add values
3）然后打开或创建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();
}
回答by Roman Pekar
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>[email protected]</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')
C#: you can use WriteXmlmethod:
C#：您可以使用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", "[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:
或使用 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"])
)));
回答by Ram Khumana
You can use DataSet.GetXml()function for getting result in XML format file
您可以使用DataSet.GetXml()函数在 XML 格式文件中获取结果
