XML string to DataTable object c# method - advice needed

Question

In ASP.NET application I have a query onto sql database for some data and one part of the data is XML string. I want to "convert" this XML string into DataTable object once retrieved into ASP.NET application.

I have SQL query which select all feeds (their titles, descriptions) from Feeds table and additional data from related tables (in this case tags of feeds). I retrieve additional data in XML string (I achieve this using FOR XML PATH('') in sql):

SELECT
    F.ID, F.Title, F.Text,
    (SELECT
       T.TagID, T.TagName
    FROM
       sn.Tag AS T
    INNER JOIN
       sn.FeedTag AS FT ON FT.TagID = T.TagID
    WHERE
       FT.FeedID = F.FeedID
    FOR
       XML PATH('tag')
    ) 
    AS FeedTagsXML
FROM
    sn.Feed AS F
WHERE 
    F.ID = 12876

And the XML result of this query is (value of FeedTagsXML column):

<tag>
  <TagID>114</TagID>
  <TagName>Test 1</TagName>
</tag>
<tag>
  <TagID>115</TagID>
  <TagName>Test 2</TagName>
</tag>
<tag>
  <TagID>116</TagID>
  <TagName>test 3</TagName>
</tag>
<tag>
  <TagID>117</TagID>
  <TagName>Test 4</TagName>
</tag>

Now all I need is to convert this string into DataTable object. Currently I have a method which returns DataTable for this xml string:

public DataTable GetDataTableFromXML(string xmlStringForDataTable)
{
    //read into dataset
    DataSet dataSet = new DataSet();
    xmlStringForDataTable = "<items>" + xmlStringForDataTable+ "</items>";
    dataSet.ReadXml(new StringReader(xmlStringForDataTable));
    DataTable dt = new DataTable();
    //return single table inside of dataset
    if (dataSet.Tables.Count > 0)
    {
       dt = dataSet.Tables[0];
    }
    return dt;
}

This approach kinda works but I am sure there are better solutions than this. One of the things I don't like for example is appending additional root element to whole string to be able to convert it (if I don't do this whole method fails) and also using DataSet for simple DataTable. How could I improve this method / is there some way (I don't know about yet) to convert xml string to DataTable C# object in a much cleaner way?

Bravo · Accepted Answer · 2012-05-08 05:03:15Z

3

I don't know, what you actually need, but i think this would be the simplest solution ever

private DataTable convertStringToDataTable(string xmlString)
{
   DataSet dataSet = new DataSet();
   StringReader stringReader = new StringReader(xmlString);
   dataSet.ReadXml(stringReader);
   return dataSet.Tables[0];
}

This might not be of much help for you now, i hope it will help others searching for it.

answered May 8, 2012 at 5:03

Bravo

3,4814 gold badges31 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hoodaticus · Accepted Answer · 2011-08-01 18:18:35Z

0

For starters, you could use DataTable.ReadXML, if you are against using a DataSet.

answered Aug 1, 2011 at 18:18

hoodaticus

3,9001 gold badge20 silver badges28 bronze badges

1 Comment

Janez Over a year ago

I've tried that. It returns error on using new StringReader(xmlStringForDataTable) inside DataTable.ReadXml()

Collectives™ on Stack Overflow

XML string to DataTable object c# method - advice needed

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related