Google Site Map XML

Standard

An example of creating an on-the-fly generated Google Shopping XML.

The basic structure of a google shopping xml is below, with some explanations of a few parts.

<feed xmlns="http://www.w3.org/2005/Atom" xmlns:g="http://base.google.com/ns/1.0">
<!—Website information -->
<title>Site Title</title>
<link>http://siteurl.com</link>
<updated>2013-12-20T13:03:47+00:00</updated>
<author>Author name</author>
<id>Identifier of this sitemap for this website</id>
<!-- start of repeatable section for each product -->
<entry>
<g:id>Unique identifier for this product on this website</g:id>
<title>Product title</title>
<description>Product description</description>
<g:google_product_category>Product category*</g:google_product_category>
<g:product_type>Product type**</g:product_type>
<g:age_group>Age Group</g:age_group>
<g:image_link>Image 1 path</g:image_link>
<g:additional_image_link>Image 2 path</g:additional_image_link>
<g:additional_image_link>Image 3 path</g:additional_image_link>
<link>Url of product page</link> <g:condition>Condition***</g:condition>
<g:availability>Availability</g:availability> <g:price>Price****</g:price>
<g:sale_price/> <g:brand>Brand*</g:brand> <g:gender>Gender</g:gender>
<g:color>Colour</g:color>
<g:size>Size</g:size>
<g:mpn>Manufacture Part Number*****</g:mpn>
<g:shipping> <g:country>Country Code</g:country>
<g:service>Delivery means</g:service>
<g:price>Delivery Price******</g:price>
</g:shipping>
</entry>
<!-- end of repeatable section for each product -->
</feed>

 

Explanation of nodes

Most of the nodes have a namespace of ‘g’ attached; as this is how Google requires the xml to be.

*Product category

This is like taxonomy and is Google specific.  One would need to consult the Google Shopper API documentation to see which category your product(s) fit into.

**Product type

This is also like a taxonomy and again Google specific.  Consult the Google Shopper API documentation to see which you products(s) fit into.

***Price

This is the price in the local currency; if this XML is to be used for the UK, then this will be XX GBP.  The letters denote the currency; consult the documentation to see what your local currency is.

****Manufacturer Part Number

The Manufacturer Part Number (MPN) is used to identify a specific product in the ‘mpn’ attribute if it is accompanied by the manufacturer’s brand name in the ‘brand’ attribute. These values are very important for matching users’ queries to your products.

*****Delivery Price

This is the Fixed delivery price (including VAT). Only delivery-to-consumer rates are allowed.

Full Google Shopping XML details are here http://goo.gl/8QEWNA.

 

Get the XML Dataset

The XML Dataset was built using a rather large SELECT statement to gain the various datafields from the database

Below is the SQL Select.

All the fields have had the semicolon from the namespace removed because it would invalidate the SQL.  In it’s place, gn has been substituted, PHP will replace gn back with a semi-colon when the XML is constructed.

//Begin with select statement by assigning the product id
$query = "SELECT CONCAT('websitename-',products.id,'-',".time().") as gnsid,\n";
 
// the product’s title
$query .= "CONCAT(brands.name,' ',products.model) as title, \n";
 
// the product’s description field
$query .= "products.description as description, \n";
 
// all products were set to be the same product type and product category.  If they were different, a CASE SELECT could be used to check for the appropriate keywords for each product
$query .= "'Apparel & Accessories > Jewelry > Watches' as gnsgoogle_product_category, \n";
$query .= "'Apparel & Accessories > Jewelry > Watches' as gnsproduct_type, \n";
 
// all of these products where for adults in this case, so a select case could be used if the products had an Age field attached
$query .= "'Adult' as gnsage_group, \n";
// the products image – in the case of this website the URL of the images where built up by the baseUrl set from the php config and the various product attributes to build up the file name.
$query .= "CONCAT('" . $baseUrl . "','images/',products.id,'-',Lower(Replace(brands.name, ' ', '-')),'-',Lower(Replace(products.model, ' ', '-')),'-1-thumb.jpg') as gnsimage_link, \n";
 
// if there is more than one picture available for the product
for($i=2;$i<=4;$i++){
if ($i == 2){
$query .= "CONCAT('" . $baseUrl . "','images/',Replace(CONCAT(products.id,'-',Lower(Replace(brands.name, ' ', '-')),'-',Replace(Lower(products.model), ' ', '-')),'', ''),'-2-thumb.jpg') as gnsadditional_image_linkDistinct".$i.", \n";
}
if ($i == 3){
$query .= "CONCAT('" . $baseUrl . "','images/',Replace(CONCAT(products.id,'-',Lower(Replace(brands.name, ' ', '-')),'-',Replace(Lower(products.model), ' ', '-')),'', ''),'-3-thumb.jpg') as gnsadditional_image_linkDistinct".$i.", \n";
}
if ($i == 4){
$query .= "CONCAT('" . $baseUrl . "','images/',Replace(CONCAT(products.id,'-',Lower(Replace(brands.name, ' ', '-')),'-',Replace(Lower(products.model), ' ', '-')),'', ''),'-4-thumb.jpg') as gnsadditional_image_linkDistinct".$i.", \n";
}
}
 
// the URL of the product on the website made from building up the specific filename from the product attributes
$query .= "CONCAT('" . $baseUrl . "','products/',products.id,'/',Lower(Replace(brands.name, ' ', '-')),'-',Lower(Replace(products.model, ' ', '-'))) as link, \n";
 
