LINQ to XML Challenge 1

Recently I came across with the unique requirement to do the extravagant changes on the incoming Hl7 data in XML document. At first, I handed it over to my offshore subordinate and explained the logic. After couple of days, when I started reviewing the code. I could clearly realize, it was not meant to be released to production. For that matter, not even to test. Lots of XML document, XMLNodes..etc. Overall the code looked old school lacking performance and scalability.

So I decided to write it myself.

Requirement : We get the ORU HL7 message in XML format with only one ORC and OBR and had to split it into multiple ORC and OBR. Also associate the corresponding OBX segments with the correct ORC and OBR, These associations would be taken form the SQL table.

public static void FormatSeperateORCOBR(ref XmlDocument doc, string trackingNo, string hl7Id)
                XElement xe;
                xe = XElement.Parse(doc.OuterXml);

                //Get the data from table
                DataSet dsResultSet = new DataSet();
                dsResultSet = DAL.DataAccess.GetTestResultAssociation();
                DataTable dtTestResults = dsResultSet.Tables[0];

                Dictionary<string, List<int>> testResultDictionary = new Dictionary<string, List<int>>();

                //Get the unique test codes from the datatable
                var testcodes = from t in dtTestResults.AsEnumerable()
                                group t by t.Field<int>("iTestCode") into testcode
                                select testcode;

                //populate dictionary with Test and results association
                foreach (var testcode in testcodes)
                    string testId = testcode.Key.ToString();

                    var results = from r in dtTestResults.AsEnumerable()
                                  where r.Field<int>("iTestCode") == testcode.Key
                                  select r.Field<int>("iResultCode");
                    testResultDictionary.Add(testId, results.ToList());         //List of result codes in the dictionary.

                StringBuilder strObservation = new System.Text.StringBuilder();

                // Create ORC and OBR Templates
                string strORCTemplate = xe.XPathSelectElement("//Observation/ORC_CommonOrder").ToString();
                string strOBRTemplate = xe.XPathSelectElement("//Observation/OBR_ObservationRequest").ToString();

                //create OBX with PDF and OBX without PDF.
                string strOBXwithPDF = xe.XPathSelectElements("//ObservationResults").
                                        Where(item => item.XPathSelectElement("OBX_ObservationResult/OBX_2_ValueType").Value == "ED")

                //Take the OBX with PDF and replace the elements using below statment to get OBXwithoutPDF
                XElement xeOBXwithPDF = XElement.Parse(strOBXwithPDF);
                                    ForEach(item =>
                                        item.XPathSelectElement("OBX_5_ObservationValue").Value = "$FileName$";
                                        item.XPathSelectElement("OBX_2_ValueType").Value = "ST";
                                        item.XPathSelectElement("OBX_3_ObservationIdentifier/CE_0_Identifier").Value = "IR";
                                        item.XPathSelectElement("OBX_3_ObservationIdentifier/CE_1_Text").Value = "IMAGE";
                string strOBXwithoutPDF = xeOBXwithPDF.ToString();

                XElement xeOBR;
                string strtestName;

                var last = testResultDictionary.Last();
                foreach (var item in testResultDictionary)

                    //1. Get the Templates and modify them accordingly
                    xeOBR = XElement.Parse(strOBRTemplate);
                    xeOBR.XPathSelectElement("OBR_4_UniversalServiceIdentifier/CE_0_Identifier").SetValue(item.Key);  //item.Key holds the test code.

                    strtestName = dtTestResults.Select("iTestCode=" + item.Key).FirstOrDefault().Field<string>("vchTestName");

                    //2. Append the template to sb.
                    strObservation.Append("<Observation xmlns=\"\">" + strORCTemplate);

                    //3. Get the Results from the XML belonging to Test code into the List.
                    string testcode = item.Key;
                    var query = from o in xe.XPathSelectElements("//ObservationResults")
                                from resultcode in item.Value  //item.Value is List of ResultCodes
                                where o.XPathSelectElement("OBX_ObservationResult/OBX_3_ObservationIdentifier/CE_0_Identifier").Value == resultcode.ToString()
                                select o;
                    //4. Join the list to get the bunch of ObservationResults beloging to Test code in context
                    strObservation.Append(String.Join("", query));

                    //5. Append the OBX without out PDF to all of them expect the last one.
                    if (item.Equals(last))


                //6. Replace the old Observation with new Observations.
                XElement xeObservations = XElement.Parse("<Root>" + strObservation.ToString() + "</Root>");     //Adding root to make the XElement parse the string.

                //7. Update OBR Index Number
                int iOBRCounter = 1;
                foreach (var observation in xeObservations.XPathSelectElements("//Observation"))


                //8. Replace the Observations

                // 9. Remove the dummy root element
                string strFinalXml = xe.ToString().Replace(@"<Root xmlns="""">", "").Replace("</Root>", "");

                //Convert to xmlDocument
                //XmlDocument xDoc = new System.Xml.XmlDocument();
                //xDoc.LoadXml(xe.ToString().Replace(@"<Root xmlns="""">", "").Replace("</Root>", ""));

                doc = new System.Xml.XmlDocument();
                //11. Update OBX index number
                UpdateOBXIndexNumber(doc, 1);
            catch (Exception ex)
                Ameritox.BTIS.Core.Components.Log.Info(trackingNo, hl7Id, "HL7Transform.cs :: Error occured in FormatSeperateORCOBR method\r\n" + ex.ToString(), "General", null);
                throw ex;



