Reading XML Document in ASP.NET

Question

I have an XML document with funny formatting that I can't seem to get into a dataset.

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="magento">
<table_structure name="cignex_faq">
    <field Field="faq_id" Type="int(10) unsigned" Null="NO" Key="PRI" Extra="auto_increment" />
    <field Field="title" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" />
    <field Field="meta_keywords" Type="text" Null="NO" Key="" Extra="" />
    <field Field="meta_description" Type="text" Null="NO" Key="" Extra="" />
    <field Field="faqcontent" Type="text" Null="NO" Key="" Extra="" />
    <field Field="creation_time" Type="datetime" Null="YES" Key="" Extra="" />
    <field Field="update_time" Type="datetime" Null="YES" Key="" Extra="" />
    <field Field="status" Type="tinyint(1)" Null="NO" Key="" Default="0" Extra="" />
    <field Field="category_ids" Type="text" Null="YES" Key="" Extra="" />
    <field Field="faqrelease_date" Type="datetime" Null="YES" Key="" Extra="" />
    <key Table="cignex_faq" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="faq_id" Collation="A" Cardinality="267" Null="" Index_type="BTREE" Comment="" />
    <options Name="cignex_faq" Engine="InnoDB" Version="10" Row_format="Compact" Rows="276" Avg_row_length="1365" Data_length="376832" Max_data_length="0" Index_length="0" Data_free="0" Auto_increment="323" Create_time="2011-09-12 16:38:02" Collation="utf8_general_ci" Create_options="" Comment="InnoDB free: 1639424 kB" />
</table_structure>
<table_data name="cignex_faq">
<row>
    <field name="faq_id">20</field>
    <field name="title">foo</field>
    <field name="meta_keywords"></field>
    <field name="meta_description"></field>
    <field name="faqcontent">foo</field>
    <field name="creation_time">2010-08-13 21:41:51</field>
    <field name="update_time">2011-10-06 18:52:48</field>
    <field name="status">1</field>
    <field name="category_ids"></field>
    <field name="faqrelease_date">2010-08-13 00:00:00</field>
</row>
<row>
    <field name="faq_id">21</field>
    <field name="title">foo</field>
    <field name="meta_keywords"></field>
    <field name="meta_description"></field>
    <field name="faqcontent">foo</field>
    <field name="creation_time">2010-08-16 20:58:24</field>
    <field name="update_time">2011-10-06 18:52:11</field>
    <field name="status">1</field>
    <field name="category_ids"></field>
    <field name="faqrelease_date">2010-08-16 00:00:00</field>
</row>

</table_data>
</database>
</mysqldump>

Previously, this worked fine:

DataSet dsFaq;
String filePath = Server.MapPath("RU_xml_faq_101311.xml");

dsFaq = new DataSet();
dsFaq.ReadXml(filePath);

foreach (DataRowView r in dvFaq) {
   Response.Write("Importing " + r["title"].ToString() + "<br>");
}

But the formatting on this document isn't the same as I'm used to. Any advice on what ReadXML code I could use to get this stuff to read properly?

What error message are you seeing when you try to load the xml? — Jonny Cundall
– Jonny Cundall, Commented Feb 13, 2012 at 19:53
The "formatting" is different? Can you post an example of one that worked? — John Saunders
– John Saunders, Commented Feb 13, 2012 at 22:52
<DATA> <ROW> <faq_id>20</faq_id> <title>foo</title> <meta_keywords></meta_keywords> <meta_description></meta_description> <faqcontent>foo</faqcontent> <creation_time>2010-08-13 21:41:51</creation_time> <update_time>2011-01-14 17:37:40</update_time> <status>1</status> <category_ids></category_ids> <faqrelease_date>2010-08-13 00:00:00</faqrelease_date> </ROW> </DATA> — mdrussell0779
– mdrussell0779, Commented Feb 13, 2012 at 23:18
Yeah, notice that's totally different. It's not a "formatting" problem. I suggest you may want to find out why they're suddenly sending you totally different data. — John Saunders
– John Saunders, Commented Feb 13, 2012 at 23:28

John Saunders · Accepted Answer · 2012-02-14 00:43:12Z

1

The XML you posted will never get into a DataSet.

The DataSet class is an in-memory representation of a relational database, with tables, with relations between them. This new XML doesn't match that model in any way.

Very brief, partial example:

XDocument doc = XDocument.Load(fileName);
var rows = from row in doc.Root.Element("database").Element("table_data").Elements("row")
           let fields = row.Elements("field")
           select new
                      {
                          FaqId = fields.Where(element => element.Attribute("name").Value == "faq_id").First().Value,
                          Title = fields.Where(element => element.Attribute("name").Value == "title").First().Value,
                          // etc.
                      };

Slightly prettier. You have do add using System.Xml.Xpath;

var rows =
    from row in
        doc.Root.Element("database").Element(
            "table_data").Elements("row")
    select
        new 
            {
                FaqId =row.XPathSelectElement("field[@name='faq_id']").Value,
                Title = row.XPathSelectElement("field[@name='title']").Value,
                // etc.
            };

edited Feb 14, 2012 at 0:43

answered Feb 13, 2012 at 22:53

John Saunders

162k26 gold badges252 silver badges403 bronze badges

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

7 Comments

mdrussell0779 Over a year ago

Would there then be a way to instead read the XML document line by line and take what I need from it by name?

John Saunders Over a year ago

You can do better even than that, using LINQ to XML.

mdrussell0779 Over a year ago

John, that's not really an option.. the nature of this page I'm doing is that I just need to iterate through each <row> and get the value from each field in the row to pass to a function.. I'm just totally green on parsing XML nodes.

John Saunders Over a year ago

What do you mean it's not an option? You don't know anything about it!

mdrussell0779 Over a year ago

Fair enough. :) Wasn't trying to be difficult. I was just hoping for a small snippet of XMLDocument code to parse the file. If you suggest Linq, do you have an example?

|

Pritam Karmakar · Accepted Answer · 2012-02-13 22:38:42Z

0

I'm sure XmlDocument will help you to read this xml.

answered Feb 13, 2012 at 22:38

Pritam Karmakar

2,8015 gold badges32 silver badges49 bronze badges

Collectives™ on Stack Overflow

Reading XML Document in ASP.NET

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related