Read [for xml auto, elements] into DataTable in ADO.NET

Question

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!

I'm not sure I understand your question; are you trying to shred the XML you receive back out into a table? If so, then why are you turning it into XML in the first place? — Stuart Ainsworth, Dec 19 '10 at 15:10

score 0 · Answer 1 · answered Feb 03 '16 at 04:45

DataSet retTable = new DataSet();
SqlConnection con = new SqlConnection("data source=servername;initial catalog=databasename;uid=userid;pwd=password");
SqlCommand cmd = new SqlCommand("GetMyXmlDate", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
string xmlDoc = "";
using (XmlReader reader = cmd.ExecuteXmlReader())
{
  while (reader.Read())
  {
    xmlDoc  = xmlDoc +  reader.ReadOuterXml();
  }
}

var dataStream = new MemoryStream();
var dataWriter = new StreamWriter(dataStream);
dataWriter.Write(xmlDoc);
dataWriter.Flush();
dataStream.Position = 0;
retTable.ReadXml(dataStream);
Grid.DataSource = retTable.Tables[0].DefaultView;

David · Accepted Answer · 2010-12-21T15:57:43.677

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.

Unfortunately SP returns that XML, I can't change that hard SP as it uses in many places. Thanks for reply! — ihorko, Dec 21 '10 at 15:57

Read [for xml auto, elements] into DataTable in ADO.NET

2 Answers2