Need help importing Amazon xml to Filemaker

[Adam Aaron]

Hi I am trying to set up a database that will import amazon data by just entering the ISBN and AWS grabbing the rest. I've read the following post but had no luck.

http://fmforums.com/forum/showtopic.php?tid/153601/post/162185/hl/amazon/#162185

It imports 0 records when using the link below with the xslt from John Lorin Welshofer. (amazon_lite.xslt)

http://webservices.amazon.co.uk/onca/xml?Service=AWSECommerceService&Version=2005-03-23&Operation=ItemLookup&SubscriptionId=0525E2PQ81DD7ZTWTK82&AssociateTag=aaaronorg-20&ItemId=1903083087&IdType=ASIN&ResponseGroup=ItemAttributes

Here is the xml amazon generates:

<?xml version="1.0" encoding="UTF-8"?>



	

		

			

			

		

		

			1P61H16P32PEFXNNEVQC

		

		

			

			

			

			

			

			

			

			

			

			

			

			

			

			

			

			

		

		

			0.0278699398040771

		

	

	

		

			

				True

			

			

				

					ASIN

				

				

					1903083087

				

				

					ItemAttributes

				

			

		

		

			

				1903083087

			

			

				

					Paperback

				

				

					Andrew Benjamin

				

				

					Peter Osborne

				

				

					9781903083086

				

				

					1903083087

				

				

					

						216

					

					

						138

					

					

						408

					

				

				

					

						1499

					

					

						GBP

					

					

						£14.99

					

				

				

					312

				

				

					Book

				

				

					2000-04

				

				

					Clinamen Press Ltd

				

				Walter Benjamin's Philosophy: Destruction and Experience

			

		

	

Basically I want to create a database with most of these fields, (also an author field if the book contains one, this particular book does not)

Another query I have, what is the recommended way to deal with multiple authors or editors etc.?

I am using the programme TestXSLT and editing the XSLT but I'm not really sure what I am supposed to get. Hmmm, thought I'd be saving time doing this rather than entering my whole book collection by hand.

Any pointers would be greatly appreciated.

Adam

[Fenton]

They have changed the syntax of the Amazon Web Services request slightly since I posted that article and files; as I just discovered. Mostly in the 1st part.

There is another issue. That is that there are many different possible requests. I'm not sure what you used to get the above. When I do what seems to be the same, an ItemLookupRequest, for an ASIN, with ItemAttributes, I get fewer fields returned.

It doesn't much matter. But an xsl file must be custom-built depending on the xml expected. It should have all the possible fields you want returned. If less are returned, that's fine, as it isn't a "tab order" kind of thing.

The xml file below translates the above xml into what FileMaker wants. It is rather tedious to build. But not too bad if you use a text editor to Find/Replace the line beginnings and endings onto the element names (hard to describe in a few words).

One tricky bit is because Amazon specifies their own namespace for the elements they return, in the root element:

This is a good idea, because they are using pretty generic element names, , etc..

FileMaker also does this in their first line, effective claiming all elements below for the FileMaker namespace:

So we've got 2 namespaces. We want FileMaker elements with Amazon data. The way to do that (which I learned the hard way), is to define a specific prefix for the Amazon data in the xsl which translates, in the stylesheet declaration:

xmlns:amz="http://webservices.amazon.com/AWSECommerceService/2005-03-23" exclude-result-prefixes="amz">

We don't need the Amazon namespace in the resulting xml for FileMaker import, so we remove it by excluded it (yeah, we add it then remove it; but it won't work without it).

Then we prefix the Amazon data in the xsl:

etc..

I didn't do every possible attribute, like the units for the dimensions. You could.

[Fenton]

For those of you still reading, here's the file(s), which I forgot to attach. It includes a TestXSLT .work file. It does not include an actual URL for fetching the stuff. You can go to AWS and get a developer ID (whatever they're calling it now), then try their examples.

It looks like:

http://webservices.amazon.com/onca/xml?Service=AWSECommerceService&AWSAccessKeyId=Your_DeveloperID&Operation=ItemLookup&responsegroup=ItemAttributes&ItemId=1903083087

You can paste this into a web browser. Or, for a quick test, run an AppleScript like this to put the result into a text file on your desktop:

do shell script "curl 'http://webservices.amazon.com/onca/xml?Service=AWSECommerceService&AWSAccessKeyId=Your_DeveloperID&Operation=ItemLookup&responsegroup=ItemAttributes&ItemId=1903083087' > ~/Desktop/amazon_item.xml"

