Linq to XML Flatten

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)

Select allOpen 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)

Select allOpen 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

Select allOpen 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
};

Select allOpen 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")
		};

Select allOpen 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>

Select allOpen 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>

Select allOpen in new window