Solved

Linq to XML Flatten

Posted on 2014-03-05
4
673 Views
Last Modified: 2016-02-10
I'm trying  to transform an XML file and I'm not sure if it can even be done with Linq to XML.

There are a set of nodes (<NEW>) that contain parent and child information at the same level in the XML.  I'll list a sample XML at the bottom of this post.  I don't have the ability to request a change to the xml structure.  It comes from a third party.

The following nodes would be wrapped in an parent node called < NEW >
<Year> (Existing)
<Name> (Existing)
<Tier> (Existing)
<StartDate> (Existing)

Open in new window


These other nodes should become children nodes of the <Deal> nodes that are nested under the <Deals> node which is a child of <NEW>
<Deals> (PROPOSED NODE)
<Deal> (PROPOSED NODE)
<Node1> (Existing)
<Node2> (Existing)
<Node3> (Existing)
<Node4> (Existing)
</Deal> (Closing tag on proposed node)
</Deals> (Closing tag on proposed node)

Open in new window



The node1 - node4 nodes are listed in document order in such a way that each "block" of <Node1> - <Node4> nodes will belong to one <Deal> node.  I've used the values A1-A4, B1-B4, C1-C4 to indicate that these go together but the values for each <Node1> - <Node4> will be different as the nodes repeat as decendants under the <NEW> node.  There may be (0 - many) <Node1> - <Node4> blocks but normally there will be at least one block of those nodes.  The values for <Node1> - <Node4> will also be different with each <Deal> node.  

I need to transform the xml in such a way that I can query the new xml using linq to xml and ultimately flatten the data into rows.  I'd prefer c# code and I'm not too well versed in lambda expressions so if I could get an answer that didn't include lambda expressions, I'd really appreciate it.

The flat part of it would look like this.

Year    Name    Tier    StartDate   Node1   Node2   Node3   Node4   Node1   Node2   Node3   Node4 ....
2013    Bob     0       20140201    A1      A2      A3      A4      B1      B2      B3      B4
2013    Bob     1       20140201    F1      F2      F3      F4      G1      G2      G3      G4
2013    Jill    0       20140201    K1      K2      K3      K4      L1      L2      L3      L4

Open in new window


Attempted Linq to XML Queries on Existing XML (just to try to flatten without transformation)

var query3 = 
from x in doc.Root.Descendants("NEW")
select new
{
	Year = (string)x.Element("Year"),
	Name = (string)x.Element("Name"),
	Tier = (string)x.Element("Tier"),
	StartDate = (string)x.Element("StartDate"),
	Node1s =
		(from zz in x.Elements("Node1")
		select zz),
	Node2s = 
		(from zz in x.Elements("Node2")
		select zz),
	Node3s = 	
		(from zz in x.Elements("Node3")
		select zz),
	Node4s = 	
		(from zz in x.Elements("Node4")
		select zz)
};

var query4 = 
from x in doc.Root.Descendants("NEW")
from a in x.Elements("Node1")
from b in x.Elements("Node2")
from c in x.Elements("Node3")
from d in x.Elements("Node4")
select new
{
	Year = (string)x.Element("Year"),
	Name = (string)x.Element("Name"),
	Tier = (string)x.Element("Tier"),
	StartDate = (string)x.Element("StartDate"),
	Node1 = (string)a,
	Node2 = (string)b,
	Node3 = (string)c,
	Node4 = (string)d
};

Open in new window



Linq to XML query on proposed xml transformed that seems to flatten the data

var query =
	from a in doc.Root.Descendants("NEW")
	from b in a.Elements("Deals")
	from c in b.Elements("Deal")
		select new
		{
			Year = (string)a.Element("Year"),
			Name = (string)a.Element("Name"),
			Tier = (string)a.Element("Tier"),
			StartDate = (string)a.Element("StartDate"),
			Node1 = (string)c.Element("Node1"),
			Node2 = (string)c.Element("Node2"),
			Node3 = (string)c.Element("Node3"),
			Node4 = (string)c.Element("Node4")
		};

Open in new window



Snippet of existing XML

