Help with XML & XSLT

[Mark Bowen]

Hi there,

I am very new to all the transformation of XML to HTML using XSLT but was wondering if someone could take a stab at leading me in the right direction.

I have been trying for the past 12 hours to create something which I am sure must be able to be done with utter ease but it is just eluding me as to how to do it.

My problem is this :

I work for a commercial production company as the Senior Producer where we create radio adverts. With each advert we have to supply the details of any music tracks used for PRS details.

Basically in the simplest form I have a table in the database called Music Details which stores the ad title, music publisher, music catalogue, cd number, track number, composer and duration.

What I have to do each month is to export this data into a format that can be emailed to the PRS company.

Unfortunately they require this in Word (.doc) format. I know that there is no way that I am going to be able to learn enough to be able to do this using XSLT from Filemaker so I noticed that Word can open up and display HTML files quite well and as the format that they are asking for the data to be supplied in is fairly simple this is what I thought I would do! - INSERT HEADACHE!!

SIDENOTE : I have been using a sort of Mail Merge from within Word at the moment as that is the only way in which I can get this all to work but as I have to submit the details for over 35 stations this is starting to get a little long winded and so I am trying to find a much easier way of doing all of this and automating it as much as possible

What I need to do would be able to be done with the Export as HTML Table in the export dialogue of Filemaker if it wasn't for the fact that unfortunately the PRS company need to have a header part to the document with various parts filled in. INSERT ANOTHER HEADACHE!!

Basically what I need to create is shown here at :

http://www.soundlogic.co.uk/userDownloads/headerTrial.html

This is a very simple, non styled version but will give the general idea I hope.

Where in the table at the bottom there are all the columns such as number of commercials, commercial title and so on I have this data as seperate fields in the database. What I need to do is to spit out these details in rows underneath the headings mentioned just now.

I would like to do this using XML export and an XSLT stylesheet but for the life of me can't figure it out!

All the examples I could find to do with Filemaker and XML-XSLT were all done using a 'for-each' style syntax and I think that what I really need to do is to say (in simple terms as at the moment I feel a bit simple!!)

place this field in this table cell then place this field in this table cell and so on and so forth.

The only other thing I would need to do is to place the station name into the document header in the relevant place and also the month.

I have tried with about 15 different ways now and none of them will even get as far as spitting out a file for me as I keep on getting errors in Filemaker.

If anyone could possibly take a look at this and give me a helping hand then I would be exceptionally greatful as this is starting to give me a headache and I will probably have to otherwise admit defeat and go back to submitting these details in a very long and laborious fashion.

Thankyou in advance for any help with this matter.

Regards,

Mark Bowen

Edited August 15, 2005 by Guest

[Martin Brändle]

Did you check the FileMaker to Word 2003 XML and the two RTF examples by Jay Welshofer and Steve Weintraut on the FileMaker XSLT Library: http://www.filemaker.com/products/technologies/xslt_library.html ?

[Mark Bowen]

Hi Martin,

Thanks for the reply.

Yes I have looked at those tutorials but as they only create very simple textual output I wasn't really too sure of how to get them to create tables and the such.

If you or anyone else has any more ideas about this then I would be exceptionally greatful as doing this submission each month is starting to get on my nerves and I would love to find a quicker way of automating this.

Thanks for the help.

Mark

[Fenton]

I began to answer this yesterday, but my answer seemed a bit negative, and I was hoping for a better solution. But I'll go ahead, as it appears there is no easy solution. I don't have Word 2004. Is the Mac version xml compatible?

So there's no tables in that xml->xsl->Word example? That's too bad, because otherwise it seemed it would do the trick. It appears that MS Word has its own entities defined, using the "w:" prefix, with often just a single letter to specify the entity, "w:r", "w:t" (text element), etc.. From what I saw, and as you said, there is not one for table. I couldn't see it in the list on their web site, and I couldn't view their entire schema, as it is a PC-only installer.

[My earlier post, more or less]

I have to say that their method of gathering data kind of sucks. Using xslt to produce a kind of crappy Word table is what you'd call "devolution". They would be much better off to allow you to submit, and to store the data in xml.

You might want to check out this program, which is made to do this kind of thing:

http://www.chapsoft.com/ezxslt/

