to generate a xml file from csv ussing c# in ssis script component

[Urvi1]

I have a csv file data of automobile insurance records which i need to convert into a xml file using ssis.
Now , when I have unique insurance numbers in csv then i can generate the xml file in the required format but there are multiple occurrences of a insurance number.
**For ex. A family have got their automobiles insured having 4 drivers and 4 vehicles so there are 4 entries of that insurance number in csv file which should be 1 block in xml having insurance number occurring 1 time and all driver and vehicle entries 4 time under 1 insurance number tag.**


I have generated a xml file when there is no repetition of driver or vehicle or insurance number. I am very new to SSIS and never done coding in c# so if someone can help me with the code. How to loop over columns if they are repeating and make child nodes entry for it.


This is the code i have used in ssis script component to generate the structure of xml i require but it works only in case when there is no repitition.

string[] lines = File.ReadAllLines(@"H:\SSIS\Source\Intermediate.csv");




        XElement xml = new XElement("Submissions",
        from str in lines
        let columns = str.Split(',')


        select new XElement("SubmissionEntry",
         new XElement("SubmissionID", columns[0]),
         new XElement("PolicyNumber", columns[1]),
         new XElement("OfferingCodeIdentifier", columns[2]),
         new XElement("BaseState", columns[3]),
         new XElement("EffectiveDate", columns[4]),
         new XElement("PeriodStart", columns[5]),
         new XElement("RateASOfDate", columns[6]),
         new XElement("RenewalNumber", columns[7]),
         new XElement("RatingCapFactor", columns[8]),
         new XElement("ConversionFactor", columns[9]),
         new XElement("ClaimsFreeCount", columns[10]),
         new XElement("PaidInFull", columns[11]),
         new XElement("IsHomeOwner", columns[12]),
         new XElement("IsNewBusinessTransfer", columns[13]),
         new XElement("IsNamedNonOwnerPolicy ", columns[14]),
         new XElement("LVTTier", columns[15]),
         new XElement("PNIBirthDate", columns[16]),
         new XElement("PNIPostalCode", columns[17]),
         new XElement("CreditStatus", columns[18]),
         new XElement("EquivalentCreditScore ", columns[19]),
         new XElement("CreditScore", columns[20]),
         new XElement("DeliverySource", columns[21]),
         new XElement("ChannelGroup", columns[22]),
         new XElement("LineCoverages",
             new XElement("LineCovEntry",
                 new XElement("PatternCode", columns[23]),
                 new XElement("CoverageTerms",
                     new XElement("CovTermCodeIdentifier", columns[24]),
                     new XElement("CovTermValue", columns[25])))),
         new XElement("PolicyDrivers",
             new XElement("DriverEntry",
                 new XElement("DriverID", columns[26]),
                 new XElement("DriverType", columns[27]),
                 new XElement("Excluded", columns[28]),
                 new XElement("RelationToApplicant", columns[29]),
                 new XElement("DateOfBirth", columns[30]),
                 new XElement("Gender", columns[31]),
                 new XElement("MaritalStatus", columns[32]),
                 new XElement("AgeLicensed", columns[33]),
                 new XElement("LicenseStatus", columns[34]),
                 new XElement("LicenseCountry", columns[35]),
                 new XElement("UnverifiedDriver", columns[36]),
                 new XElement("EmploymentStatus", columns[37]),
                 new XElement("DriverImprovementCourse", columns[38]),
                 new XElement("DriverImprovementCourse", columns[39]),
                     new XElement("IncidentEntry",
                         new XElement("IncidentID", columns[40]),
                         new XElement("IncidentDate", columns[41]),
                         new XElement("ViolationCode", columns[42]),
                         new XElement("OverrideCategory", columns[43]),
                         new XElement("LossAmount", columns[44])))),
         new XElement("PersonalVehicles",
             new XElement("VehicleEntry",
                 new XElement("VehicleID", columns[45]),
                 new XElement("VehicleYear", columns[46]),
                 new XElement("GaragePostalCode", columns[47]),
                 new XElement("PrimaryUse", columns[48]),
                 new XElement("GaragedOutOfState3MonthsPerYear", columns[49]),
                 new XElement("SecurityTypeCode", columns[50])),
                 new XElement("RAPA",
                     new XElement("Rapa_Bi", columns[51]),
                     new XElement("Rapa_Coll", columns[52]),
                     new XElement("Rapa_Comp", columns[53]),
                     new XElement("Rapa_Med", columns[54]),
                     new XElement("Rapa_Pd", columns[55]),
                     new XElement("Rapa_Pip", columns[56])),
                     new XElement("VehicleCovEntry",
                         new XElement("PatternCode", columns[57]),
                         new XElement("CoverageTerm",
                             new XElement("CovTermCodeIdentifier", columns[58]),
                             new XElement("CovTermValue", columns[59]))))));








        xml.Save(@"H:\SSIS\Destination\demo xml.xml");