How do I read nested XML in single flatten object

Question

I have to read nested xml and save into db but the problem is the xml is nested and have to save in single table when i read it with dataset.readxml() method it returns multiple tables but i need single table is there any way to do this? i want result like

author | contact | book | Price

test |155335852| abc | 152

test |155335852| xyz | 1523

<bookinfo>
<author>
<name>test</name>
<contact>155335852</contact>
</author>
<books>
<book>
<title>abc</book>
<price>152</price>
</book>
<book>
<title>xyz</book>
<price>1523</price>
</book>
</books>
</bookinfo>

@JohnyL thanks for comment actually this xml is just for sample purpose i want to show nested xml as you can see there are two main nodes author and book when i read it with dataset.readxml it returns 2 tables but i want single table. — Abid Zahid, May 15 '18 at 07:55
Surely the whole point of a table is that it contains flat data, not nested data? You're asking if you can fit a round peg into a square hole... — Michael Kay, May 15 '18 at 08:58
@AmitKumar there is no fixed xml it can be vary and important nodes will be defined by user and xml will be nested multiple levels but i want it convert it to flat form like nested join between master and child data and want to save in single table. — Abid Zahid, May 15 '18 at 09:07
@AbidZahid : I still didn't get what be your structure in xml, if you can add one more author and book node then it would help me to solve your problem. — Amit Kumar, May 15 '18 at 09:14

score 0 · Answer 1 · answered May 15 '18 at 09:16

Use xml Linq. The DataSet/DataTable ReadXml() method doesn't work well when the xml file have lots of nested tags. The results is a fragmented dataset that can not be combined into anything useful. So you have to manually parse the xml. See code below. :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApplication44
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Author", typeof(string));
            dt.Columns.Add("Contact", typeof(string));
            dt.Columns.Add("Book", typeof(string));
            dt.Columns.Add("Price", typeof(decimal));

            XDocument doc = XDocument.Load(FILENAME);
            foreach(XElement bookInfo in doc.Descendants("bookinfo"))
            {
                string author = (string)bookInfo.Descendants("name").FirstOrDefault();
                string contact = (string)bookInfo.Descendants("contact").FirstOrDefault();

                foreach (XElement book in bookInfo.Descendants("book"))
                {
                    string title = (string)book.Element("title");
                    decimal  price = (decimal)book.Element("price");

                    dt.Rows.Add(new object[] { author, contact, title, price });
                }
            }

        }
    }
}

score 0 · Answer 2 · answered May 15 '18 at 09:19

Some possible ways of storing hierarchic data in a tabular structure are:

(a) blobs or clobs. Just use large binary or character fields in the table with a micro-syntax to represent the hierarchic structure. The micro-syntax could even be XML. Downside: you have to parse the data on every access; query becomes difficult

(a) shredding. Reduce the data to its most primitive form and use one of the well-known techniques for tabular representation of hierarchic data: see What are the options for storing hierarchical data in a relational database? Downside: assembling data in usable form typically requires access to dozens or hundreds of rows, and complex multi-way joins.

This has been likened to parking your car in a garage overnight by disassembling all the parts, storing each one in the correct shelf location, and then reassembling it in the morning before you drive away. It's not much fun. That's why XML databases are such an attractive alternative.

How do I read nested XML in single flatten object

2 Answers2