Exporting XML for Google Merchant Center

[Steven1337]

Hello,

My truck accessory company uses Google's Merchant Center to put our items in their google product search. One way to upload your items is using XML (which is what I use).

To put all the items from my filemaker database into an XML format I use a PHP page which disguises itself as an xml page. And just basically echos the xml in the google format. I have over 20k items and my method is time consuming. One because the php times out since I have so many tiems so I have to break down the database into bits.

I've been looking into exporting the database and all the fields that I need in XML straight from the database. But how would I place the fields within the mark up tags that I specify? Does an XSL stylesheet do this for you? I'm new to XSL I just need a step in the right direction.

[comment]

But how would I place the fields within the mark up tags that I specify? Does an XSL stylesheet do this for you?

Yes. When you export as XML without a stylesheet, you get markup in one of Filemaker's grammars (either FMPXMLRESULT or FMPDSORESULT). To transform this into a schema that can be used by the target application, you need to use a XSLT stylesheet during the export.

[Steven1337]

Ok that's what I thought. So I just need to format this XSLT style sheet so that the fields in the database go in between the marks up I set up on it. Is there an example of this being done anywhere? My knowledge of XML and XSLT is very limited.

[comment]

I suggest you start here for a basic introduction:

http://my.advisor.com/doc/12009

There are tons of examples in these forums (check the Import/Export and the XML/XSL sections). And there are many XSL resources (not Filemaker specific) on the web, e.g.

http://www.w3schools.com/xsl/

If you post some details (like what fields you have and how the final document needs to look), we can help you get started.

[Steven1337]

This is the php code i use to create the pages. It should give you an idea of the format and fields.

<?php require_once('../Connections/Rackspace.php'); ?>

<?php 

 header('content-type: text/xml');

echo '<?xml version="1.0"?>' . "n";

echo '' . "n";

echo '' . "n";

echo 'Discount Truck Accessories' . "n";

echo 'http://www.discounttruckaccessories.com' . "n";

echo 'Truck accessory products updated daily' . "n";

  ?>

<?php

$items_find = $Rackspace->newFindCommand('googlefeed');

$items_findCriterions = array('xml'=>'=='.fmsEscape('g'),);

foreach($items_findCriterions as $key=>$value) {

    $items_find->AddFindCriterion($key,$value);

}



fmsSetPage($items_find,'items',3000); 



$items_result = $items_find->execute(); 



if(FileMaker::isError($items_result)) fmsTrapError($items_result,"error.php"); 



fmsSetLastPage($items_result,'items',3000); 



$items_row = current($items_result->getRecords());



 



$items_row = current($items_result->getRecords());





foreach($items_result->getRecords() as $items_row){

echo '' . "n";

echo ''. htmlspecialchars($items_row->getField('ProductManufacturer')). " " .  htmlspecialchars($items_row->getField('_kp_PartNumber')) . " ".htmlspecialchars($items_row->getField('DescriptionShort')) . " "  . htmlspecialchars($items_row->getField('VehicleId')) . '' . "n";

echo '' .  htmlspecialchars($items_row->getField('ProductPrice')) . '' . "n";

echo 'http://www.discounttruckaccessories.com/details.php?ProductID=' . htmlspecialchars($items_row->getField('ProductID')) . '' . "n";

echo '' . htmlspecialchars($items_row->getField('ProductID')) . '' . "n";

echo '' . htmlspecialchars($items_row->getField('VehicleCategory')) . " " . htmlspecialchars($items_row->getField('VehicleId')) . " " . htmlspecialchars($items_row->getField('VehicleDetail')) . " " . htmlspecialchars($items_row->getField('year')) . " " . htmlspecialchars($items_row->getField('BedSize')) . htmlspecialchars($items_row->getField('Cab')) . " " . htmlspecialchars($items_row->getField('DescriptionLong')) . '' . "n";

echo 'new' . "n";

echo '' . htmlspecialchars($items_row->getField('VehicleCategory')) . '' . "n";

echo 'http://72.32.124.231:8020/SuperContainer/RawData/Images/Inventory/' . htmlspecialchars($items_row->getField('ProductID')) . '' . "n";

echo '' . htmlspecialchars($items_row->getField('_kp_PartNumber')) . '' . "n";

echo '' . htmlspecialchars($items_row->getField('MainCategory')) . '>' . htmlspecialchars($items_row->getField('SubCategory1')) . '>' . htmlspecialchars($items_row->getField('SubCategory2')) . '' . "n";

echo '' . htmlspecialchars($items_row->getField('Weight')) . '' . "n";

echo '' . "n";

}

