0

I'm trying to load an XML file into an embedded database (SQLite) using .NET. The best way I've found to do this is to read the data into a DataTable/DataSet and then use a DataAdapter to Update that DataTable into the database table.

The problem is, the XML file I have to work with has data that isn't in the root node. There's the root node (tables), then a subnode (tableXXX) and all of the data is in the subnode.

When I use ReadXML (for the DataSet object), it simply reads in a single record (containing the name of the subnode table). I want to completely ignore the root node and treat the first subnode as if it is the root node.

How would I go about doing this?

(Or, if there's an easier way to load an XML file to a database, I'd be interested in hearing that as well, although I'm guessing I'll still need to work around this root node issue).

WATYF

WATYF
  • 409
  • 1
  • 5
  • 16

1 Answers1

0
string xml = "<tables><table1><col1>xyz</col1></table1></tables>";

DataSet ds = new DataSet();
ds.ReadXml(XDocument.Parse(xml).Root.Element("table1").CreateReader());

var value = ds.Tables[0].Rows[0]["col1"].ToString(); //<-- xyz

EDIT

  • XDocument is in System.Xml.Linq namespace

  • the code can be as

    ds.ReadXml(XDocument.Parse(xml).Root.Elements().First().CreateReader());

I4V
  • 34,891
  • 6
  • 67
  • 79
  • A couple questions. First, where is the XDocument object? Second, I don't have a fixed subnode name. The first few lines of the file look like this: The problem is that the table name ("MyTableName") changes, and I don't have control over that. How can I account for that using this method?
     – WATYF Sep 03 '13 at 20:17
  • Well, that didn't work. I got a design-time error saying that there was no "First" object in "Elements". – WATYF Sep 04 '13 at 15:50
  • I ended up trying several other methods of XDocument. "Parse" didn't work because I need to read the XML from a file (not an in-memory string). I found I was able to load the data using XDocument.ReadFrom. I created an XMLReader, used ReadFrom to create an XElement, and then used XElement.CreateReader. The problem is that my xml file is terribly formatted (in more ways than I had imaged), making it indecipherable by ds.ReadXML. It ends up that the data WAS being loaded by a simple ds.ReadXML("filepath"), but that it was creating multiple tables, none of which contained data in a useful format. – WATYF Sep 04 '13 at 16:01
  • All that to say, your suggestion lead me to something (XDocument) which can be used to selectively pass parts of an XML file to ReadXML, so I will give you credit for the answer, even though the file I'm stuck with is so horribly formatted that XDocument isn't sufficient to save it. :o) – WATYF Sep 04 '13 at 16:02