如何导出完整的SQL表到XML [英] How to Export Full SQL Table to XML
问题描述
我的主要编码的理想是在.NET应用程序..所以我有限的技能与应用设计。
我想整个表从数据库导出(使用视图)到XML文件(然后希望导出为preadsheet - 我看,并不能找到一个直接的方式)<。 / P>
我已经成功出口仅1列到XML文件中,使用这种code:
DataConn.UserName =***;
DataConn.Password =***;
DataConn.SqlServer =***;
DataConn.Database =***;
常量字符串STRSQL =SELECT TOP 1 * FROM vwGetStaffDetails FOR XML AUTO;
使用(SqlCommand的sqlComm =新的SqlCommand(STRSQL,DataConn.Connect()){的CommandType = CommandType.Text})
{
。字符串结果= sqlComm.ExecuteScalar()的ToString();
File.WriteAllText(@C:\ TEMP \ text.xml,结果);
}
每当我用的ExecuteReader
或的ExecuteXmlReader
,我没有得到任何实际的结果。
我怎么会得到所有的字段?
编辑:我不得不使用顶1
获得执行标
工作好。
使用下面的解决方案,我的文件显示:
&LT; XML版本=1.0独立=是&GT?;
&LT; NewDataSet&GT;
&LT;表&gt;
&LT; XML_F52E2B61-18A1-11d1-B105-00805F49916B&GT;&放大器; LT;**** JPGvwGetStaffDetails ImageLoc = LName的=GAINESFName参数=****StaffTitle =****JobPosition = ****的电子邮件=。*** *** @ COMcode =***号=******PhoneTypeID =1/&放大器; GT;&LT ; / XML_F52E2B61-18A1-11d1-B105-00805F49916B&GT;
&LT; /表&gt;
&LT; / NewDataSet&GT;
它写&放大器;而不是正确的XML
等; LT。是什么办法来解决这个问题?
您可以使用的SqlDataAdapter
和 System.Data.DataSet中
加载数据表
,这将写入XML。
常量字符串STRSQL =SELECT * FROM vwGetStaffDetails;
使用(SqlCommand的sqlComm =新的SqlCommand(STRSQL,DataConn.Connect()){的CommandType = CommandType.Text})
{
SqlDataAdapter的DA =新的SqlDataAdapter(sqlComm);
的DataSet ds为新的DataSet();
da.Fill(DS);
ds.Tables [0] .WriteXml(@C:\ TEMP \ text.xml);
}
修改使用这种方法,你会从SQL删除XML code,让.NET转换的一切。我更改了自己的SQL命令来反映这一点。
My primary coding ideal is on .net applications.. So I have limited skill with application design.
I am trying to export an entire table from a database (Using a view) to an XML file (To then hopefully export that to a spreadsheet - I've looked and fail to find a direct way).
I have successfully exported only 1 column to the xml file, using this code:
DataConn.UserName = "***";
DataConn.Password = "***";
DataConn.SqlServer = "***";
DataConn.Database = "***";
const string strSql = "SELECT TOP 1 * FROM vwGetStaffDetails FOR XML AUTO";
using (SqlCommand sqlComm = new SqlCommand(strSql, DataConn.Connect()) { CommandType = CommandType.Text })
{
string result = sqlComm.ExecuteScalar().ToString();
File.WriteAllText(@"C:\Temp\text.xml", result);
}
Whenever I use ExecuteReader
or ExecuteXmlReader
, I don't get any actual results.
How would I get all the fields?
Edit: I had to use Top 1
to get the Execute Scalar
working well.
Using the below solution, my file shows:
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Table>
<XML_F52E2B61-18A1-11d1-B105-00805F49916B><vwGetStaffDetails ImageLoc="****.jpg" LName="GAINES" FName="****" StaffTitle="****" JobPosition="****" Email="***@***.com" Code="***" Number="******" PhoneTypeID="1"/></XML_F52E2B61-18A1-11d1-B105-00805F49916B>
</Table>
</NewDataSet>
It's writing <
etc instead of proper XML. Is the any way to fix it?
You can use a SqlDataAdapter
and System.Data.DataSet
to load a DataTable
, which will write to XML.
const string strSql = "SELECT * FROM vwGetStaffDetails";
using (SqlCommand sqlComm = new SqlCommand(strSql, DataConn.Connect()) { CommandType = CommandType.Text })
{
SqlDataAdapter da = new SqlDataAdapter(sqlComm);
DataSet ds = new DataSet();
da.Fill(ds);
ds.Tables[0].WriteXml(@"C:\Temp\text.xml");
}
Edit Using this method you'll remove the XML code from SQL and let .NET convert everything. I've changed your SQL command to reflect this.
这篇关于如何导出完整的SQL表到XML的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!