importing XML Questions 2

[tomp]

I'm using an XML source to create an FM file for the first time and followed with interest the discussion started by Jerry Parlee last week. I was able to use the 'patches' download provided by Fenton (thank you - more useful than any FM documentation I found) to create a style sheet that creates an FM file when doing an open/xml source.

BUT - The style sheet creates the correct number of records and the right fields in the FM file, but it does not fill the fields with the data in the xml file. I've tried various ways to get to the data (as you'll see in the .xsl file) but none work. I've run out of ideas.

I've enclosed a snippet of the xml file below and attached the xsl file (xsl file has a .txt extension so I can 'attach' it.

Could anyone help me get the data into the fields?

TIA

xml file: 2 records; 1 'result' and 1 'reservation'

<?xml version="1.0" encoding="iso-8859-1" ?>

<reservations>

<result>

<status>success</status>

<code>0</code>

<message><![CDATA[reservation download succeeded]]></message>

</result>

<reservation id="488">

<guest>

<firstName><![CDATA[Charles]]></firstName>

<lastName><![CDATA[bailey]]></lastName>

<address><![CDATA[123 Main Street]]></address>

<address2></address2>

<city><![CDATA[Woodbury]]></city>

<state><![CDATA[MN]]></state>

<zip><![CDATA[55131]]></zip>

<country><![CDATA[uSA]]></country>

<email><![CDATA[[email protected]]]></email>

<dayPhone><![CDATA[651-555-1234]]></dayPhone>

<eveningPhone></eveningPhone>

</guest>

<resDetails>

<confirmationNumber>20021</confirmationNumber>

<unitName></unitName>

<typeName><![CDATA[double]]></typeName>

<numberOfUnits>1</numberOfUnits>

<dateReserved>20041228165256</dateReserved>

<arrivalDate>2005-03-10</arrivalDate>

<arrivalTime></arrivalTime>

<departureTime></departureTime>

<daysReserved>3</daysReserved>

<adults>2</adults>

<children>2</children>

<priceDetail></priceDetail>

<subTotal>330.00</subTotal>

<price>367.95</price>

<comments></comments>

</resDetails>

<payment>

<creditCardName><![CDATA[Charles Bailey]]></creditCardName>

<creditCardType>Visa</creditCardType>

<creditCardNumber>4444222211113333</creditCardNumber>

<creditCardExpiration>04/2007</creditCardExpiration>

<creditCardSecurityCode>789</creditCardSecurityCode>

</payment>

<misc>

<guestQuestion1></guestQuestion1>

<guestQuestion2></guestQuestion2>

</misc>

</reservation>

</reservations>

DownloadExport.txt

[Fenton]

You were on the right track. But the data has somewhat more structure than you allowed for. And I think you started one level too high, for what would determine the FileMaker record. This is a common problem, since XML is both "flat" and relational at the same time.

The data is in a few sections. Looking at the data, especially if there was more than 1 reservation, the first section is <result>. I bet there'd only be 1 of these for every download; telling you the status of the transaction.

Next section is the <reservation>. This is really our FileMaker record. That is what we'd use for <xsl:for-each>. We'd go back up one level to get the <result>, which would repeat the same for all reservations in this download. That <result> block could possibly be left out, especially if the <code>0</code> just means "no error".

Within <reservation> there are a few sections also. The first is <guest>, then <resDetails>, then <payment>, then <misc>.

You have to specify these to get their data, which are nested elements within them.

One misconception (probably due to the fact that my last example, the Microsoft software update thing, was mostly attributes):

@ is only needed to get the attribute of an element. You don't need it if the data is within elements. The only attribute in your xml is the id, <reservation id="488">. This is a much better format really. I'll comment below:

<xsl:for-each select="/reservations/reservation">

It's the reservation that is the FileMaker record

<ROW MODID="" RECORDID="">

<COL><DATA>

<xsl:value-of select="../result/status" />

</DATA></COL>

Go back 1 level, then down into <result> to get the repetitive data

<COL><DATA>

<xsl:value-of select="@id" />

</DATA></COL>

This is our only attribute, of <reservation> (which is the current node)

<COL><DATA>

<xsl:value-of select="./guest/firstName" />

</DATA></COL>

Go down inside the next level (guest in this case) to get the data

Reservations.zip

[tomp]

Fenton, thank you for going above & beyond - and for the prompt response. I certainly didn't expect a working .xsl file!

I have learned a lot today from the earlier discussion and now this.

This forum has saved a great deal of frustration and time.

Thanks again!

Tom

[Fenton]

Well, I was going to post a non-working xsl file, but I thought, what the heck :-/

I'd like to take this occasion to thank my editor, BBEdit, who made it possible to Find & Replace within a Selection. Otherwise it would have been tedious. Not to mention the pretty colors. And my testing and rendering application, which told me which bits were coming through: TestXSLT, free, at:

http://www.entropy.ch/software/macosx/

It can also render an HTML result, if applicable, using the same web engine as Safari (gotta love open source).

BBEdit's creators have another app, TextWrangler, which they are now giving away free (it was $50). It's like BBEdit Lite was, but more powerful. It supports xml syntax coloring.

http://www.barebones.com

A companion to it (also free) is the BBTidy extension (a version of the Tidy engine), available on the extensions page. It will run from the Tools menu.

BBEdit supposedly checks XML with its Markup, Tidy "Convert to XML" command. But it doesn't work on XML! Only on HTML. It's kind of a bug, and they know (I called them; they were surprised it didn't work -).