Announcement

Collapse
No announcement yet.

SQL -> XML -> Delimited (CSV)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL -> XML -> Delimited (CSV)

    #Mirth Connect 3.5.1


    I was trying to run a sql query and get the XML the mirth source channel transforms the results into and convert that into CSV.

    I have changed the Data Types to XML on inbound and outbound to Delimited Text. I doesn't seem to convert to delimited.

    I've tried changing some of the serialization and deserialization, but don't seem to get the right settings.

    I've tried changing the outbound template to include my headers.

    I've tried manually parsing the XML to get the javascript, but I feel like there is probably an easier way to do it with the default settings.

    Will correctly setting the data types and the serialization and deserialization settings allow mirth to convert the XML returned from the SQL query to CSV?

  • #2
    You have to map the values. You can't just say inbound is XMl and outbound is comma delimited and expect Mirth to know what to do with that because these are two different data types. Even if you have CD to CD or HL7 to HL7 you may still have to do some manual mapping...unless it's a passthrough with no manipulation required.

    You'll need to tell Mirth how to move the data from XML to CD for each field.

    tmp['row']['column1'] = msg['EVN']['EVN.1']['EVN.1.1'].toString();

    Also, why not just map the results to the CD fields? Why mess with the XML version of the query results? I've attached a sample channel that is barebones to give you an idea.
    Attached Files
    Last edited by appsbyaaron; 11-01-2017, 07:17 AM.

    Comment


    • #3
      Ok. Thanks. I thought it would auto map each result to a row and each column to the specified delimiter.

      I'll manually map them.

      Comment


      • #4
        You can use JavaScript to do that, in Source/Destination Transformer:

        Code:
        var finalCSV="";
        var len=msg.children().length();
        var i=1;
        
        for each(csv in msg.children())
        {
            if(i<len)
                finalCSV+=csv + ",";
            else
                finalCSV+=csv;
        
            i++;
        }
        
        channelMap.put("CSV", finalCSV);
        works perfectly.


        Best Regards
        Best Regards,
        Alex Neiva

        Comment


        • #5
          Hi Alex,
          could you please help me to convert xml to csv using mirth connector. can u pls share channel which you used for above code

          Comment


          • #6
            The above code doesn't work for you?
            HL7v2.7 Certified Control Specialist!

            Comment


            • #7
              It doesn't have any of the escaping logic in it. It will produce bad or questionable output if any of the fields contain commas, newlines, or double quotes.

              Comment


              • #8
                All you need to do is convert the xml format returned by the database reader to the format expected by the delimited data type. This will return all fields in the order that they were queried and use the outbound delimited data type properties when serializing. Do not use an outbound template with this solution.
                Code:
                var newMsg = <delimited/>;
                newMsg.appendChild(msg);
                newMsg.result.setName('row');
                msg = newMsg;
                If you are using aggregate results in your db reader, it would look like this instead:

                Code:
                msg.setName('delimited'); // technically, this line is optional.
                for each (var result in msg.result) result.setName('row');

                Comment


                • #9
                  Hi Agermano - Where to provide this code. also can you please suggest what values to enter in the source & destination

                  Comment


                  • #10
                    This code should go in a transformer. It can be the source transformer or any destination transformer depending on your needs. It should be placed in a javascript type step. Your inbound data type for that transformer should be XML, and your outbound type should be Delimited.

                    I recommend reviewing the User Guide from the mirth downloads page. It answers a lot of the basic questions about transformers, data types, and connectors.

                    Comment


                    • #11
                      I'm getting below error. Please find the attached channel for reference. could you please check and let me know what's issue

                      TypeError: setName is not a function, it is xml.
                      Attached Files
                      Last edited by yeruva; 09-13-2018, 11:10 AM.

                      Comment


                      • #12
                        Your source connector is a Channel Reader, not a Database Reader. This thread was specifically for converting the XML returned by a Database Reader. If your XML messages are in a different format, please start a new thread.

                        Comment


                        • #13
                          I expanded Tonys code to also include a header row:

                          Code:
                          msg.setName('delimited'); // technically, this line is optional.
                          for each (var result in msg.result) result.setName('row');
                          
                          //get the name of each element and add it as a header row
                          var headerRow = new XML('<row/>');
                          for each (var element in msg.row[0].children()){
                          	if (element != null){
                          		var name = element.name();
                          		var headerColumn = new XML('<' + name + '>' + name + '</' + name + '>');
                          		headerRow.appendChild(headerColumn);
                          	}
                          }
                          msg.prependChild(headerRow);
                          This looks clunky but it works.
                          Jon Bartels

                          Zen is hiring!!!!
                          http://consultzen.com/careers/
                          Talented healthcare IT professionals wanted. Engineers to sales to management.
                          Good benefits, great working environment, genuinely interesting work.

                          Comment


                          • #14
                            Originally posted by agermano View Post
                            All you need to do is convert the xml format returned by the database reader to the format expected by the delimited data type ...

                            Code:
                            msg.setName('delimited'); // technically, this line is optional.
                            for each (var result in msg.result) result.setName('row');
                            Whoa! This was incredibly useful to me. Thanks for posting it.

                            Tell me, how did you learn the details of the internal representations Mirth Connect uses for its various data types? I've been working with Connect for years, and the whole time I've been frustrated that I can't find clear and complete documentation of stuff like this. If you know of a resource I should be using, I'll be grateful to hear it!

                            Comment


                            • #15
                              For the code you quoted, it was just looking at the transformed content of the two types and noticed they had a similar structure.

                              I do spend quite a bit of time poking around in the source code when I'm curious how certain things work. It's sparse on documentation, but the code base itself is pretty clean and easy to follow most of the time.

                              Today happens to be the 1-year annivesary of the announcement that they moved the public repo to github, which makes it more accessible than it was previously.

                              http://www.mirthproject.org/communit...d.php?t=218333

                              A local copy of the repo and a good IDE helps with browsing.

                              Comment

                              Working...
                              X