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.

Are you open for a stored procedure based solution in T-SQL? — Yitzhak Khabinsky, Mar 28 '22 at 23:01
I would like to learn how to do it that way as well, but in this case it needs to be done in C# only, without using stored procedures. @Yitzhak — Oggie, Mar 29 '22 at 00:09
Why does it "need to be done in C# only"? You could create a SQL temp table with an XML column - load the entire document there and then use a Stored Procedure to extract and load the values. Also possible to have the XML schema defined in SQL to help — Martin Cairney, Mar 29 '22 at 02:36
Is your Document.Number value unique? If so then you could use this as the key in your Document table and then you will have access to the same value to use as the FK in the item table -that is probably your simplest approach as you can then load each table separately while maintaining referential integrity — Martin Cairney, Mar 29 '22 at 02:39

score 0 · Answer 1 · answered Mar 29 '22 at 12:01

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

Store data from XML in relational SQL database

1 Answers1