How to Export Full SQL Table to XML

Question

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>&lt;vwGetStaffDetails ImageLoc="****.jpg" LName="GAINES" FName="****" StaffTitle="****" JobPosition="****" Email="***@***.com" Code="***" Number="******" PhoneTypeID="1"/&gt;</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
  </Table>
</NewDataSet>

It's writing < etc instead of proper XML. Is the any way to fix it?

Answer 1

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.