Nsryjdtyk

Parsing XML file using SSIS / C#

operations like fetching record count from trailer , TIN from body and store into a variable or somewhere temporarily(your suggestions please) for further processing. I don't want to store it in a table.

Please find the sample xml mentioned below

<ACOParticipantData xmlns:xsi="">

  <Header>

    <HeaderCode>HDR_PFPRVDR</HeaderCode>

    <FileCreationDate>20160101</FileCreationDate>

    <ACOProgCode>21</ACOProgCode>

  </Header>

  <Participants>

    <Participant>

      <ACO_ID>V199</ACO_ID>

      <TIN>123456789</TIN>

      <Old_TIN>987654321</Old_TIN>

      <Org_NPI>1234567890</Org_NPI>

      <Ind_NPI>1234567890</Ind_NPI>

      <CCN>123456</CCN>

      <PRG_Eff_Dt>20160101</PRG_Eff_Dt>

      <PRG_Term_Dt>20161231</PRG_Term_Dt>

    </Participant>

  </Participants>

  <Trailer>

    <TrailerCode>TRL_PFPRVDR</TrailerCode>

    <FileCreationDate>20160101</FileCreationDate>

    <RecordCount>1</RecordCount>

  </Trailer>

</ACOParticipantData>

You need to get fist get list of Participants then fetch all Participants tin number into list like

Here i created console app for your demonstration purpose.

class Program

{

    static void Main(string args)

    {

        XDocument doc = XDocument.Load(@"Path to your xml file");



        List<long> tinList = new List<long>();



        tinList = doc.Descendants("Participants").Elements().Elements("TIN").Select(x => (long)x).ToList();



        foreach (long tin in tinList)

        {

            Console.WriteLine(tin);

        }



        Console.ReadLine();

    }

}

Output: (For 2 Participants)

You need to create a class for each node and use XML deserialisation to create the object.

I had to remove the empty namespace as the deserialisation process requires a valid namespace.

Also you can change the type of the properties according to your needs.

using System;

using System.IO;

using System.Xml.Serialization;

using System.Linq;



public class Program

{

    public class ACOParticipantData 

    {

        public Header Header { get; set; }

        public Participant Participants { get; set; }

    }



    public class Header 

    {

        public string HeaderCode { get; set; }

        public string FileCreationDate { get; set; }

        public string ACOProgCode { get; set; }

    }



    public class Participant 

    {

        public string ACO_ID { get; set; }

        public string TIN { get; set; }

        public string Old_TIN { get; set; }

        public string Org_NPI { get; set; }

        public string Ind_NPI { get; set; }

        public string CCN { get; set; }

        public string PRG_Eff_Dt { get; set; }

        public string PRG_Term_Dt { get; set; }

    }



    public class Trailer 

    {

        public string TrailerCode { get; set; }

        public string FileCreationDate { get; set; }

        public string RecordCount { get; set; }

    }



    public static void Main()

    {

        var xmlString = @"<ACOParticipantData>

          <Header>

            <HeaderCode>HDR_PFPRVDR</HeaderCode>

            <FileCreationDate>20160101</FileCreationDate>

            <ACOProgCode>21</ACOProgCode>

          </Header>

          <Participants>

            <Participant>

              <ACO_ID>V199</ACO_ID>

              <TIN>123456789</TIN>

              <Old_TIN>987654321</Old_TIN>

              <Org_NPI>1234567890</Org_NPI>

              <Ind_NPI>1234567890</Ind_NPI>

              <CCN>123456</CCN>

              <PRG_Eff_Dt>20160101</PRG_Eff_Dt>

              <PRG_Term_Dt>20161231</PRG_Term_Dt>

            </Participant>

            <Participant>

              <ACO_ID>V199</ACO_ID>

              <TIN>123456780</TIN>

              <Old_TIN>987654321</Old_TIN>

              <Org_NPI>1234567890</Org_NPI>

              <Ind_NPI>1234567890</Ind_NPI>

              <CCN>123456</CCN>

              <PRG_Eff_Dt>20160101</PRG_Eff_Dt>

              <PRG_Term_Dt>20161231</PRG_Term_Dt>

            </Participant>

          </Participants>

          <Trailer>

            <TrailerCode>TRL_PFPRVDR</TrailerCode>

            <FileCreationDate>20160101</FileCreationDate>

            <RecordCount>1</RecordCount>

          </Trailer>

        </ACOParticipantData>";



        var serializer = new XmlSerializer(typeof(ACOParticipantData));



        ACOParticipantData obj = null;

        using (var reader = new StringReader(xmlString))

        {

            obj = (ACOParticipantData)serializer.Deserialize(reader);

        }



        if (obj == null) 

        {

            return;

        }



        foreach (var tin in obj.Participants.Select(x => x.TIN)) 

        {

            Console.WriteLine(tin);

        }

    }

}

Output:

123456789

123456780