我可以使用Linq“拉平" XDocument吗? [英] Can I 'flatten' an XDocument with Linq?

问题描述

我有一个高度嵌套的XML文档，需要将其加载到数据库中以进行其他处理.由于超出了本讨论范围的各种原因，我需要压缩"该结构，然后将其加载到DataTables中，然后我可以将SQLBulkCopy到要对其进行处理的db中.因此，假设我的原始XML看起来像这样(我的XML嵌套得更多，但这是基本思想):

I have a heavily nested XML document that I need to load into my db for additional processing. For various reasons beyond the scope of this discussion I need to 'flatten' that structure down, then load it into a DataTables and then I can SQLBulkCopy it into the db where it will get processed. So assume my original XML looks something like this (mine is even more heavily nested, but this is the basic idea):

<data>
    <report id="1234" name="XYZ">
        <department id="234" name="Accounting">
            <item id="ABCD" name="some item">
                <detail id="detail1" value="1"/>                    
                <detail id="detail2" value="2"/>                    
                <detail id="detail3" value="3"/>                    
            </item>
        </department>      
    </report>
 </data>   

，我想将其简化为单个(尽管是冗余的)表结构，其中每个属性都变成一列(即ReportId，ReportName，DepartmentId，DepartmentName，ItemId，ItemName，Detail1，Detail2，Detail3).

and I want to flatten that down into a single (albeit redundant) table structure where each attribute becomes a column (i.e. ReportId, ReportName, DepartmentId, DepartmentName, ItemId, ItemName, Detail1, Detail2, Detail3).

所以我的问题很简单:是否可以通过一个简单的Linq查询来完成此任务?"过去，我只写一些XSLT并完成它，但是我很好奇Linq库是否可以完成相同的工作?

So my question is simply 'is it possible to accomplish this with a simple Linq query'? In the past I would just write some XSLT and be done with it but I'm curious if the Linq library can accomplish the same thing?

谢谢！

推荐答案

这是您要寻找的吗?

var doc = XDocument.Load(fileName);
var details =
    from report in doc.Root.Elements("report")
    from department in report.Elements("department")
    from item in department.Elements("item")
    from detail in item.Elements("detail")
    select new
    {
        ReportId = (int)report.Attribute("id"),
        ReportName = (string)report.Attribute("name"),
        DepartmentId = (int)department.Attribute("id"),
        DepartmentName = (string)department.Attribute("name"),
        ItemId = (string)item.Attribute("id"),
        ItemName = (string)item.Attribute("name"),
        DetailId = (string)detail.Attribute("id"),
        DetailValue = (int)detail.Attribute("value"),
    };

如果您希望将其作为DataTable，则可以使用以下扩展方法:

If you want it as a DataTable, you can use the following extension method:

public static DataTable ToDataTable<T>(this IEnumerable<T> source)
{
    PropertyInfo[] properties = typeof(T).GetProperties()
                                         .Where(p => p.CanRead && !p.GetIndexParameters().Any())
                                         .ToArray();

    DataTable table = new DataTable();
    foreach (var p in properties)
    {
        Type type = p.PropertyType;
        bool allowNull = !type.IsValueType;
        if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
        {
            allowNull = true;
            type = Nullable.GetUnderlyingType(type);
        }
        DataColumn column = table.Columns.Add(p.Name, type);
        column.AllowDBNull = allowNull;
        column.ReadOnly = !p.CanWrite;
    }

    foreach (var item in source)
    {
        DataRow row = table.NewRow();
        foreach (var p in properties)
        {
            object value = p.GetValue(item, null) ?? DBNull.Value;
            row[p.Name] = value;
        }
        table.Rows.Add(row);
    }

    return table;
}

像这样使用它:

var table = details.CopyToDataTable();

这篇关于我可以使用Linq“拉平" XDocument吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！