<Root>
  <NEW>
    <Year>2013</Year>
    <Name><![CDATA[Bob]]></Name>
    <Tier>0</Tier>
    <StartDate>20140201</StartDate>
    <Node1>A1</Node1>
    <Node2>A2</Node2>
    <Node3>A3</Node3>
    <Node4>A4</Node4>
    <Node1>B1</Node1>
    <Node2>B2</Node2>
    <Node3>B3</Node3>
    <Node4>B4</Node4>
    <Node1>C1</Node1>
    <Node2>C2</Node2>
    <Node3>C3</Node3>
    <Node4>C4</Node4>
    <Node1>D1</Node1>
    <Node2>D2</Node2>
    <Node3>D3</Node3>
    <Node4>D4</Node4>
    <Node1>E1</Node1>
    <Node2>E2</Node2>
    <Node3>E3</Node3>
    <Node4>E4</Node4>
  </NEW>
  <NEW>
    <Year>2013</Year>
    <Name><![CDATA[Bob]]></Name>
    <Tier>1</Tier>
    <StartDate>20140201</StartDate>
    <Node1>F1</Node1>
    <Node2>F2</Node2>
    <Node3>F3</Node3>
    <Node4>F4</Node4>
    <Node1>G1</Node1>
    <Node2>G2</Node2>
    <Node3>G3</Node3>
    <Node4>G4</Node4>
    <Node1>H1</Node1>
    <Node2>H2</Node2>
    <Node3>H3</Node3>
    <Node4>H4</Node4>
    <Node1>I1</Node1>
    <Node2>I2</Node2>
    <Node3>I3</Node3>
    <Node4>I4</Node4>
    <Node1>J1</Node1>
    <Node2>J2</Node2>
    <Node3>J3</Node3>
    <Node4>J4</Node4>
  </NEW>
  <NEW>
    <Year>2013</Year>
    <Name><![CDATA[Jill]]></Name>
    <Tier>0</Tier>
    <StartDate>20140201</StartDate>
    <Node1>K1</Node1>
    <Node2>K2</Node2>
    <Node3>K3</Node3>
    <Node4>K4</Node4>
    <Node1>L1</Node1>
    <Node2>L2</Node2>
    <Node3>L3</Node3>
    <Node4>L4</Node4>
    <Node1>M1</Node1>
    <Node2>M2</Node2>
    <Node3>M3</Node3>
    <Node4>M4</Node4>
    <Node1>N1</Node1>
    <Node2>N2</Node2>
    <Node3>N3</Node3>
    <Node4>N4</Node4>
    <Node1>P1</Node1>
    <Node2>P2</Node2>
    <Node3>P3</Node3>
    <Node4>P4</Node4>
  </NEW>
</Root>

Open in new window


Snippet of Proposed Transformed XML

