web stats
Write csv from xml column stored in database - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 02-17-2016, 02:54 PM
Colizobble Colizobble is offline
OBX.1 Kenobi
 
Join Date: Mar 2008
Posts: 33
Colizobble
Default 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.
Reply With Quote
  #2  
Old 02-17-2016, 03:46 PM
jackwhaines jackwhaines is offline
 
Join Date: May 2011
Location: Kansas City, Missouri
Posts: 168
jackwhaines is on a distinguished road
Send a message via Skype™ to jackwhaines
Default

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
File Type: xml XML to CSV example.xml (53.3 KB, 203 views)
__________________

-= Jack Haines : Healthcare Integrations, LLC
-= jack.haines@HealthcareIntegrations.com
-= Mirth Connect (Advanced)-certified
-= Gold member of HL7.org
-= Available for Mirth Connect channel development and consultation! Schedule a call with me at https://integrations.as.me
Reply With Quote
  #3  
Old 02-18-2016, 07:36 AM
Colizobble Colizobble is offline
OBX.1 Kenobi
 
Join Date: Mar 2008
Posts: 33
Colizobble
Default

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.
Reply With Quote
  #4  
Old 01-27-2017, 06:31 AM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
 
Join Date: Sep 2014
Location: Michigan
Posts: 556
kirbykn2 is on a distinguished road
Default

Hello Jack,

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

Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

Appliance Version 3.11.2
Mirth Connect Version 3.6.1
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_121-b13
PostgreSQL Version 9.6.3
Reply With Quote
  #5  
Old 01-27-2017, 06:36 AM
jackwhaines jackwhaines is offline
 
Join Date: May 2011
Location: Kansas City, Missouri
Posts: 168
jackwhaines is on a distinguished road
Send a message via Skype™ to jackwhaines
Default

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 : Healthcare Integrations, LLC
-= jack.haines@HealthcareIntegrations.com
-= Mirth Connect (Advanced)-certified
-= Gold member of HL7.org
-= Available for Mirth Connect channel development and consultation! Schedule a call with me at https://integrations.as.me
Reply With Quote
  #6  
Old 01-27-2017, 07:11 AM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
 
Join Date: Sep 2014
Location: Michigan
Posts: 556
kirbykn2 is on a distinguished road
Default

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>>: org.mozilla.javascript.Undefined@45300b47
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)
__________________
Kirby

Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

Appliance Version 3.11.2
Mirth Connect Version 3.6.1
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_121-b13
PostgreSQL Version 9.6.3
Reply With Quote
  #7  
Old 01-27-2017, 07:16 AM
jackwhaines jackwhaines is offline
 
Join Date: May 2011
Location: Kansas City, Missouri
Posts: 168
jackwhaines is on a distinguished road
Send a message via Skype™ to jackwhaines
Default

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 : Healthcare Integrations, LLC
-= jack.haines@HealthcareIntegrations.com
-= Mirth Connect (Advanced)-certified
-= Gold member of HL7.org
-= Available for Mirth Connect channel development and consultation! Schedule a call with me at https://integrations.as.me
Reply With Quote
  #8  
Old 01-27-2017, 07:20 AM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
 
Join Date: Sep 2014
Location: Michigan
Posts: 556
kirbykn2 is on a distinguished road
Default

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);
__________________
Kirby

Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

Appliance Version 3.11.2
Mirth Connect Version 3.6.1
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_121-b13
PostgreSQL Version 9.6.3
Reply With Quote
  #9  
Old 01-27-2017, 07:25 AM
jackwhaines jackwhaines is offline
 
Join Date: May 2011
Location: Kansas City, Missouri
Posts: 168
jackwhaines is on a distinguished road
Send a message via Skype™ to jackwhaines
Default

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 : Healthcare Integrations, LLC
-= jack.haines@HealthcareIntegrations.com
-= Mirth Connect (Advanced)-certified
-= Gold member of HL7.org
-= Available for Mirth Connect channel development and consultation! Schedule a call with me at https://integrations.as.me
Reply With Quote
  #10  
Old 01-27-2017, 08:36 AM
kirbykn2's Avatar
kirbykn2 kirbykn2 is offline
Mirth Guru
 
Join Date: Sep 2014
Location: Michigan
Posts: 556
kirbykn2 is on a distinguished road
Smile

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
__________________
Kirby

Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

Appliance Version 3.11.2
Mirth Connect Version 3.6.1
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_121-b13
PostgreSQL Version 9.6.3
Reply With Quote
Reply

Tags
database reader, xml content

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 08:08 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation