Announcement

Collapse
No announcement yet.

Write csv from xml column stored in database

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

  • Write csv from xml column stored in database

    I have a database reader that is reading an xml document stored in a database row, together with other columns providing metadata (such as destination filename and keys for updating on success).
    I want to write a separate multi-line CSV file for each row, the content coming from a transform of the XML column.
    I have tried to use a database reader as source and file writer as destination. I've mapped the columns, including the xml content. I've used a sample xml document to populate the message template tree, but I'm unsure how to make the xml column data the basis for the mapped variable values.

  • #2
    Maybe try this?

    The source can be XML or a Database Reader.

    Set the destination to File Writer, and make sure to set it to "Append" to create the CSV.

    Sounds like maybe you're just missing setting the Outbound Message Template to the format you want, but I could be wrong. Feel free to post of copy of the channel you are working with, and someone will take a look!
    Attached Files

    -= Jack Haines : Founder/CEO of Healthcare Integrations, LLC
    -= [email protected]
    -= Mirth Connect (Advanced)-certified
    -= Gold member of HL7.org
    -= Available for Mirth Connect channel development and consultation! Schedule a FREE call with me at https://calendly.com/jackhaines

    Comment


    • #3
      Thank you very much for your help. I think I understand how to make it work, but in the end I've just generated the CSV file content in the database query using SQL Server's support for XQuery, as I am more familiar with this. I'm then just using Mirth to write out the file to the target FTP server.

      Comment


      • #4
        Hello Jack,

        In this scenario, what would need to be in the outbound message template?
        Best,

        Kirby

        Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

        Appliance Version 3.11.4
        Mirth Connect Version 3.8.0
        Java Version 1.6.0_45-b06
        Java (64 bit) Version 1.6.0_45-b06
        Java 7 (64 bit) Version 1.7.0_151-b15
        Java 8 (64 bit) Version 1.8.0_181-b13
        PostgreSQL Version 9.6.8

        Comment


        • #5
          kirbykn2,

          In the example channel, I placed
          Code:
          id,name,address,city,state,zip
          in the outbound template, but that was just to make it easier to create the Message Builder steps. There is no NEED for anything in the outbound template.

          -= Jack Haines : Founder/CEO of Healthcare Integrations, LLC
          -= [email protected]
          -= Mirth Connect (Advanced)-certified
          -= Gold member of HL7.org
          -= Available for Mirth Connect channel development and consultation! Schedule a FREE call with me at https://calendly.com/jackhaines

          Comment


          • #6
            Thanks Jack,

            One more question, in your experience, what causes the following error?

            I am connecting to a MS SQL server using a stored procedure. I get this error when setting up the channel as a database reader. When I access the stored procedure using a javascript step in a channel reader, I do not get this error.

            [2017-01-27 11:08:11,205] ERROR (com.mirth.connect.connectors.jdbc.DatabaseReceive rScript:123): An error occurred while polling for messages, retrying after 10000 ms...
            com.mirth.connect.connectors.jdbc.DatabaseReceiver Exception: Unrecognized value returned from script in channel "DI 5", expected ResultSet or List<Map<String, Object>>: [email protected]
            at com.mirth.connect.connectors.jdbc.DatabaseReceiver Script.poll(DatabaseReceiverScript.java:119)
            at com.mirth.connect.connectors.jdbc.DatabaseReceiver .poll(DatabaseReceiver.java:108)
            at com.mirth.connect.donkey.server.channel.PollConnec torJob.execute(PollConnectorJob.java:49)
            at org.quartz.core.JobRunShell.run(JobRunShell.java:2 13)
            at org.quartz.simpl.SimpleThreadPool$WorkerThread.run (SimpleThreadPool.java:557)
            Best,

            Kirby

            Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

            Appliance Version 3.11.4
            Mirth Connect Version 3.8.0
            Java Version 1.6.0_45-b06
            Java (64 bit) Version 1.6.0_45-b06
            Java 7 (64 bit) Version 1.7.0_151-b15
            Java 8 (64 bit) Version 1.8.0_181-b13
            PostgreSQL Version 9.6.8

            Comment


            • #7
              I'm not 100% sure without seeing the code, but I will say that there are certain functions and variables that are not available during the initial database reader call... If it the same code works in one spot (like a javascript step in a transformer) this is generally the cause.

              -= Jack Haines : Founder/CEO of Healthcare Integrations, LLC
              -= [email protected]
              -= Mirth Connect (Advanced)-certified
              -= Gold member of HL7.org
              -= Available for Mirth Connect channel development and consultation! Schedule a FREE call with me at https://calendly.com/jackhaines

              Comment


              • #8
                var statement = ("SELECT ClientFirstName AS firstName, ClientLastName AS lastName, PatientId AS patientId, VisitID AS vistId, DOB AS dob, Phone AS phone, Email AS email, Date AS patientDate, Time AS patientTime, Location AS location, Reason AS reason from DI.MyStoredProc.CSSSchedule");
                var results = executeSharedDBStatementDi(statement, true);
                Best,

                Kirby

                Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

                Appliance Version 3.11.4
                Mirth Connect Version 3.8.0
                Java Version 1.6.0_45-b06
                Java (64 bit) Version 1.6.0_45-b06
                Java 7 (64 bit) Version 1.7.0_151-b15
                Java 8 (64 bit) Version 1.8.0_181-b13
                PostgreSQL Version 9.6.8

                Comment


                • #9
                  I'm assuming "executeSharedDBStatementDi" is a shared function (in the code templates maybe) and that the database reader doesn't have access to something that function is doing or calling.

                  I'm sorry... for me to troubleshoot properly, I would have to have access to your box. =(

                  -= Jack Haines : Founder/CEO of Healthcare Integrations, LLC
                  -= [email protected]
                  -= Mirth Connect (Advanced)-certified
                  -= Gold member of HL7.org
                  -= Available for Mirth Connect channel development and consultation! Schedule a FREE call with me at https://calendly.com/jackhaines

                  Comment


                  • #10
                    Thanks Jack,

                    I moved the db connection string to the DB Reader connector and it works as expected.

                    When I have time I am going to try and figure out why it wouldn't work with my shared DB connection in a code template.

                    Or perhaps Nick Rupley can tell us
                    Best,

                    Kirby

                    Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

                    Appliance Version 3.11.4
                    Mirth Connect Version 3.8.0
                    Java Version 1.6.0_45-b06
                    Java (64 bit) Version 1.6.0_45-b06
                    Java 7 (64 bit) Version 1.7.0_151-b15
                    Java 8 (64 bit) Version 1.8.0_181-b13
                    PostgreSQL Version 9.6.8

                    Comment


                    • #11
                      Originally posted by kirbykn2 View Post
                      Thanks Jack,

                      I moved the db connection string to the DB Reader connector and it works as expected.

                      When I have time I am going to try and figure out why it wouldn't work with my shared DB connection in a code template.

                      Or perhaps Nick Rupley can tell us
                      That's great! Glad you got it working!

                      -= Jack Haines : Founder/CEO of Healthcare Integrations, LLC
                      -= [email protected]
                      -= Mirth Connect (Advanced)-certified
                      -= Gold member of HL7.org
                      -= Available for Mirth Connect channel development and consultation! Schedule a FREE call with me at https://calendly.com/jackhaines

                      Comment

                      Working...
                      X