<Root>
  <NEW>
    <Year>2013</Year>
    <Name><![CDATA[Bob]]></Name>
    <Tier>0</Tier>
    <StartDate>20140201</StartDate>
    <Deals>
      <Deal>
        <Node1>A1</Node1>
        <Node2>A2</Node2>
        <Node3>A3</Node3>
        <Node4>A4</Node4>
      </Deal>
      <Deal>
        <Node1>B1</Node1>
        <Node2>B2</Node2>
        <Node3>B3</Node3>
        <Node4>B4</Node4>
      </Deal>
      <Deal>
        <Node1>C1</Node1>
        <Node2>C2</Node2>
        <Node3>C3</Node3>
        <Node4>C4</Node4>
      </Deal>
      <Deal>
        <Node1>D1</Node1>
        <Node2>D2</Node2>
        <Node3>D3</Node3>
        <Node4>D4</Node4>
      </Deal>
      <Deal>
        <Node1>E1</Node1>
        <Node2>E2</Node2>
        <Node3>E3</Node3>
        <Node4>E4</Node4>
      </Deal>
    </Deals>
  </NEW>
  <NEW>
    <Year>2013</Year>
    <Name><![CDATA[Bob]]></Name>
    <Tier>1</Tier>
    <StartDate>20140201</StartDate>
    <Deals>
      <Deal>
        <Node1>F1</Node1>
        <Node2>F2</Node2>
        <Node3>F3</Node3>
        <Node4>F4</Node4>
      </Deal>
      <Deal>
        <Node1>G1</Node1>
        <Node2>G2</Node2>
        <Node3>G3</Node3>
        <Node4>G4</Node4>
      </Deal>
      <Deal>
        <Node1>H1</Node1>
        <Node2>H2</Node2>
        <Node3>H3</Node3>
        <Node4>H4</Node4>
      </Deal>
      <Deal>
        <Node1>I1</Node1>
        <Node2>I2</Node2>
        <Node3>I3</Node3>
        <Node4>I4</Node4>
      </Deal>
      <Deal>
        <Node1>J1</Node1>
        <Node2>J2</Node2>
        <Node3>J3</Node3>
        <Node4>J4</Node4>
      </Deal>
    </Deals>
  </NEW>
  <NEW>
    <Year>2013</Year>
    <Name><![CDATA[Jill]]></Name>
    <Tier>0</Tier>
    <StartDate>20140201</StartDate>
    <Deals>
      <Deal>
        <Node1>K1</Node1>
        <Node2>K2</Node2>
        <Node3>K3</Node3>
        <Node4>K4</Node4>
      </Deal>
      <Deal>
        <Node1>L1</Node1>
        <Node2>L2</Node2>
        <Node3>L3</Node3>
        <Node4>L4</Node4>
      </Deal>
      <Deal>
        <Node1>M1</Node1>
        <Node2>M2</Node2>
        <Node3>M3</Node3>
        <Node4>M4</Node4>
      </Deal>
      <Deal>
        <Node1>N1</Node1>
        <Node2>N2</Node2>
        <Node3>N3</Node3>
        <Node4>N4</Node4>
      </Deal>
      <Deal>
        <Node1>P1</Node1>
        <Node2>P2</Node2>
        <Node3>P3</Node3>
        <Node4>P4</Node4>
      </Deal>
    </Deals>
  </NEW>
</Root>

Open in new window

0
Comment
Question by:rcavins
  • 2
  • 2
4 Comments
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 250 total points
ID: 39908417
Hi rcavins;

The following code snippet should transform the original XML document to the new document layout as you indicated.

// Load the XML document from the file system to memory. 
// Change the file path to the location of your XML file.
XDocument xdoc = XDocument.Load("C:/Working Directory/TestData.xml");

// In the Select clause Level1 are the are the first four nodes to be wraped.
// In Level2 are all the Nodex nodes.
var xmlParsed = (from n in xdoc.Root.Descendants("NEW")
                 select new
                 {
                     Level1 = (from l1 in n.Descendants()
                               where !l1.Name.ToString().Contains("Node")
                               select l1),
                     Level2 = (from l1 in n.Descendants()
                               where l1.Name.ToString().Contains("Node")
                               select l1)
                 }).ToList();
 
// Creates a new XML document with the basic format and all the Level1 nodes
var newDoc = new XElement("Root",
                 from n in xmlParsed
                 select new XElement("New",
                            n.Level1,
                            new XElement("Deals" 
                            ))
                 );

// Get a reference to all the Deals node in the new document
var addDeals = (from nd in newDoc.Descendants("Deals")
                select nd).ToList();

// Adds the Nodes to the Deal parent and then adds it to the Deals node
for( int idx = 0; idx < xmlParsed.Count(); idx++)
{
    var nodes = xmlParsed[idx].Level2.ToList();
    for( int pos = 0; pos < nodes.Count(); pos += 4)
    {
        var newDeal = new XElement("Deal");
        newDeal.Add(nodes[pos]);
        newDeal.Add(nodes[pos + 1]);
        newDeal.Add(nodes[pos + 2]);
        newDeal.Add(nodes[pos + 3]);
        addDeals[idx].Add(newDeal);
    }
}

// At this point the newDoc document contains all the nodes in the correct order.

Open in new window

0
 

Author Comment

by:rcavins
ID: 39909232
Thank you!  I'm testing it now.
0
 

Author Closing Comment

by:rcavins
ID: 39909582
Works perfectly! Thank you very much
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39909703
Not a problem, glad I was able to help.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now