XML with sub nodes to csv format using linq

Question

I need some help with converting XML with child nodes to csv file.

Here's my XML:

<Main>
  <root>
    <Name>Sample name1</Name>
    <StreetAddress>Sample Address1</StreetAddress>
    <Service>
      <Type>Outlet</Type>
      <PhoneNumber></PhoneNumber>
      <Openweekday>Closed</Openweekday>
    </Service>
    <Service>
      <Type>Mall</Type>
      <PhoneNumber></PhoneNumber>
      <Openweekday>Closed</Openweekday>
      </Service>
  </root>
  <root>
    <Name>Sample name2</Name>
    <StreetAddress>Sample Address2</StreetAddress>
    <Service>
      <Type>Shop</Type>
      <PhoneNumber></PhoneNumber>
      <Openweekday>Closed</Openweekday>
    </Service>
</Main>

Expected CSV Result:

Name,StreetAddress,Type,PhoneNumber,OpenweekDay,Type,PhoneNumber,OpenweekDay
Sample name1,Sample Address1,Outlet,,Closed,Mall,,Closed
Sample name2,Sample Address2,Shop,,Closed

Tried this code I got from MSDN. But it throws a null exception -

XElement custOrd = XElement.Load("xxx.xml");
    string csv =
        (from el in custOrd.Element("Main").Elements("root")
         select
             String.Format("{0},{1},{2},{3},{4},{5},{6},{7}",
                 (string)el.Attribute("Name"),
                 (string)el.Element("StreetAddress"),
                 (string)el.Element("Service").Element("Type"),
                 (string)el.Element("Service").Element("PhoneNumber"),
                 (string)el.Element("Service").Element("Openweekday"),
                 (string)el.Element("Service").Element("Type"),
                 (string)el.Element("Service").Element("PhoneNumber"),
                 (string)el.Element("Service").Element("Openweekday"),
                 Environment.NewLine
             )
        )
        .Aggregate(
            new StringBuilder(),
            (sb, s) => sb.Append(s),
            sb => sb.ToString()
        );
    Console.WriteLine(csv);

let me know how to manage nulls within linq.

possible duplicate of [What is a NullReferenceException and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — crthompson, Mar 26 '15 at 17:20
@paqogomez - How do I manage null inside the linq for each element? How do I handle when the service node is missing? I am new to linq. — coolio schwab, Mar 26 '15 at 17:24
@paqogomez - The duplicate reference post you added does not answer my question? Can you remove it so others can help me out? — coolio schwab, Mar 26 '15 at 18:10
No one sees that except you, though others get to vote on if its truly a duplicate or not. So far, only one other agrees with me. As for your question, it seems to me that inline if statements would fix your problem. Something like `el.Element("Service").Element("PhoneNumber") == null ? "" : el.Element("Service").Element("PhoneNumber");` — crthompson, Mar 26 '15 at 18:52
And that inline statement is covered very well in the duplicate link that I provided. — crthompson, Mar 26 '15 at 21:32
@CoderofCode casting null to string is fine : `var test = (string)null;` — har07, Mar 27 '15 at 03:38

score 0 · Answer 1 · answered Mar 27 '15 at 03:37

The actual problem is that custOrd it self already references <Main> so calling Element("Main") on custOrd will return null, and calling Elements("root") on null will definitely trigger null reference exception.

To fix that problem, you can either change custOrd to an XDocument type :

XDocument custOrd = XDocument.Load("xxx.xml");

...or simply remove .Element("Main") call :

XElement custOrd = XElement.Load("xxx.xml");
string csv =
        (from el in custOrd.Elements("root")
        ......
        ......

Casting null to string is safe, but in case there any <root> without child element <service> your code will throw null reference exception again.

score 0 · Accepted Answer · answered Mar 27 '15 at 17:59

As others mention on loading or parsing the xml, main is already referenced, so your starting point should be root. This will give you the structure you are looking for.

        XElement dataSet1Tree = XElement.Parse(xml);
        var dataSet1List = dataSet1Tree.Elements().Select(
        root => new
        {
            Name = (string)root.Element("Name"),
            StreetAddress = (string)root.Element("StreetAddress"),
            Service = root.Elements("Service")
                .Select(service => new
                {
                    Type = (string)service.Element("Type"),
                    PhoneNumber = (string)service.Element("PhoneNumber"),
                    OpenWeekDay = (string)service.Element("Openweekday")
                })

        }).SelectMany(root => root.Service, (root, service) => new
        {
            Name = root.Name, 
            StreetAddress = root.StreetAddress , 
            Type = service.Type, 
            PhoneNumber = service.PhoneNumber,
            OpenWeekDay = service.OpenWeekDay
        }).Aggregate("",(sb,s)=> sb +=string.Format("{0},{1},{2},{3},{4}",
                                                    s.Name,
                                                    s.StreetAddress,
                                                    s.Type,
                                                    s.PhoneNumber, 
                                                    s.OpenWeekDay)+"\r\n");

Console.WriteLine(dataSet1List);

XML with sub nodes to csv format using linq

2 Answers2