echo '' . "n";

echo '' . "n";





 // FMStudio v1.0 - do not remove comment, needed for DreamWeaver support 















?>

The resulting page should look like this:

<?xml version="1.0"?>





Discount Truck Accessories

http://www.discounttruckaccessories.com

Truck accessory products updated daily



Truxedo 888601 Truck Roll Up Tonneau Cover Titan

368.1

http://www.discounttruckaccessories.com/details.php?ProductID=108220

108220



Nissan Titan w/ or w/out Track System 2004, 2005, 2006, 2007, 2008, 2009, 2010 6.5' Bed TruXedo used the latest cutting edge technology to bring you a truck bed cover that not only looks great, but is extremely functional at a great price. The TruXedo Edge mounts between your bed rails allowing you to utilize your stake pockets for racks and tie down points. Installing a Truxedo Edge tonneau cover with it's clamp-on design is simple and easy without doing any drilling or cutting. TruXedo has added a tension control system that allows you to keep your truck bed cover tight with no tools required and the velcro closures along the sides are among the widest in the industry. Opening and closing the Edge is a breeze with TruXedo's one finger quick release mechanism. The Edge truck bed cover is backed by TruXedo with a full 5 year warranty

new

Nissan

http://72.32.124.231:8020/SuperContainer/RawData/Images/Inventory/108220

888601

Tonneau Covers>Soft Tonneau Covers>TruXedo Edge Tonneau Cover

40

XML works perfectly for what I am trying to do since I can combine different fields into one mark up tag. This helps for SEO purposes.

[comment]

Could you export this exact record as XML using FMPDSORESULT and attach it here?

[Steven1337]

Here it is.

xml.zip

[comment]

Try the attached.

Note that this is tailored to the FMPDSORESULT schema - which means it will break if you change your field names. The FMPXMLRESULT variant, OTOH, depends on the field export order.

DSO2RSS.zip

[Steven1337]

Thanks a lot once I try this out I'll let you know how it works.

[Steven1337]

I get the attached error when doing the export. It works fine when i do a found set of other items. So maybe it could be that I have illegal characters in one of items when i try to export the whole database. I'm actually pretty sure that's what that is. Unless you specify something different.

Its going to be hard hunting down these illegal characters.

Edited October 20, 2010 by Guest

[comment]

Do you get the same error if you export without a stylesheet?

[Steven1337]

I don't. It creates the file but i read this comment at the top:

I dont know if that is normal.

Also I scrolled down to the end to see if it did all the records and it does not. I exported 20k items and there are only 13k lines of code. Plus the ending line is a description which is cut off.

Maybe the problem is the size of the file? I can probably try to see what the limit is and split my items once again.

[comment]

The DSO grammar has been deprecated for ages - that's nothing to worry about. I don't know why it didn't export all records - perhaps it's the same problem as before, just without an error message. I would go to the record whose description has been cut off and look for the illegal character there.

[Steven1337]

I began to split the items and export them in smaller chunks. The second set of items i was exporting gave me the same invalid character error and it is only 3k of them. It sucks because even though it gives me the line and column in which its in it creates it in a temp file. Then a blank file is created.

So I think you're right it might not have exported all the records because of the same error. The challenge now is to find these illegal characters with in the thousands of records and their many fields.

But that is my problem to solve your style sheet works perfectly I uploaded successfully to Google with the first set of items that worked fine. I'm going to try to browse to the temp file that was created and track all the characters from there.