Store data from XML in relational SQL database

Question

I need to store data from an XML file into SQL database with 2 tables. The XML file looks like this (the full file has more Document nodes):

<Documents>
    <Document>
        <Number>110</Number>
        <Date>2020-10-23</Date>
        <TotalPrice>3800</TotalPrice>
        <Items>
            <Item>
                <SKU>001234</SKU>
                <Name>FirstItem</Name>
                <Quantity>10</Quantity>
                <Price>1550</Price>
            </Item>
            <Item>
                <SKU>001235</SKU>
                <Name>SecondItem</Name>
                <Quantity>8</Quantity>
                <Price>1200</Price>
            </Item>
            <Item>
                <SKU>001236</SKU>
                <Name>ThirdItem</Name>
                <Quantity>21</Quantity>
                <Price>1050</Price>
            </Item>
        </Items>
    </Document>
</Documents>

The SQL database has 2 tables. One for Documents, and the other one for Items.

create table Document(
DocumentId int identity(1,1) not null primary key,
Number int not null,
[Date] date not null,
TotalPrice money not null
);

create table Item(
ItemId int identity(1,1) not null primary key,
SKU int not null,
[Name] nvarchar(30) not null,
Quantity int not null,
Price money not null,
DocumentId int not null foreign key references Document(DocumentId)
);

Previously, I've been storing only simple XML files into SQL databases with only one table. The way that I was doing it (let's say that we just have Document table, and we can ignore Items):

DocumentMetadata.cs

    [Serializable]
    [XmlRoot("Document")]
    public class DocumentMetadata
    {
        [XmlElement("Number")]
        public int Number { get; set; }

        [XmlElement("Date")]
        public DateTime Date { get; set; }

        [XmlElement("TotalPrice")]
        public int TotalPrice { get; set; }
    }

    [MetadataType(typeof(DocumentMetadata))]
    public partial class Document
    {

    }

Example.cs

XDocument xDoc = XDocument.Load(XmlFile);

List<Document> documentList = xDoc.Descendants("Document").Select(document => new Document
{
    Number = Convert.ToInt32(document.Element("Number").Value),
    Date = Convert.ToDateTime(document.Element("Date").Value),
    TotalPrice = Convert.ToInt32(document.Element("TotalPrice").Value),
}).ToList();


using (DocumentsEntities entity = new DocumentsEntities())
{
    foreach (var doc in documentList)
    {
        var dbDoc = entity.Documents.Where(x => x.Number.Equals(d.Number)).FirstOrDefault();

        if (dbDoc != null)
        {
            dbDoc.Number = doc.Number;
            dbDoc.Date = doc.Date;
            dbDoc.TotalPrice = doc.TotalPrice;
        }
        else
        {
            entity.Documents.Add(doc);
        }
    }
        entity.SaveChanges();
    }

Since I have a bit more complex XML now to work with, and 2 related database tables, my head is all over the place. What would be the best approach in this situation? Could you point me in the right direction? Thanks in advance.

Answer 1

I don't see the point in shredding the XML using complex C# code. SQL Server can do this pretty neatly

INSERT Document
  (Number, [Date], TotalPrice)
SELECT
  x.doc.value('(Number/text())[1]','int'),
  x.doc.value('(Date/text())[1]','date'),
  x.doc.value('(TotalPrice/text())[1]','money')
FROM @xml.nodes('Documents/Document') x(doc);

INSERT Item
  (SKU, [Name], Quantity, Price, DocumentId)
SELECT
  x2.item.value('(SKU/text())[1]','int'),
  x2.item.value('(Name/text())[1]','nvarchar(30)'),
  x2.item.value('(Quantity/text())[1]','int')
  x2.item.value('(Price/text())[1]','money'),
FROM @xml.nodes('Documents/Document') x(doc)
JOIN Document d ON d.Number = x.doc.value('(Number/text())[1]','int')
CROSS APPLY x.doc.nodes('Item') x2(item);

Your C# code could be something like

const string sql = @"
THE ABOVE SQL
";

using (DocumentsEntities entity = new DocumentsEntities())
{
    entity.ExecuteSqlCommand(sql, new SqlParameter("@x", xDoc));
}

If you need the IDENTITY ID numbers you can use an OUTPUT clause with entity.FromSqlQuery