CODEWITHSUNDEEP
c#xmlexcelextract
ctee
ctee

Reputation: 23

Reading XML and Save into Excel C#

I having XML as follow and I need to read this XML and get specific element in the XML and save into excel. Below is my XML File :

<?xml version="1.0" encoding="UTF-8"?>
<ns1:BookTestXMLExport StartTime="2016-09-20T12:58:15.000+07:00" scanTime="2016-09-20T12:58:15.000+07:00" scanStatus="Pass">
    <ns1:MachineXML barCode="ISN-1000500213" Revision="2016A" bookType="Novel"/>
    <ns1:StationXML scannerName="HP4512745" stage="v810"/>
    <ns1:ScanXML name="32:2:165:1">
        <ns1:IndictmentXML algorithm="NIL" subType="17X8X15MM" imageFileName="175228000_9_0.jpg">
            <ns1:BorrowXML packageId="NIL" userId="NIL" name="NIL"/>
            <ns1:BookXML name="GrayDay" desc="Love Story"/>
        </ns1:IndictmentXML>
    </ns1:ScanXML>
    <ns1:ScanXML name="35:23:165:1">
        <ns1:IndictmentXML algorithm="NIL" subType="17X8X15MM" imageFileName="175228001_9_0.jpg">
            <ns1:BorrowXML packageId="NIL" userId="8799" name="Sharon"/>
            <ns1:BookXML name="Harry Potter" desc="Magic"/>
        </ns1:IndictmentXML>
    </ns1:ScanXML>
</ns1:BookTestXMLExport>

Below is the expected result: Expected Result

Can anyone guide me on this.

Upvotes: 0

Views: 539

Answers (1)

jdweng
jdweng

Reputation: 34421

Try this to parse file. You still need to save as excel.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApplication14
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            XDocument doc = XDocument.Load(FILENAME);

            XElement bookTestXMLExport = doc.Descendants().Where(x => x.Name.LocalName == "BookTestXMLExport").FirstOrDefault();
            XNamespace ns = bookTestXMLExport.GetNamespaceOfPrefix("ns1");

            var results = doc.Descendants(ns + "BookTestXMLExport").Select(x => new
            {
                scanStatus = (string)x.Attribute("scanStatus"),
                barCode = (string)x.Element(ns + "MachineXML").Attribute("barCode"),
                ScanXML = x.Elements(ns + "ScanXML").Select( y => new {
                    name = (string)y.Descendants(ns + "BookXML").FirstOrDefault().Attribute("name"),
                    desc = (string)y.Descendants(ns + "BookXML").FirstOrDefault().Attribute("desc")
                }).ToList()
            }).ToList();

        }

    }

}

Upvotes: 1

Related Questions