Convert a XML to CSV using C#

Question

How to convert a XML file to a CSV file in C#, showing only these Tags: <original-impot-no>, <price>, <Small-price>, <Big-price>?

sample XML code: it represents one line among several lines, and every line may contains several <product-lineitem>Tags

<?xml version="1.0" encoding="UTF-8"?>
<impots xmlns="http://www.google.com/xml/impot//20016-02-31">
  <impot impot-no="W0110891258">
    <impot-date>2017-12-10T22:33:35.000Z</impot-date>
    <prop-by>Yallo</prop-by>
    <original-impot-no>891258</original-impot-no>
    <currency>EUR</currency>
    <server-locale>Esp</server-locale>
    <lax>gross</lax>
    <current-impot-no>123358</current-impot-no>
    <product-lineitems>
       <product-lineitem>
            <price>450</price>
            <red>6.50</red>
            <Small-price>39</Small-price>
            <Big-price>3229</Big-price>
            <lineitem-text>Grand create</lineitem-text>
            <basis>234.00</basis>
        </product-lineitem>
    </product-lineitems>
       <product-lineitem>
            <price>432</price>
            <red>12</red>
            <Small-price>44</Small-price>
            <Big-price>34</Big-price>
            <lineitem-text>Small create</lineitem-text>
            <basis>44.00</basis>
       </product-lineitem>
    </product-lineitems>
  </impot>
</impots>

I should get someting like this in y CSV file:

891258;450;39;229

891258;432;44;34

the C# code:

the problem that Im facing with this code is that I can not retrieve the descandent of the TAG <impot>

   XmlTextReader xtr = new XmlTextReader(@"C:\Temp_Convert\Impot.xml");
            StringBuilder dataToBeWritten = new StringBuilder();
            while (xtr.Read())
            {

                    if (xtr.NodeType == XmlNodeType.Element && xtr.Name == "original-impot-no")
                    {
                        string s1 = xtr.ReadElementString();
                        dataToBeWritten.Append(s1);
                        dataToBeWritten.Append(";");
                    }
                    else if (xtr.NodeType == XmlNodeType.Element && xtr.Name == "price")
                    {
                        string s2 = xtr.ReadElementString();
                        dataToBeWritten.Append(s2);
                        dataToBeWritten.Append(";");

                    }
                    else if (xtr.NodeType == XmlNodeType.Element && xtr.Name == "Small-price")
                    {
                        string s2 = xtr.ReadElementString();
                        dataToBeWritten.Append(s2);
                        dataToBeWritten.Append(";");
                        dataToBeWritten.Append(0);
                        dataToBeWritten.Append(Environment.NewLine);
                    }
                        else if (xtr.NodeType == XmlNodeType.Element && xtr.Name == "Big-price")
                    {
                        string s2 = xtr.ReadElementString();
                        dataToBeWritten.Append(s2);
                        dataToBeWritten.Append(";");
                        dataToBeWritten.Append(0);
                        dataToBeWritten.Append(Environment.NewLine);
                    }
                }

                File.WriteAllText(@"C:\Temp_Convert\Impot.csv", dataToBeWritten.ToString());

            }

Can somebody please propose a solution, thank you so much in advance.

Answer 1

You have an invalid XML. I guess here is the correct format.

<?xml version="1.0" encoding="UTF-8"?>
<impots xmlns="http://www.google.com/xml/impot//20016-02-31">
   <impot impot-no="W0110891258">
      <impot-date>2017-12-10T22:33:35.000Z</impot-date>
      <prop-by>Yallo</prop-by>
      <original-impot-no>891258</original-impot-no>
      <currency>EUR</currency>
      <server-locale>Esp</server-locale>
      <lax>gross</lax>
      <current-impot-no>123358</current-impot-no>
      <product-lineitems>
         <product-lineitem>
            <price>450</price>
            <red>6.50</red>
            <Small-price>39.00</Small-price>
            <Big-price>3229.00</Big-price>
            <lineitem-text>Grand create</lineitem-text>
            <basis>234.00</basis>
         </product-lineitem>
      </product-lineitems>
      <product-lineitems>
         <product-lineitem>
            <price>432</price>
            <red>12</red>
            <Small-price>44.00</Small-price>
            <Big-price>34.00</Big-price>
            <lineitem-text>Small create</lineitem-text>
            <basis>44.00</basis>
         </product-lineitem>
      </product-lineitems>
   </impot>
</impots>

You couldn't retrieve the descendant because you are not including the namespace http://www.google.com/xml/impot//20016-02-31.

Here is how the code should be.

XNamespace ns = "http://www.google.com/xml/impot//20016-02-31";
var results = xDocument.Descendants(ns + "impot");

Then, you need to modify your query to retrieve elements that you need.

Here is the sample. I assume that product-lineitems only has one child product-lineitem.

var results = xDocument.Descendants(ns + "impot").Select(x => new {
    ImpotNo = x.Attribute("impot-no")?.Value,
    ProductLineItems = x.Descendants(ns + "product-lineitems").Select(y => new
    {
        Item = y.Descendants(ns + "product-lineitem").Select(z => new
        {
            Price = z.Element(ns + "price")?.Value,
            SmallPrice = z.Element(ns + "Small-price")?.Value,
            BigPrice = z.Element(ns + "Big-price")?.Value,
        }).FirstOrDefault()
    })
});

foreach (var result in results)
{
    foreach (var productLine in result.ProductLineItems)
    {
        dataToBeWritten.Append(result.ImpotNo);
        dataToBeWritten.Append(";");
        dataToBeWritten.Append(productLine.Item.Price);
        dataToBeWritten.Append(";");
        dataToBeWritten.Append(productLine.Item.SmallPrice);
        dataToBeWritten.Append(";");
        dataToBeWritten.Append(productLine.Item.BigPrice);
        dataToBeWritten.Append(";");
        dataToBeWritten.Append(0);
        dataToBeWritten.Append(Environment.NewLine);
    }
}