c# - Linq 到 XML : generic approach

Question

假设我有以下 xml。

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

我正在尝试实现 3 个小时，但未能通过此 xml 插入数据库。通过下面的代码，我得到了代码中注释的结果。

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}')",
                            tableName,
                            String.Join(",",columns),
                            String.Join(",",values));


    Console.WriteLine(command);
    //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')
}

如果这是一个愚蠢的帖子，我很抱歉，但我无法打破这个......如何构建 propper 命令？

提前致谢。

Answer 1

您正在寻找一个 SelectMany在 record 上节点(从语义上讲，您希望在每个 record 中找到每个 table，但是有很多)。方法如下:

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));

commands 中的结果值是:

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')