Best way to import XML into a database C# .NET Core

Question

I'm working on a project that requires that I take data from an XML API that I don't control and insert that data into a database (I control) for consumption by a different application (I control but can't modify code too it)

We already managed to get the full database layout from the XML API via the providers special endpoint but all other queries (to get data) are via XML.

Currently what I have is the following:

Book.cs - This represents one of the database tables

using System;
using System.ComponentModel.DataAnnotations;

namespace Example.Models
{
    public partial class Book
    {
        [Key]
        public int Recordno { get; set; }
        public decimal? Amount { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public DateTime? Whenmodified { get; set; }
        public DateTime? Whencreated { get; set; }
        public int? Createdby { get; set; }
        public int? Modifiedby { get; set; }
    }
}

API Return XML - This represents the XML returned by the API (an SDK already converts it to an XMLDocument Type)

<BOOKS>
   <BOOK>
      <RECORDNO>1</RECORDNO>
      <AMOUNT>24.12</AMOUNT>
      <TITLE>This is a title</TITLE>
      <DESCRIPTION>This is a description</DESCRIPTION>
      <WHENMODIFIED></WHENMODIFIED>
      <WHENCREATED>13/03/20 15:23:12</WHENCREATED>
      <CREATEDBY>3</CREATEDBY>
      <MODIFIEDBY></MODIFIEDBY>
   </BOOK>
   <BOOK>
      <RECORDNO>1</RECORDNO>
      <AMOUNT>24.12</AMOUNT>
      <TITLE>This is another title</TITLE>
      <DESCRIPTION>This is another description</DESCRIPTION>
      <WHENMODIFIED>21/03/20 12:45:23</WHENMODIFIED>
      <WHENCREATED>15/03/20 15:23:12</WHENCREATED>
      <CREATEDBY>6</CREATEDBY>
      <MODIFIEDBY>2</MODIFIEDBY>
   </BOOK>
</BOOKS>

Currently the way we are doing this is via switch statements, but I'm hoping that there is a better method. Partial BookController.cs

var books = new List<Book>();

// response.Data return is a List<XElement>
foreach (var result in response.Data)
{
    var xElements = result.Elements();
    var book = new Book();
    foreach (var element in xElements)
    {
        switch (element.Name.ToString())
        {
            case "RECORDNO":
                book.Recordno = int.Parse(element.Value);
                break;
            case "AMOUNT":
                if (element.Value != string.Empty)
                {
                    book.Amount = int.Parse(element.Value);
                }
                break;
             // etc.
        }
    }
    books.Add(book);
}

I have a feeling that there is a much easier method of doing this that I'm just unaware of, my concern is that I have about fifty tables and hundreds of elements to do making this method inefficient time wise.

score 1 · Answer 1 · answered Apr 15 '20 at 18:04

1

You could do the following:

use XML Schema Definition Tool (Xsd.exe) to generate classes
use generated classes to read the input XML
use that data

answered Apr 15 '20 at 18:04

aybe

15,516
9
57
105

score 1 · Accepted Answer · answered Apr 15 '20 at 18:07

Use xml serialization

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


namespace ConsoleApplication8
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            XmlReader reader = XmlReader.Create(FILENAME);
            XmlSerializer serializer = new XmlSerializer(typeof(Books));
            Books books = (Books)serializer.Deserialize(reader);


        }
    }
    [XmlRoot("BOOKS")]
    public class Books
    {
        [XmlElement("BOOK")]
        public List<Book> books { get; set; }
    }
    public partial class Book
    {
        //[Key]
        [XmlElement("RECORDNO")]
        public int Recordno { get; set; }
        [XmlElement("AMOUNT")]
        public decimal? Amount { get; set; }
        [XmlElement("TITLE")]
        public string Title { get; set; }
        [XmlElement("DESCRIPTION")]
        public string Description { get; set; }
        private DateTime Whenmodified { get;set;}
        [XmlElement("WHENMODIFIED")]
        public string _Whenmodified {
            get { return Whenmodified.ToString("dd/MM/yy HH:mm:ss");  }
            set { DateTime.ParseExact(value,"dd/MM/yy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);}
        }
        private DateTime Whencreated { get; set; }
        public string _Whencreated
        {
            get { return Whencreated.ToString("dd/MM/yy HH:mm:ss"); }
            set { DateTime.ParseExact(value, "dd/MM/yy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture); }
        }
        [XmlElement("CREATEDBY")]
        public int? Createdby { get; set; }
        [XmlElement("MODIFIEDBY")]
        public int? Modifiedby { get; set; }
    }


}

Although this seems to work pretty well, I'm getting an error regarding the DateTime field. FormatException: String '02/04/2020 18:04:05' was not recognized as a valid DateTime. Is this because the value can be null? Can we do null DateTimes? Further if one of the integer values is null in the XML (empty value) it runs into a parsing error. — Matt, Apr 15 '20 at 19:20
the yer is 4 digits and the code is using only a two digit year. — jdweng, Apr 15 '20 at 20:51
Anyway to resolve the null int error I'm getting though (I updated the example XML to reflect the possibility of null in a couple of fields) — Matt, Apr 16 '20 at 00:10
You do not have a null int. You have issues with parsing a datatime. You can modified my get/set code to handle different datetime strings. You can use TryParseExact or the ParseExact can have an array of format strings to handle a variety of string formats. — jdweng, Apr 16 '20 at 03:00

score 0 · Answer 3 · answered Apr 15 '20 at 18:04

Welcome to SO

What you're trying to do here of construct an in-memory C# object from an XML string representation is known as deserialization. There are lots of resources on this (e.g. MSDN), but here's a link to a SO post where the OP was looking to achieve something similar.

Applied to your above code snippet might look something like this:

var books = new List<Book>();

var serializer = new XmlSerializer(typeof(Book));
// response.Data return is a List<XElement>
foreach (var result in response.Data)
{
    var book = (Book)serializer.Deserialize(result.CreateReader());
    books.Add(book);
}

Best way to import XML into a database C# .NET Core

3 Answers3