XML - Why won't you play nice?

I don't know anything about Crystal Reports, but in order to get from XML to PDF, you can use a program called http://princexml.com. You need to write a CSS style sheet, defining the formatting you want for each of the XML tags that your files use. You can specify line breaks after certain elements but not after others, as you require. Prince can be invoked from the command line, so you could write a shell script to invoke it consecutively on each of your XML files (after exporting them from the database, I guess). So, that's one option: XML + CSS --> PDF.

The task I am hearing here is "I need to transfer some data from one EMR to another EMR". Is the PDF step the only way the other db can import it? Can it read CSV? This is the sort of question where keeping the main task in mind, and knowing who the players are (what is EMR1, and EMR2) might make a huge difference. PDF as a middle layer sounds... unusual... to me. For transforming the xml into fields, lots of scripting languages can help. Sorry to be vague, but the question is vague!

http://www.w3schools.com/xslfo/default.asp is designed for custom transformation of XML to formatted print including PDF. Agreed with gregglind that PDF being a required middle ground would be odd. If I was spec-ing my desires for moving data from one platform to another PDF as a middle man would be near my last choice.

This is definitely the kind of thing where you probably have to write a special purpose script, unless of course there is something existing that is specifically designed for this. The problem isn't just formatting XML, it's that you have a mix of XML and table columns. One approach would be something in perl or whatever that would read each row out of the DB and add the Name/DOB/Account stuff to the XML schema and write it all out in a combined XML blob. Then you could use whatever XML tools you want to format that, XSLT/CSS, etc. Or you could do the XML parsing in the script so that you output everything as plain text or HTML or something and then use whatever program you want to create PDF from that. All of this is pretty straightforward to someone that's worked with XML and scripting, so I don't think it would take too long.

http://ask.metafilter.com/165729/XML-Why-wont-you-play-nice#2382045: "For transforming the xml into fields, lots of scripting languages can help. " You can do this in postgres itself with the http://www.postgresql.org/docs/current/static/functions-xml.html#FUNCTIONS-XML-PROCESSING query built-in.

Is the XML data in a column of type xml? Recent versions of Postgres offer the http://www.postgresql.org/docs/9/static/functions-xml.html#FUNCTIONS-XML-PROCESSING function with which you can select text out of an XML value.

Heh, too slow.

Not taking anything away from the other suggestions offered above, but based on your description of the tasks at hand I am fairly certain that you could also do this in FileMaker 11 Advanced. (Disclaimer: I am a FileMaker developer and tend to see it as a Swiss Army knife for these sorts of situations; YMMV.) I am not suggesting that FMP11 is the only way to do this, or the best way, or the quickest way (though it may be). But based on the details you provided, I am fairly certain one could do this in FMP11 without too much struggling. FMP11 will import XML directly, though you will likely need to define an XSL style sheet for field mapping on import. Once the data is in FMP, you can use FMP's native scripting environment to loop through the records and save the output as a series of PDF files (using the "Save Records as PDF" script step.) Output file names can be set dynamically in the scripting environment using variables. Again, not suggesting this is the only/best/quickest way to do this, but after reading your question I would suggest that this is a user-friendly option. FileMaker Pro 11 is available as a free 30 day trial from FileMaker.com, though I'm not sure if FileMaker Pro Advanced is available in a trial version. You would probably do better with the Advanced version, which has a script debugger, more robust import capabilities, and some other developer-friendly features. http://www.filemaker.com/products/compare/fmp_vs_fmpa.html AFAIK, the only limitation of the trial version is its 30 day time bomb, so you could download it and poke at it for an hour or two and see if it does what you need it to.

Is the PDF step the only way the other db can import it? Unfortunately, yes. The problem with this data is that none of it is structured, so we couldn't map any of the relevant data to the second EMR even if we wanted to. We had thought at first that we might be able to just import the problem list data without ICD-9 codes but were told by the EMR2 vendor that we have to have certain data for them to map it. Since in most EMRs the problem lists (i.e. active diseases/conditions) are structured so that there's an ICD-9 code, that provider that added it, the onset date, and the problem status with each problem; in this EMR there's just an "Active Problems" section that's a giant unformatted, freetext block. I've actually migrated clinical data from two other EMRs without incident so I was a little surprised when I found out this particular one is a glorified word processor. Is the XML data in a column of type xml? No, unfortunately, it's stored as text and not xml. If I was spec-ing my desires for moving data from one platform to another PDF as a middle man would be near my last choice. In this case most of the patients already have a chart in EMR2 with the clinical data, our providers just want to have a copy of the "Clinical Summary" from EMR1 for reference. you can use a program called Prince. Thanks for this, it looks like it might be what I was looking for.