Ultimately you might want to create a calculation in FileMaker for the URL, then use the XML Import, with http request. This requires also putting your xsl file on a web site, then targetting that with the Amazon syntax to specify an xsl file (it doesn't seem to work otherwise, though I'd like to be proved wrong; I haven't tried if for a while). Hence it's easier to just use the AppleScript to create a local xml file.

ASIN_Lookup.zip

[Fenton]

If anyone is still reading -/, I know I didn't answer the question about how to get multiple creators. It's not difficult to get them. In the xsl, just add a around that element(s) and their attributes.

But, the problem is what to do with this in FileMaker. You cannot import multiple values for something into the same record. It's the same as similar relational problems. You need another table for these multiple creators (and I just noticed there are 2 creators in the xml, both editors).

You can only import into 1 FileMaker table at a time, whether the format is xml or otherwise. The solution is to have a separate table. And a separate xsl file for importing into that table. That's not difficult. Just cut the fields for that table out of the metadata section and put them in a more-or-less duplicate xsl file. Then add the section to select the fields you want (above).

An Import for each table, importing from the same xml file, but calling a different xsl stylesheet.

[Adam Aaron]

Thanks Fenton,

I seem to be able to import the result from the TestXSLT work file but not directly from Amazon. I've pointed it to a copy of the xsl file from your example files that I loaded onto my website. I just seem to get errors from Filemaker. Also when I point it to the xsl in a web browser I get the same result that I do without pointing to it. Am I correct in putting &Style=http://www.aaaron.org/ASIN_Lookup.xsl on the end of the amazon request url?

Cheers,

Adam

[Fenton]

There are a few glitches. First, xml/xsl is case-sensitive. The xml you originally posted is all lower case. The response I get from Amazon is not, it appears to be title case. Look at the xml you get. The xsl must match.

I do not get the same xml you get; I get far less. So I don't know what exactly you're using as a request. [i know why now. I was using "responserequest" (lower case), which was completely ignored because it was not ResponseRequest (title case). Now I get the full fields.]

There is an additional problem, which I didn't see documented at Amazon. It appears that the path to the stylesheet must be URL-encoded. I discovered this by pasting what I though was the correct request URL into Safari. It returned the results, and also translated the path to the xsl file. The translated path works with AppleScript or FileMaker. (Your path will differ; don't use mine, as I'll likely delete or modify the file). You can do the same with yours:

Style=http%3A%2F%2Fwww.fentonjones.com%2FNew%2FASIN%5FLookup%5FNEW.xsl

It's also probably a good idea to match the date of the namespace in the xsl to that returned in the result. Which would look like (I think their suggested "aws" is better than my "amz")

xmlns:aws="http://webservices.amazon.com/AWSECommerceService/2005-10-05"

Heres the files. And it still doesn't solve the "multiple creators" problem. Using the Amazon xsl Style method, you'd need to make the request twice, specifying 2 different xsl files to get such related data.

Or you could use AppleScript and curl to write a local file once, and call the 2 xsl files separately for the FileMaker import. Either way would work. But I'm just ignoring it for now.

XML

<?xml version="1.0" encoding="utf-8"?>



  

    

      

    

    1JJSKHVSYNCRGP7X73XG

    

      

      

      

      

      

    

    0.0157320499420166

  

  

    

      True

      

        1903083087

      

    

    

      1903083087

      SNIPPED

      

        Andrew Benjamin

        Peter Osborn

        Book

        Walter Benjamin's Philosophy : Destruction and Experience

      

    

  

XSL

<?xml version="1.0" encoding="UTF-8"?>



  xmlns:aws="http://webservices.amazon.com/AWSECommerceService/2005-10-05" exclude-result-prefixes="aws">



	

		

			0

			

			

			

				

				

				

				

				

			

			

				

					

						

						

							

							

						

						

						

					

				

			

		

	

Resulting xml for FileMaker:

<?xml version="1.0" encoding="utf-8"?>



  0

  

  

  

    

    

    

    

    

  

  

    

    1903083087

    Andrew Benjamin

    Editor

    Peter Osborn

    Editor

    Book

    Walter Benjamin's Philosophy : Destruction and Experience

    

  

Edited January 15, 2006 by Guest
ReponseRequest