Linq to xml: generic approach

suppose I have the following xml.

<?xml version="1.0" encoding="utf-8"?>
    <table name="Product">
                <field name="Id" value="1"/>
                <field name="Description" value="iPhone"/>
                <field name="Id" value="2"/>
                <field name="Description" value="iPad"/>
    <table name="Car">
                <field name="Id" value="1"/>
                <field name="Name" value="Freelander"/>
                <field name="Brand" value="Land rover"/>
                <field name="Id" value="2"/>
                <field name="Name" value="Evoque"/>
                <field name="Brand" value="Land Rover"/>

I'm trying to implement 3 hours trying unsuccessfully to insert into the database through this xml. I've got, through the code below, the results commented in the code.

var filePath = "C:\\xml.xml";
XElement xml = XElement.Load(filePath);

foreach (var t in xml.Descendants("table"))
    var tableName = t.Attribute("name").Value;  

    var columns = t.Descendants("field").Select(c=>c.Attribute("name").Value).Distinct();

    var values = t.Descendants("field").Select(c=>c.Attribute("value").Value);

    var command = String.Format("insert into {0} ({1}) values ('{2}')",

    //First pass: insert into Product (Id,Description) values ('1,iPhone,2,iPad')
    //Second pass: insert into Car (Id,Name,Brand) values ('1,Freelander,Land rover,2,Evoque,Land Rover')

I'm sorry if it's a dumb post, but i couldn't break this... How can I build the propper command?

Thanks in advance.


You are looking to do a SelectMany on the record node (semantically, you want each record found in each table, however many there are). Here's how:

var commands = (from table in xml.Descendants("table")
                from record in table.Descendants("record")
                let tableName = (string)table.Attribute("name")
                let fields = record.Descendants("field")
                let fieldNames = string.Join(", ", fields.Attributes("name").Select(n => n.Value).ToArray())
                let fieldValues = string.Join(", ", fields.Attributes("value").Select(v => string.Format("'{0}'", v.Value.Replace("'", "''"))).ToArray())
                select string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tableName, fieldNames, fieldValues));

The resulting values in commands are:

INSERT INTO Product (Id, Description) VALUES ('1', 'iPhone') 
INSERT INTO Product (Id, Description) VALUES ('2', 'iPad') 
INSERT INTO Car (Id, Name, Brand) VALUES ('1', 'Freelander', 'Land rover') 
INSERT INTO Car (Id, Name, Brand) VALUES ('2', 'Evoque', 'Land Rover') 

The problem that you're dealing with is that you have multiple records per table and you should use union all to insert multiple records in one statement. The code below is a little clumsy, but it'll do what you want.

var doc = XElement.Load(@"C:\xml.xml");
var commands = doc.Descendants("table")
    var tableName = t.Attribute("name").Value;
    var columns = t.Descendants("field")
                    .Select(e => e.Attribute("name").Value)
    var records = t.Descendants("record")
        .Select(r => r.Descendants("field")
                    .Select(e => e.Attribute("value").Value));
    var recordValues = records.Select(x => String.Join(", ", x.Select(y=>String.Format("'{0}'", y)).ToArray()));
    var selects = recordValues.Select(rv => String.Format("select {0}",rv)).ToArray();
    return String.Format("insert into {0} ({1}) {2}", tableName, 
                        String.Join(", ", columns), 
                        String.Join(" union all ", selects.ToArray()));

The (formatted) output is:

insert into Product (Id, Description) 
select '1', 'iPhone' 
union all 
select '2', 'iPad' 

insert into Car (Id, Name, Brand) 
select '1', 'Freelander', 'Land rover' 
union all 
select '2', 'Evoque', 'Land Rover' 

Need Your Help

Rails logger level changes to ERROR abruptly

ruby-on-rails ruby ruby-on-rails-3 logging thread-safety

I am experiencing very strange behavior with my Rails logs. Wherein the logger level automatically/abruptly changes to ERROR(:error. logger level code 3) instead of whatever it was supposed to be (...

Dynamic Row Level Security In a SQL Server Database Using Extended Properties

sql-server security sql-server-2012 extended-properties

We have a requirement to provide customer access to a staging database so that they can extract their data into their own servers, but every table contains all customers data. All of the tables hav...