Best approach for selecting records from XML

Question

I have data that is sent to me as an XML file. This data is to go into two accounts systems which exist on a single server. There could potentially be more but at the moment there are 2. One of the accounts systems is a UK company, and the other is a Swedish company. This doesn't matter really. The important thing is that data from the XML will go into either one of these systems.

I have a Windows services that reads the data in from the XML and using an XML serialiser I get the data into an object containing all the records and puts it into the relevant company, UK or Sweden. At this point, I do not know which of the two companies, the particular record is destined for. To work that out, as I read the record, one of the XML fields contains some initials. I use those initials to work out which company the data is to go into. The companies that the system can connect to is configured in a JSON file which is read at runtime. The JSON file that configures the companies is:

    {
      "Company": [
        {
          "Name": "TESTUK"
        },
        {
          "Name": "TESTSWED",
          "Users": "ABC,DEF,XYZ"
        }
      ]
    }


 

An example of the XML records is:

    <RECORD section="Detail" rec="1">
    <V datatype="String">UK001</V>
    <V datatype="String">Test Company</V>
    <V datatype="String"></V>
    <V datatype="Memo">Address</V>
    <V datatype="String">Your ref</V>
    <V datatype="String">Our ref</V>
    <V datatype="String">Case ref FMT</V>
    <V datatype="String">Case ref</V>
    <V datatype="String">Case type</V>
    <V datatype="String">Memo line 1</V>
    <V datatype="String">line 2</V>
    <V datatype="String">line 3</V>
    <V datatype="String">line 4</V>
    <V datatype="String">line 5</V>
    <V datatype="String">line 6</V>
    <V datatype="String">line 7</V>
    <V datatype="String">line 8</V>
    <V datatype="String">ABC</V>
    <V datatype="String">GBP</V>
    <V datatype="String">PRODID</V>
    <V datatype="Currency" dataformat="7.2">100.00</V>
    <V datatype="String">PRODCODE</V>
    <V datatype="Currency" dataformat="-16.2">200.00</V>
    <V datatype="String"></V>
    <V datatype="Currency" dataformat="-16.2"></V>
    <V datatype="Currency" dataformat="-16.2">1701.79</V>
    <V datatype="Currency" dataformat="7.2">0.00</V>
    <V datatype="Currency" dataformat="7.2">1701.79</V>
    <V datatype="String">VENDORID</V>
    </RECORD>

The key line there is the one with the initials ABC (line 19). I read this value and then find the company name where this user is linked to. A user will only be linked to a single company. If no user is found it defaults to the company with no users listed in the JSON. The problem I have is that I am reading the records one by one and on each read, I have to open and close the connection to the target company which is very slow when you're dealing with say 300 or 400 records. What I'd like to do is akin to selecting the records from the XML where any record where a set of initials for Sweden is found, open the Swedish company and pile them all in, in one go. Then go through any potential other company that might exist with user initials defined (there are none at the moment) and do them, then anything that doesn't match, put them all in the UK.

I don't know how to do the selecting of the XML records. It feels like I could do a LINQ query on them where I do something like select all records where the initials in the XML are contained in the users list for Sweden. As I'm reading the records, I can remove them from the data set easily enough. Then, in my example, what was left I would just put in the UK company. How can I do that record selection from the XML based on a "where clause" from data in the JSON file.

The JSON file is read into an object list which contains properties for the name and the users as a string. I have a property which returns true if there are no users listed against the company which lets the system know this is the default company. So, I was thinking of selecting the users from the first company that has users (Sweden) and then selecting records from the XML where the value in the initials field is contained withing the users string. In SQL it would be:

select * from XMLRecords where UserInitials in ('ABC','DEF','XYZ')

How can I do this in C#?

Hopefully, I've explained my query well enough!