I have MS SQL Server stored procedure that returns XML (it uses SELECT with for xml auto, elements)
I tried read it into DataTable:
DataTable retTable = new DataTable();
SqlCommand comm = new SqlCommand("exec MySP", connection);
SqlDataAdapter da = new SqlDataAdapter(comm);
connection.Open();
da.Fill(retTable);
but retTable contains 12 rows with separated full xml thar SQL Server returns.
How can I read that XML from DB into DataTable object? Thanks!
What exactly are you trying to accomplish here? It sounds like you're building an XML field in the stored procedure and it's being placed in your DataTable exactly as that, a single XML field. This is expected behavior. So I guess the biggest question, as asked in comments above, is why is the data being formed into XML in the first place if you want it to be a table in the code?
One thing you could do is use LINQ to XML (or some similar approach for XML DOM parsing) to query into the returned results to get the data you need out of the returned fields. It should feel fairly straightforward since you're expecting to be working with table data and LINQ has that "SQL feel" to it.
One important point you may be missing in this question, however, is the structural difference between tables and XML. Tables are relational, XML is hierarchical. They're designed to represent data in entirely different ways. That being said, it's entirely possible that the XML being returned can't be directly translated into a single DataTable object. It just might not represent a table of data.