nested linq queries

Question

I'm working with an xml file like this.

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Body>
    <Element1>
      <Element2 day="2009-10-18">
        <Element3 name="Joe">
          <Element4 time="1">
            <Element5 amount="0" price="16.58"/>
            <Element5 amount="1" price="18.58"/>
            <Element5 amount="2" price="20.58"/>
          </Element4>
        </Element3>
        <Element3 name="Fred">
          <Element4 time="5">
            <Element5 amount="1" price="15.41"/>
            <Element5 amount="2" price="16.41"/>
            <Element5 amount="3" price="17.41"/>
            <Element5 amount="4" price="18.41"/>
          </Element4>
        </Element3>
      </Element2>
    </Element1>
  </Body>
</Envelope>

I need to loop through all the Element3 nodes and then loop through all the Element5 nodes to get an output similar to this.

day, name, time, amount, price 
2009-10-18, Joe, 1, 0, 16.58 
2009-10-18, Joe, 1, 1, 18.58
2009-10-18, Joe, 1, 2, 20.58
2009-10-18, Joe, 1, 1, 16.58
2009-10-18, Fred, 5, 0, 15.41 
etc

My linq query to do this looks something like the below which I thought was great until I realized this was only grabbing the first Element5 node it came to.

XDocument doc = XDocument.Load(@"myfile.xml");
        DataContext st = new DataContext();

        var docxml = from c in doc.Elements("Envelope").Elements("Body").Elements("Element1").Elements("Element2").Elements("Element3")
                     select new mytable()
                     {
                         MyKey = Guid.NewGuid(),
                         day = Convert.ToDateTime(c.Parent.Attribute("day").Value)
                         name = c.FirstAttribute.Value,
                         hour = Convert.ToInt32(c.Element("Element4").FirstAttribute.Value),
                         price = Convert.ToDecimal(c.Element("Element4").Element("Element5").Attribute("price").Value),
                         amount = Convert.ToDecimal(c.Element("Element4").Element("Element5").Attribute("amount").Value)

                     };
        st.mytable.InsertAllOnSubmit(docxml);
        st.SubmitChanges();

How can I loop this to include ALL The Element5 nodes?

Yvo · Accepted Answer · 2010-02-01 22:35:43Z

Every from-statement is basically a foreach-loop (easy way to think of it).
Note: I had to change some of the parent relations

XDocument doc = XDocument.Load(@"myfile.xml");
DataContext st = new DataContext();

var docxml = from c in doc.Elements("Envelope").Elements("Body").Elements("Element1").Elements("Element2").Elements("Element3").Elements("Element4")
             from e in c.Elements("Element5")
             select new mytable()
             {
                 MyKey = Guid.NewGuid(),
                 day = Convert.ToDateTime(c.Parent.Parent.Attribute("day").Value)
                 name = c.Parent.FirstAttribute.Value,
                 hour = Convert.ToInt32(c.FirstAttribute.Value),
                 price = Convert.ToDecimal(e.Attribute("price").Value),
                         amount = Convert.ToDecimal(e.Attribute("amount").Value)

             };

st.mytable.InsertAllOnSubmit(docxml);
st.SubmitChanges();

You could make the code a bit more readable by using the let-statement and better names:

var docxml = from element4 in doc.Elements("Envelope").Elements("Body").Elements("Element1").Elements("Element2").Elements("Element3").Elements("Element4")
             let element3 = element4.Parent
             let element2 = element3.Parent
             from element5 in c.Elements("Element5")
  ...

I can't test the code, because I'm on my MacBook. Hope this helps.

Collectives™ on Stack Overflow

nested linq queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related