You could also do it yourself, producing an html file identical to the one you linked to. I copied their HTML, and it opened in Word(X) as tables, as you'd expect. HTML and xslt can work together, by adding the HTML tags where desired, then adding the XSL elements to produce the FileMaker data within the tags. You would still have to open it in Word and save it as .doc (in order to get all the extra junk that Word adds).

Look at the plainer examples on the FileMaker XML Library site. Specifically the "simple_table.xsl" file, in the XML Examples download. It shows how to use the "call-template" and the "for-each" methods.

The XSL would not be all that hard to write. It is their HTML that is kind of ugly. Their tables are lame. For example:

Station Name:

Reporting Month:

This would what we'd call a "multi-valued" field. Not very good for data storage. But then they're probably just printing and filing this info. It's too awkward to do anything else with it.

Another alternative, on the Mac, is to write the tables in Word directly with AppleScript. Depending on which version of Word. I'm not real fond of Word's AppleScript dictionary, as it is a bit (a lot) eccentric (but most apps' are), but it is large.

[Mark Bowen]

Hi Fenton,

Thanks for the reply. Yes I have seen that programme (EZxslt) before but I don't really have the money for buying something like that unfortunately.

I did have a look at the simple_table.xsl before but as they are using the for-each statements and what I need to do is to place each of the fields of each row into a certain place I wasn't really too sure of how to do that as at the moment if I use that then all the table cells won't line up with the ones above due to them being created afresh and without size information.

Really I think my main problem is that I don't understand enough of XML & XSLT to be able to do this and especially the XML that Filemaker spits out. Such things as how to find out what to write when you want to spit out a certain field instead of every row etc.. I don't know if it is just me or it just seems the Filemaker way is a bit long winded??

Anyway I will put my head back to the exceptionally sharp grindstone and see if I can figure something out.

If in the mean time anyone has any ideas then I would be greatly appreciative of any help given.

Many thanks again.

Mark

[Fenton]

> Really I think my main problem is that I don't understand enough of XML & XSLT to be able to do this and especially the XML that Filemaker spits out. Such things as how to find out what to write when you want to spit out a certain field instead of every row etc..

It is not as big a problem as it looks. For one thing, you can use "for-each" to produce a single piece of data. All you have to do is put it in a section where something only occurs once; such as "FMRESULTSET". Or you can just use "value-of" by itself, if you include the node path to the object from the current location.

XML is hierarchical, can be nested, etc.. There is often more than one way to write the selection for a given piece of data. There are also 2 different formats for exporting xml from FileMaker, "fmdsoresult" and "fmxmlresult". I mostly use "fmxmlresult" because it can also be used for import. "fmdsoresult" is actually a little easier to use for export, but I use "fmxmresult" anyway, just to keep the confusion down. "fmxmlresult" is a little more abstract, but in a logical way.

For example, you can specify a row and/or column (field) by its position in the export order (which would also be its position in the METADATA element). XML does NOT use a "zero-based" index like JavaScript; it starts counting at 1, like normal.

Attached is a stylesheet with a way to do it. It uses templates to "block" FileMaker top-level data that it doesn't want (METADATA, etc.), then continues processing just the RESULTSET.

It produces HTML, using a combination of template matching and for-each. It has a few tricks, like alternating row colors, etc., which you can ignore. But it shows the basic method to create an HTML table with headers, using dynamic FileMaker data.

It could probably be optimized, but it was the best I could do at the time. It's for golf scores (which I don't even play, but I know how to score :-)

HCPvsPar.zip

[Fenton]

Addendum:

You cannot expect to write an xsl stylesheet correctly the 1st time, then test it by exporting from FileMaker; this is a painful way to learn. Get an application that can transform xml/xsl and show you the result. It will tell you if your xsl has syntax errors, and will hopefully produce some result, after processing. You can then at least see something, then try and fix the xsl to get what you want.

I use the free TestXSLT for transformations, used in combination with the BBEdit text editor for creation/editing (also works with free TextWrangler? http://www.barebones.com). There are other dedicated xml/xsl programs, but they tend to be Java apps, with clunky file navigation dialogs (I use Default Folder, I am admittedly spoiled rotten).

First step: Create a FileMaker database with the fields you need. Put some data in. Export as xml, with NO xsl stylesheet (either xml or dso result, that's up to you). This is your base xml to use for transformations; this is what you need to transform to your HTML result.