remove preserve from xmlwriter

Question

The goal of my app is to take data from the database and write it to an XML file on my desktop.

private void btnSubmit_Click(object sender, EventArgs e)
{
    dtPart_Sales = new DataTable { TableName = "Part_Sales"};

    Application.DoEvents();
    List<string> listTemp = new List<string>(Regex.Split(textBox1.Text, Environment.NewLine));
    List<string> list = listTemp.Distinct().ToList();
    var joinedNames = list.Aggregate((a, b) =>  a + "','" + b+ "");

    SqlConnection mConnection = new SqlConnection();
    SqlCommand mCommand = new SqlCommand();
    SqlDataAdapter mDataAdapter = new SqlDataAdapter();

    mConnection.ConnectionString = Conn_Str();
    mCommand.CommandType = CommandType.Text;
    mCommand.Connection = mConnection;

    if (chkSales.Checked)
    {
        try
        {
            mConnection.Open();

            Application.DoEvents();
            SqlDataAdapter da = new SqlDataAdapter("select PRTNUM_29, SLSCAT_29, PMDES1_29, PMDES2_29, STK_29, TAXABL_29, " +
                "BOMUOM_29, SLSUOM_29, SLSCNV_29, PRICE_29, BREAK1_29, DISC1_29, PRICE1_29, BREAK2_29, DISC2_29, PRICE2_29," +
                " BREAK3_29, DISC3_29, PRICE3_29, BREAK4_29, DISC4_29, PRICE4_29, BREAK5_29, DISC5_29, PRICE5_29, BREAK6_29," +
                " DISC6_29, PRICE6_29, BREAK7_29, DISC7_29, PRICE7_29, BREAK8_29, DISC8_29, PRICE8_29, BREAK9_29, DISC9_29," +
                " PRICE9_29, QTYMTD_29, SLSMTD_29, CSTMTD_29, QTYYTD_29, SLSYTD_29, CSTYTD_29, QTYLYR_29, SLSLYR_29, CSTLYR_29," +
                " QTYCOM_29, CRTLTO_29, AUTOMS_29, APLDSC_29, PRDLIN_29, HISFLG_29, WARFLG_29, LABWAR_29, MATWAR_29, RETMTD_29," +
                " RETYTD_29, UNWRPL_29, UNWREP_29, OUWRPL_29, OUWREP_29, COMMIS_29, TAXCDE_29, TAXCDE2_29, TAXCDE3_29, MCOMP_29," +
                " MSITE_29, UDFKEY_29, UDFREF_29, ALWBCK_29, AUTOMF_29, FILLER_29, MANPRC_29, WARRES_29, CreatedBy, ModifiedBy" +
                " from Part_Sales where PRTNUM_29 in ('" + joinedNames + "')", mConnection);
            da.Fill(dtPart_Sales);
            mConnection.Close();

            string FileName = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\PRSBatchDL\\Part_Sales" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".XML";
            dtPart_Sales.WriteXml(FileName);

            MessageBox.Show(FileName + " is saved!");
        }
        catch (Exception ex)
        {
            if (ex.Message == "The source contains no DataRows.")
                dtPart_Sales = new DataTable();
            else
            {
                MessageBox.Show(ex.Message.ToString());
                return;
            }
        }
    }
}

The file writes out ok, but .the problem I'm having is ns1:space is being populated in the some column headers with preserve being set as the data.

My question is how do I remove these columns?

Answer 1

No, there is no straight way to remove the xml:space="preserve" while generating xml file using WriteXml() method.

There are couple of alternatvies

Option 1:

Load the xml and do string replace, simple.

 XmlDocument doc = null;
 doc.LoadXml(ds.GetXml())
 Return doc.InnerXml.Replace(" xml:space=""preserve""", "")

Option 2:

Using XSLT, you can create XSLT template and run it against it to remove the xml:space attribute. Will require some knowledge of XSLT transaformation.

Option 3:

Using XmlReader / XmlWriter and chain them to copy all the nodes except xml:space attribute. Will require some deep programming to handle all the possible xnodes.

Hope this helps.