// a select case to set the correct condition – all these products were used, so they were set to be translated in the same way, but this can be changed to New etc.
$query .= "CASE \n";
$query .= " WHEN Lower(products.grade) = 'unworn' then 'Used' \n";
$query .= " WHEN Lower(products.grade) = 'excellent' then 'Used' \n";
$query .= " WHEN Lower(products.grade) = 'good' then 'Used' \n";
$query .= " WHEN Lower(products.grade) = 'average' then 'Used' \n";
$query .= "END as gnscondition,\n";
 
// a select case to set the correct availability
$query .= "CASE \n";
$query .= " WHEN (products.reserved = 1) then 'Out of Stock' ELSE 'In Stock'\n";
$query .= " WHEN products.reserved = 0 then 'available for order' \n";
$query .= "END as gnsavailability,\n";
 
// the pricing information of the product
$query .= "(products.price) as gnsprice,\n";
$query .= "(products.sale_price) as gnssale_price,\n";
 
// the brand of the product
$query .= "brands.name as gnsbrand, \n";
 
// the gender the product is for
$query .= "products.gender as gnsgender, \n";
 
//the color of the product
$query .= "products.dial as gnscolor, \n";
 
// the size of the product
$query .= "products.size as gnssize, \n";
 
// the MPN was created by combining the product name and model and stripping away the spaces, dot and hyphens
$query .= "Replace(Replace(REPLACE(CONCAT(brands.name,products.model), ' ',''),'-',''),'.','') as gnsmpn \n";
 
// selected from a brand table and products table
$query .= "FROM brands INNER JOIN products ON brands.id = products.brand_id \n";
 
// this database table had been defined to allow alpha-numeric values in the products ‘price’ field, and consequently products as they have been entered and deactivated in the system had the price field set to ENQUIRE, POR, SOLD etc.
// Google requires all products to have a valid price, so all products that had no price had to be skipped. REGEX was used to enable this.
$query .= "WHERE (products.price REGEXP '(^[+-]?[0-9]+\.?[0-9]*e?[0-9]+$)|(0x[0-9A-F]+)') AND
 
// only active products (i.e. in-stock and available to purchase) can be added
products.status = '1'";

 

Below is the PHP to create the XML.

// create a new XML document to save to the server and download.
// the download means the administrator can take a copy as a backup.
// google shopper can read from your domain and consume the generated
// XML by a scheduled update.
$doc = new DomDocument('1.0', 'UTF-8');
// create the root node
$root = $doc->createElement('feed');
// add attribute information
$root = $doc->createElementNS('http://www.w3.org/2005/Atom', 'feed');
$root->setAttributeNS('http://www.w3.org/2000/xmlns/', 'xmlns:g', 'http://base.google.com/ns/1.0');
$root = $doc->appendChild($root);
// the website title
$title = $doc->createElement("title", "Website Title");
$title = $root->appendChild($title);
// the website url
$link = $doc->createElement("link", "http://website.url");
$link = $root->appendChild($link);
// the datetime of last update
$updated = $doc->createElement("updated", date(DATE_ATOM));
$updated = $root->appendChild($updated);
// name of author
$author = $doc->createElement("author", "Author name");
$author = $root->appendChild($author);
$id = $doc->createElement("id", "Unique ID for website shopper xml-use the full website url here appended with xml filename");
$id = $root->appendChild($id);
while($row = mysqli_fetch_assoc($sql)) {
// add a node for each product
$product_node = $doc->createElement("entry");
$product_node = $root->appendChild($product_node);
// add a child node for each field
foreach ($row as $fieldname => $fieldvalue) {
// if the current item is the description
if ($fieldname == "description")
{
// a search and replace to get rid of erroneous characters.
// this database had a few.  One can be cleverer in cleansing
// the information, but a simple string replace can do the job
$fieldvalue = str_replace('â','\'', $fieldvalue);
}
// remove numbers from incremented xml
// as field names needed to be enumerated
if (substr($fieldvalue, -9) == "thumb.jpg" && substr($fieldname,0,-9) == "gnsadditional_image_link")
{
$tempstring = str_replace(substr(BASE_URL,0,-1), BASE_URI, $fieldvalue);
if (file_exists($tempstring)){
$fieldname = substr($fieldname,0,-9);
} else {
$fieldname = substr($fieldname,0,-9);
$fieldvalue="";
}
}
if ($fieldname == "gnsprice" or $fieldname == "gnssale_price")
{
if ($fieldvalue != ""){
$fieldvalue = $fieldvalue . " GBP";
}
}
if (substr($fieldname, 0, 3) == "gns"){
$fieldname = str_replace("gns","g:",$fieldname);
}
$child = $doc->createElement($fieldname);
$child = $product_node->appendChild($child);
$value = $doc->createTextNode($fieldvalue);
$value = $child->appendChild($value);
} // foreach
$shipping = $doc->createElement("g:shipping");
$shipping = $product_node->appendChild($shipping);
$country = $doc->createElement("g:country", "GB");
$country = $shipping->appendChild($country);
$service = $doc->createElement("g:service", "courier");
$service = $shipping->appendChild($service);
$deliveryprice = $doc->createElement("g:price", "0 GBP");
$deliveryprice = $shipping->appendChild($deliveryprice);
} // while
// get completed xml document
$doc->formatOutput = true;
$xml_string = utf8_encode($doc->saveXML());
$newShopperFeed = "websiteurl_shop_feed.xml";
// save the xml to the server
file_put_contents("folder/path/".$newShopperFeed, $xml_string);