converting xml to a datatable from a string c#?

Question

I am trying to get xml string from an attachment using IMAPX which later saves the data to byte[]. and after conversion, the string has linebreaks represented by \r\n.

byte[] att = w.FileData;
str = System.Text.Encoding.Default.GetString(att);

the string seems to have "\r\n" breaks instead of whitespace and new lines

str = str.Replace("\r\n", " ");

when I try to read this Xml using a string reader to read the Xml

StringReader sr = new StringReader("your xml");
DataSet ds = new DataSet();
ds.ReadXml(sr);

it doesn't seem that the program can understand the code in such format. I have tried to apply linebreaks in the Xml instead of \r\n like using &#xA ; instead but without any success. the Xml works fine and converts to a datatable if it was retrieved from a file on the system but I want to be able to create the datatable from the string directly

original xml:

<?xml version="1.0" standalone="yes"?>

     <Data>
           <ID>931304</ID>
           <Age>26</Age>

     </Data>
     <Data>
           <ID>932160</ID>
           <Age>26</Age>
     </Data>
 <Data />

it becomes like this on reading the byte[] att to a string

<?xml version="1.0" standalone="yes"?> \r\n <DocumentElement> \r\n  <Data>     \r\n <ID>931304</ID>  \r\n  <Age>26</Age>   \r\n      </Data> \r\n   <Data> \r\n               <ID>932160</ID>     \r\n          <Age>26</Age>     \r\n    </Data> \r\n     <Data /> \r\n </DocumentElement>

the ds.ReadXml(sr);

does not recognize the \r\n, and even if it was replaced with a blank space.

score 1 · Accepted Answer · edited May 23 '17 at 12:22

1

You can not transfer any xml into datatable, Your xml is wrong for convert.

<?xml version="1.0" standalone="yes"?> <-- why "?" this at the start and end.
...<Data /> at the end

Either you remove the first line and last line, which is valid xml , then it convert into datatable

or

You can use linq for this typeof data.

Loading XML String into datatable

or

You can use loadxml method and then loop for each node like

Finding header value in xml

How to display DataTable XML from WriteXml in a nicely readable format?

Updated or you can use schema for this.

First create a schema - (How to convert complex XML to .NET Class?)

then just parse the xml to schema and convert into datatable.

how to convert xml to DataTable in C#

edited May 23 '17 at 12:22

Community

1
1

answered Mar 21 '15 at 07:17

Ajay2707

5,690
6
40
58

Thank you for your reply, the Xml is fine, in fact it is the xml generated by C# from a previous session. Parsing the Xml is a good idea but I have found an easier way to make the xml recognizable; by creating a schema and feeding it to the datatable. – Falah Abu Hassan Mar 23 '15 at 05:47
Yes, that is good, but you have to create a schema for every result set. If it is fixed, then good to with schema, if dynamic , then something do with linq or else. – Ajay2707 Mar 23 '15 at 05:48

Ravi M Patel · Answer 2 · 2015-03-20T19:00:30.007

0

The DataSet.ReadXml method accepts file name as string (Documentation). You cannot pass the xml itself as string. You have to convert the string into stream to do that.

You'll have to do something like this:

    StringReader sr = new StringReader("your xml");
    DataSet ds = new DataSet();
    ds.ReadXml(sr);

edited Mar 20 '15 at 19:00

answered Mar 20 '15 at 18:45

Ravi M Patel

2,905
2
23
32

thank you for your reply. I have used a string reader in my code, but I have mistakenly omitted it from the explanation above. The problem is not in the conversion itself, the problem is in the \r\n that replaces the newlines in the original xml. Isn't there a method to replace \r\n with a new line in a string? The Xml cannot recognize the tags unless they are neatly written on separate lines – Falah Abu Hassan Mar 21 '15 at 06:34

score 0 · Answer 3 · answered Mar 23 '15 at 05:57

I have managed to create the datatable from a string although it wasn't readable by

StringReader sreader = new StringReader(string);
DataTable.ReadXml(sreader);

the trick is to feed the DataTable with the schema of your data

Data.ReadXmlSchema("yourschema.xsd");

without constructing the table in the program then feeding the data using the StringReader() in ReadXml() as above;

converting xml to a datatable from a string c#?

3 Answers3