CODEWITHSUNDEEP
c#xmllinqdatatable
user3276191
user3276191

Reputation: 29

XML to Datatable LINQ

I’m very new to Linq and c# so sorry for my ignorance. Below is my xml.

<FormData FormOID="F_TABLEOFMODAL_V20" OpenClinica:Version="v2.0" OpenClinica:Status="initial data entry">
    <ItemGroupData ItemGroupOID="IG_TABLE_MODALITYTABLE" ItemGroupRepeatKey="3" TransactionType="Insert">
        <ItemData ItemOID="I_TABLE_MODAL_DATE_TABLE" Value="2014-04-10" />
        <ItemData ItemOID="I_TABLE_MODAL_TYPE_TABLE" Value="4" />
    </ItemGroupData>
    <ItemGroupData ItemGroupOID="IG_TABLE_MODALITYTABLE" ItemGroupRepeatKey="1" TransactionType="Insert">
        <ItemData ItemOID="I_TABLE_MODAL_DATE_TABLE" Value="2014-04-01" />
        <ItemData ItemOID="I_TABLE_MODAL_TYPE_TABLE" Value="2" />
    </ItemGroupData>
    <ItemGroupData ItemGroupOID="IG_TABLE_MODALITYTABLE" ItemGroupRepeatKey="2" TransactionType="Insert">
        <ItemData ItemOID="I_TABLE_MODAL_DATE_TABLE" Value="2014-04-04" />
        <ItemData ItemOID="I_TABLE_MODAL_TYPE_TABLE" Value="1" />
    </ItemGroupData>
</FormData>

I’m trying to get out ItemData with attribute Value in chunks of two ie(2014-04-10,4) for the first example to be used later. This is what I did and i’m very sure it is not very productive. 

var doc = XDocument.Load(XmlReader.Create(streaming));

XNamespace nsSys = "http://www.cdisc.org/ns/odm/v1.3";

var items = from i in doc.Descendants(nsSys + "ItemData")
            select new
            {
                //item = (string)i.Attribute("ItemOID"),
                val = (string)i.Attribute("Value")
            };

var oddCategories =  items.ToList().Where((c, i) => i % 2 == 0);
var evenCategories = items.ToList().Where((c, i) => i % 2 != 0);


//Put it into a datatable
DataTable modDt = new DataTable();
modDt.Columns.Add(new DataColumn("Date", typeof(int))); //odd values
modDt.Columns.Add(new DataColumn("Type", typeof(int))); //even values

I’m getting xml out (item data) into 1x6 list. Then splitting it by odd and even then putting it back to a datatable to be used. I’m stuck knowing how to put this into a datatable. I'm also wanting to change the date format from 2014-04-01 to 04/01/14 (M/D/Y). Was thinking of doing a 

var itemlist = items.ToArray();  
var skipped = String.Join(",", itemlist.Select(p => p.ToString()).ToArray());
List<string> result = skipped.Split(',').ToList();

for (int i = 0; i < result.Count; i += 2)
{
    var ccc = result[i].ToCharArray();
    var dd = ccc.Skip(10);
    var ff = dd.Take(8);
    var yr = ff.Take(2);
    var mnth = ff.Skip(3).Take(2);
    var dte = ff.Skip(6).Take(2);
    var sss = ee.Concat(mnth).Concat(dte);

 }

Then putting it back to a datatable to be read later. Although that will not allow me to add a \ due to the read only problem. Thank you so much for your suggestions and for your help. Cheers.

Upvotes: 1

Views: 2427

Answers (1)

har07
har07

Reputation: 89325

Check this example :

//loop through <ItemGroupData> to get both date and type value
//from each iteration
var items = from i in doc.Descendants(nsSys + "ItemGroupData")
            select new
            {
                date = (string)i.Element(nsSys + "ItemData").Attribute("Value"),
                type = (string)i.Elements(nsSys + "ItemData").Skip(1).First().Attribute("Value")
            };
DataTable modDt = new DataTable();
modDt.Columns.Add(new DataColumn("Date", typeof(string)));
modDt.Columns.Add(new DataColumn("Type", typeof(int)));

//add LINQ-to-XML query result to DataTable
foreach (var item in items)
{
    //convert item.date string to DateTime  
    //then convert it back to string with different format
    modDt.Rows.Add(DateTime.ParseExact(item.date, "yyyy-MM-dd", CultureInfo.InvariantCulture)
                           .ToString("MM/dd/yyyy"), 
                    int.Parse(item.type);
}

I assumed that XML format is consistent, f.e each <ItemGroupData> always contains 2 <ItemData> elements, and each <ItemData> must have Value attribute.

Upvotes: 1

Related Questions