Adopted Healthcare Integration Transmission.

HL7 / EDI file using TCP/ IP over VPN : –

  1. Considering the challenges and effort needed to make this happen, I am surprised to see this is still the technology we use to get integrated with most of the healthcare entities such as clinics, hospitals..etc.
  2. In reality, Once the Small Sized Clinics choose the EMR, they tend to stay with it for years and most of them may not opt for the premium support offered by these EMR vendors. Hence the software in the clinic remains old-school and it get hard to get integrated with such EMR using other communication medium.
  3. Main pain point getting integrated using this method, – Network teams from both the ends needs to get involved, exchange the Firewall settings, IP Address, Port numbers and make a Hole in both Firewall so the packets can traverse through it.
  4. Chances of VPN connectivity going down can be seen often  – whenever either entity do any modifications in the Firewall rules / policies.
  5. Here the message would be transmitted in the Transport layer of OSI, hence the overhead of the layers above transport is eliminated.
  6. We can use “ telnet 10.XX.XX.XX <port>” in the command prompt to check if the VPN connectivity is up.
  7. I would recommend this only to entities such as big hospitals / insurance companies which has multiple locations across the country and want to
      • Save over the network bandwidth during exchange of healthcare data (HL7 or HIPAA ) files.
      • Reduce transmission latency.
      • Afford a good network team.

sFTP : –

…To be contd.

Schedule a Task to pull files from sFTP Server

Files from sFTP server can be downloaded to the local system easily using the free tool such as winscp.

1. Download WinSCP From here

WinSCP :: Download

2. Install WinScp to the client machine

3. Copy the below script and paste in a text file called “sFTP_Script.txt” and place it in C:\sFTPPull folder you wish

# Automatically abort script on errors
option batch abort
# Disable overwrite confirmations that conflict with the previous
option confirm off
# Connect using a password
# open s -hostkey="ssh-rsa 1024 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
# Connect
open s -hostkey="ssh-rsa 1024 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
# Change remote directory
cd /
# Force binary mode transfer
option transfer binary
# Download file to the local directory d:\
get *.txt c:\
# Disconnect

4. Either you can download file using command line or by scheduling a task in Windows Task Scheduler.

5. Command line is :-

C:\ProgramFiles\WinSCP\WinSCP.exe /console  /script="C:\sFTPPull \sFTP_ Script.txt"

6. The File would be written to C:\ drive, but it can be changed to the destination folder you wish by editing the “get *.txt C:\ “ line.

Articles Referred :-