Export XML

[Rick Whitelaw]

Hi,

Recently Canada Revenue has announced that as of the tax year 2012 it will no longer be accepting paper T4A forms from payers (employers) submitting more than 50 forms. They will expect the data as XML. The data I need to export has perhaps 20 fields . . . 10 in the main table and around 10 related fields. I've spent a lot of time reading FM documentation and searching the WWW to get an idea of how to even get started. I'm using FMPA 11. RevCan has provided a PDF which looks like it's some kind of schema or format required, plus a lot of .xsd files.

I guess I'm simply asking where I can get started. I know virtually nothing about xml or xsl either. Any suggestion will be greatly appreciated.

Rick.

[comment]

XML and XSLT are general technologies, not specific to Filemaker. There are many resources out there for learning these, e.g.:

http://www.w3schools.com/xml/

In addition, you need to be familiar with Filemaker's own XML grammars:

http://www.filemaker.com/11help/html/import_export.16.32.html#1029602

Basically, you use XSLT to transform between FMPXMLRESULT grammar and your target application schema.

[Rick Whitelaw]

Thanks! I believe I'm started. Will likely have more questions!

[Rick Whitelaw]

Hi,

Posting again as I appear stuck. I'm attaching a zipped PDF file that describes the format Revenue Canada (CRA) expects. I'm assuming their software expects the tags to appear exactly the same. So, for example, when I export "Name_last" from my solution. this must go in CRA's <snm> (surname) tag. So . . . first question: how do I automate the change of tags. Second question: since many of the entries expected by the CRA form are irrelevant for my purposes, how do I skip them, or do I somehow delete them from the schema? Lastly, this PDF has a section at the end that deals with T4A Summary. This is one record per year in my solution and it totals up the number of T4A forms sent and the sum amount. If I am exporting records of table "T4A" will the export just keep duplicating the Summary data? Or should I export from the "Summary" table (one record per year) and somehow export the T4A records as related data (this doesn't seem possible).

Thanks for any help,

RW

t4a-eng.pdf.zip

[comment]

first question: how do I automate the change of tags.

You don't. You insert your data into their tags, e.g.:

...

<snm>

<xsl:value-of select="fmp:COL[2]/fmp:DATA" />

</snm>

...

where Name_last is field #2 in your export fields order.

since many of the entries expected by the CRA form are irrelevant for my purposes, how do I skip them, or do I somehow delete them from the schema?

That's for them to answer. Technically, you can leave these elements empty or remove them altogether. The document you have attached is not a formal schema and doesn't say which elements are required (even if empty) and which are optional.

I didn't understand your last question.

[Rick Whitelaw]

Thanks Comment,

I'm feeling very "thick" on this XML exporting business. I've attached another file which I assume is a proper schema file. I don't have a clue what to do with it although I understand it can be used with a validating parser to check code.

You wrote:

<snm>

<xsl:value-of select="fmp:COL[2]/fmp:DATA" />

</snm>

Would that be a snippet from a stylesheet (xsl) that I should be writing?

Rick.

t4a.xsd.zip

[comment]

Would that be a snippet from a stylesheet (xsl) that I should be writing?

Yes.

I've attached another file which I assume is a proper schema file. I don't have a clue what to do with it although I understand it can be used with a validating parser to check code.

Well, apart from that it can also be read by humans. For example, if you see minOccurs="0" then you know that the element may be skipped. OTOH, when you see type="CanadaAddressType" then you know that some information is missing.

[Rick Whitelaw]

Thanks. I'll continue to try and educate myself about XML. I know I'm missing some simple concepts but the penny will likely drop after the right amount of effort . . . As usual. My only extensive programming has been in FMPA so learning something new will hopefully be refreshing once I wrap my head around it.

Rick.

[Chong-Yee]

Hi, Rick,

I've just spent the last couple of weekends getting my head around XML and FileMaker's export grammar. It looks very daunting at first, so I feel your pain. In fact, it took me over four years to get around this fear (it is a problem that we have had for that long).

I am still very much a novice at XML, but having now got over that hurdle, I must say that it is not as bad as it seems. There is a logic to it. You have to write an XSLT stylesheet and tell FileMaker to apply this stylesheet to the XML it exports. The XML that comes out will be in a row/column format, with the names of the corresponding fields listed towards the top of the XML. You will need to understand the structure of the XML the revenue expects (I find actual examples of expected XML to be more useful than dry documentation), and get the stylesheet to write out the bits of the structure, interspersed with the data from the exported XML file, using <xsl:value-of select="fmp:COL[2]/fmp:DATA" /> etc.

I found the chapter in my "Special Edition: Using FileMaker 9" book (published by Que) to be particularly helpful. There is a detailed walkthrough both of importing and exporting data. I suggest you either get a copy or try to borrow it from a library. The other material I have found useful is "XML in a Nutshell", published by O'Reilly, which I am currently reading.

Hope this helps. Good luck!

Regards,

Chong-Yee

[Rick Whitelaw]

Most of the XSLT examples I've seen transform XML into HTML. Looking at the PDF file I attached earlier, all I want to do is find a way to insert my data into the CRA supplied tags. I haven't a clue how to make a style sheet to accomplish this. The result should be XML.

Thanks for any guidance.