take blank value in XML

Question

Following is a piece of code I am using to get data in XML format from excel sheet. but if a value is blank in excel sheet. the XML is not taking it as value. suppose in excel i have a column name as EmployeeAddress and if its blank it should close the tag as <EmployeeAddress/>.

daAdapter = new OleDbDataAdapter("Select * FROM [KDC Report$]", connection);
System.Data.DataTable dt = new System.Data.DataTable("Assets");
//daAdapter.FillSchema(dt, SchemaType.Source);
daAdapter.Fill(dt);

DataSet ds = new DataSet("DocumentElement");
ds.Tables.Add(dt);
var memoryStream = new MemoryStream();

using (TextWriter streamWriter = new StreamWriter(memoryStream))
{
    var xmlSerializer = new XmlSerializer(typeof(DataSet));
    xmlSerializer.Serialize(streamWriter, ds);
    Encoding.UTF8.GetString(memoryStream.ToArray());
}

ds.WriteXml("C:\\Development\\MyAppln\\ExcelToXML\\Products.xml");

Channs · Answer 1 · 2013-10-08T11:43:10.627

0

You can try using Linq to Xml like below:

var xlContent = new XElement("Content", from row in dt.AsEnumerable()
                                                    select new XElement("Row", new XElement("Column1", row["Column1"]),
                                                                               new XElement("Column2", row["Column2"]),
                                                                               new XElement("Column3", row["Column3"]),
                                                                               new XElement("Column4", row["Column4"]))).ToString();

Sample output:

<Content>
  <Row>
    <Column1>1</Column1>
    <Column2>2</Column2>
    <Column3>3</Column3>
    <Column4>4</Column4>
  </Row>
  <Row>
    <Column1>5</Column1>
    <Column2></Column2>
    <Column3></Column3>
    <Column4>8</Column4>
  </Row>
</Content>

UPDATE:

Since you have 50+ columns, I have updated my code to iterate through the column collection. I couldn't use LINQ on dt.Columns since it doesn't implement IEnumerable<T>.

var columnNames = new List<string>();
foreach (DataColumn column in dt.Columns)
{
    columnNames.Add(column.ColumnName);
}

var xlContent = new XElement("Content", from row in dt.AsEnumerable()
                                        select new XElement("Row", from columnName in columnNames
                                                                   select new XElement(columnName, row[columnName]))).ToString();

Hope this helps.

edited Oct 08 '13 at 11:43

answered Oct 08 '13 at 10:23

Channs

2,091
1
15
20

thx but i have mre den 50 columns in my sheet so its nt advisable to write all the column name one by one. is there a way i can achieve using foreach.? – NoviceToProgramming Oct 08 '13 at 10:51
am getting a error at run time when i use the code " The ' ' character, hexadecimal value 0x20, cannot be included in a name.", i have to use some encoding.? this is the last step in my project where am stuck.. any help would be appreciated – NoviceToProgramming Oct 08 '13 at 12:01
i think column name has space e.g: "My Employee" thats why its giving error. any way to ignore space. i use trim() but same error is there – NoviceToProgramming Oct 08 '13 at 12:08
@user2795254 - This [SO thread](http://stackoverflow.com/questions/6219454/efficient-way-to-remove-all-whitespace-from-string) might help. – Channs Oct 08 '13 at 12:08
if i change my columnname "My Employee" to "MyEmployee" its working. but i want to use "My Employee" how can i make it works.? – NoviceToProgramming Oct 08 '13 at 12:22
@user2795254 - XML element names cannot contain spaces. See [here](http://stackoverflow.com/questions/3480887/how-to-include-space-in-xml-tag-element-which-gets-transformed-by-xslt-into-exce). – Channs Oct 08 '13 at 12:34
thx.. all set.. last thing how to save this in xml var xlContent = new XElement("DocumentElement", from row in dt.AsEnumerable() select new XElement("Assets", from columnName in columnNames select new XElement(columnName.Replace(" ",""), row[columnName]))).ToString(); – NoviceToProgramming Oct 08 '13 at 12:57

take blank value in XML

1 Answers1