CODEWITHSUNDEEP
c#xmllinq
scarpacci
scarpacci

Reputation: 9194

How to flatten this XML

I am receiving an xml message like the below (Believe me this is NOT how I want it):

<items>
 <item item="A" position="0">
   <itemvalue>10</itemvalue>
 </item>
  <item item="A" position="1">
    <itemvalue>20</itemvalue>
 </item>
  <item item="A" position="2">
    <itemvalue>30</itemvalue>
 </item>
  <item item="B" position="0">
    <itemvalue>10</itemvalue>
  </item>
   <item item="B" position="1">
     <itemvalue>20</itemvalue>
  </item>
   <item item="B" position="2">
     <itemvalue>30</itemvalue>
 </item>
</items>

I am shredding the XML using LINQ. So what I get is a List of Item | Position | Value. How I really want the data is to match my table structure. 

Item  | Column1 | Column2 | Column3
A         10        20        30

What is the best way for me to take that List and Build a separate Object I can pass to the DB. Right now I am getting the distinct list of Items (So A & B here) and then passing that into a Lambda expression so that I can say give me the value where Item = A and Position = X (0,1,2).

Just wondering what the best method would be to "flatten" this poorly structured XML.

Upvotes: 0

Views: 1495

Answers (5)

Nicholas Carey
Nicholas Carey

Reputation: 74277

You could write an XSLT to rearrange things, I have a deep aversion to using XML as a programming language. Whoof!

Something like this ought to do the trick, though. A little XML serialization and a pretty simple transform:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Xml;
using System.Xml.Serialization;

namespace ConsoleApplication22
{

    [XmlRoot( "items" )]
    public class ItemList
    {
        public static ItemList CreateInstance( string xml )
        {
            ItemList instance = null ;

            using ( TextReader tr = new StringReader( xml ) )
            {
                XmlSerializer serializer = new XmlSerializer(typeof(ItemList)) ;

                instance = (ItemList) serializer.Deserialize( tr ) ;

            }

            return instance ;

        }

        public SimpleItem[] Simplify()
        {
            return Items.OrderBy( x => x.Name     )
                        .ThenBy(  x => x.Position )
                        .GroupBy( x => x.Name , x => x , (name,group) => SimpleItem.CreateInstance(name,group) )
                        .ToArray()
                        ;
        }

        [XmlElement("item")]
        public Item[] Items { get ; set ; }

    }

    public class Item
    {

        [XmlAttribute("item")]
        public string Name { get ; set ; }

        [XmlAttribute("position")]
        public int Position { get ; set ; }

        [XmlElement("itemvalue")]
        public int Value { get ; set ; }

    }

    public class SimpleItem
    {

        public static SimpleItem CreateInstance( string name , IEnumerable<Item> items )
        {
            List<int> values = new List<int>() ;
            int       i      = 0 ;
            foreach( Item item in items.OrderBy( x => x.Position ) )
            {
                if ( item.Position != i++ ) throw new InvalidOperationException("bad data") ;
                values.Add(item.Value) ;
            }
            SimpleItem instance = new SimpleItem(name , values.ToArray() ) ;

            return instance ;
        }

        private SimpleItem( string name , int[] values )
        {
            this.Name    = name   ;
            this.Columns = values ;
            return ;
        }

        public string Name    { get ; private set ; }
        public int[]  Columns { get ; private set ; }

    }

    class Program
    {

        static void Main( string[] args )
        {
            string xml = @"
<items>
 <item item=""A"" position=""0"">
   <itemvalue>10</itemvalue>
 </item>
  <item item=""A"" position=""1"">
    <itemvalue>20</itemvalue>
 </item>
  <item item=""A"" position=""2"">
    <itemvalue>30</itemvalue>
 </item>
  <item item=""B"" position=""0"">
    <itemvalue>10</itemvalue>
  </item>
   <item item=""B"" position=""1"">
     <itemvalue>20</itemvalue>
  </item>
   <item item=""B"" position=""2"">
     <itemvalue>30</itemvalue>
 </item>
</items>
" ;

            ItemList     instance = ItemList.CreateInstance(xml) ;
            SimpleItem[] items    = instance.Simplify() ;

            return ;
        }

    }

}

Upvotes: 1

Alex
Alex

Reputation: 35409

Code:

static void Main(string[] args)
{
    var xml = XDocument.Parse("<items><item item=\"A\" position=\"0\"><itemvalue>10</itemvalue></item><item item=\"A\" position=\"1\"><itemvalue>20</itemvalue>"
        + "</item><item item=\"A\" position=\"2\"><itemvalue>30</itemvalue></item><item item=\"B\" position=\"0\"><itemvalue>10</itemvalue>"
        + "</item><item item=\"B\" position=\"1\"><itemvalue>20</itemvalue></item><item item=\"B\" position=\"2\"><itemvalue>30</itemvalue>"
        + "</item></items>").Root;

    var keys = xml.Elements()
                  .GroupBy(x => x.Attribute("item").Value)
                  .Select(x => x.Key);

    var flattened = new XDocument();

    flattened.Add(new XElement("flattened"));

    foreach (var item in keys)
    {
        var elements = xml.Elements().Where(x => x.Attribute("item").Value == item);

        flattened.Root.Add(new XElement("Item", new XAttribute("Item", elements.First().Attribute("item").Value)
            , new XAttribute("Column1", elements.First().Element("itemvalue").Value)
            , new XAttribute("Column2", elements.ElementAt(1).Element("itemvalue").Value)
            , new XAttribute("Column3", elements.Last().Element("itemvalue").Value)));
    }

    Console.WriteLine(flattened.ToString());

    Console.ReadLine();
}

Result:

enter image description here

Upvotes: 2

KV Prajapati
KV Prajapati

Reputation: 94645

Read xml and group on item attribute.

var result = from ele in doc.Descendants("item")
             group ele by ele.Attribute("item").Value into grp
            select grp;
foreach (var   t in result)
 {
  XElement[] ar = t.ToArray();
  Console.WriteLine(t.Key + " "+ ar[0].Value  + " " + ar[1].Value + " " + ar[2].Value    );
}

Upvotes: 1

Bryan Naegele
Bryan Naegele

Reputation: 660

If you have no control over the format of the xml the best thing I can think of would use structs and instantiate/populate an instance in your lambda, outputting the objects to a list. You're on the right track.

Upvotes: 0

Jonathan Allen
Jonathan Allen

Reputation: 70317

What you are doing is probably the best way. There is some magic you can try with XSLT transformations, but they tend to be brittle when faced with pathological formats like the one you are stuck with.

Upvotes: 0

Related Questions