xTuple Export to Google Merchant Center

[metersales]

I am using commercial postbooks version 4.11.3. I have created a MetaSQL query to export product information from xTuple to Google Merchant Center to populate a Google Ads Shopping campaign. However, when I use the xTupleXML export under System>Utilities>Export Data, it doesn't format properly. The header and footer need to be changed, and the xTupleXML doesn't convert all of the XML entities correctly. It converts "&" to "&amp;" and "<" to "&lt;", but not ">" to "&gt;", and using the MetaSQL to convert ">" to "&gt;" runs afoul of the "&" to "&amp;" and gives you "&amp;gt;", which leads me to my question.

Has anyone successfully implemented their own alternate XML export from xTuple? I don't have xTupleConnect yet and it has been suggested that I use a CSV export in that module to automate the export to Google Merchant Center. Any comments on that would be helpful as well.

Also, if anyone is interested in the MetaSQL for exporting, just let me know and I will see if I can post it.

[szuke]

We have quite a few customers exporting xml. You're doing this from the Export Data menu option? If you'd like to share your metasql, I'd be happy to take a look. I do often end up using xslt to translate the data to make it suitable for the trading partner.

[metersales]

-- Group:
-- Name: Items on Website
-- Notes:
SELECT
item.item_number AS "g:id",
substring(CONCAT(item.item_number, ': ', item.item_descrip1) for 150) AS "g:title",
--substring(REPLACE(REPLACE(REPLACE(item_ext.item_mrkt_descrip, 'h3>', 'strong>'), '>', '&gt;'), '<', '&lt;') for 5000) AS "g:description",
substring(REPLACE(REPLACE(item_ext.item_mrkt_descrip, 'h3>', 'strong>'), '>', '&gt;') for 5000) AS "g:description",
FORMAT('https://metersales.com/products/%s',item.item_id) AS "g:link",
(SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1) AS "g:image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 1), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 2), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 3), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 4), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 5), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 6), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 7), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 8), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 9), '') AS "g:additional_image_link",
coalesce((SELECT product_images.url FROM xdruple.product_images WHERE item.item_id = product_images."productId" ORDER BY product_images.weight LIMIT 1 OFFSET 10), '') AS "g:additional_image_link",
(CASE WHEN itemsite_qtyonhand > 0 THEN 'in stock'
WHEN item.item_type = 'M' THEN 'preorder'
WHEN item.item_type = 'P' AND item.item_active = TRUE THEN 'preorder'
ELSE 'out of stock'
END) AS "g:availability",
(CASE WHEN itemsite_qtyonhand > 0 THEN current_date
WHEN itemsite_qtyonhand = 0 AND item.item_active = TRUE THEN -- AND item.item_type = 'M'
(SELECT current_date + (
(SELECT charopt.charopt_order FROM public.charopt WHERE charopt.charopt_value =
(SELECT charass.charass_value FROM public.charass WHERE charass.charass_char_id = 40 AND item.item_id = charass.charass_target_id)--40=*Estimated Lead Time
) % 1000))
WHEN itemsite_qtyonhand = 0 THEN current_date
END) AS "g:availability_date",
(CASE WHEN itemsite.itemsite_qtyonhand > 0 THEN FORMAT('%s USD', ROUND((itemsite.itemsite_value/itemsite.itemsite_qtyonhand), 2))
ELSE FORMAT('%s USD', ROUND(itemcost_actcost, 2))
END) AS "g:cost_of_goods_sold",
FORMAT('%s USD', ROUND(item.item_listprice, 2)) AS "g:price",
charass.charass_value AS "g:brand",
'no' AS "g:identifier_exists",
FORMAT('%s lb',(item.item_prodweight + item.item_packweight + 0.5)) AS "g:shipping_weight",
FORMAT('%s in', round(item_ext.item_pack_length + 2, 3)) AS "g:shipping_length",
FORMAT('%s in', round(item_ext.item_pack_width + 2, 3)) AS "g:shipping_width",
FORMAT('%s in', round(item_ext.item_pack_height + 2, 3)) AS "g:shipping_height"
FROM
public.item LEFT JOIN public.charass ON item.item_id = charass.charass_target_id,
xdruple.item_ext,
public.itemsite,
public.itemcost

WHERE
item.item_id = item_ext.item_item_id AND
item.item_id = itemsite.itemsite_item_id AND
item.item_id = itemcost.itemcost_item_id AND
charass.charass_char_id = 23 AND
charass.charass_target_type = 'I' AND
--item.item_id = <? value("item_code") ?> AND <? value("item_code") ?>
item.item_sold = TRUE AND
item.item_active = TRUE AND
item_ext.item_web_published = TRUE;

[metersales]

Yes, I am using the Export Data menu option.

In the MetaSQL query, the g:availability attribute is arrived at using a control flow sequence based partially on an Item Characteristic I added to my database called "*Estimated Lead Time", which in my database has a charass.charass_char_id=40.

[szuke]

Your metasql threw me at first, until I realized what you were doing. I'm a fan of handling the formatting in XSLT. Formatting xml should be a job for an xml tool. A quick google search found multiple xslt examples for the Google Merchant Center.

https://our.umbraco.com/forum/developers/xslt/38787-How-do-I-export-Google-Merchant-Centers-specific-XML-to-use-in-XSLT

Yes, it's possible to do all of this in the metasql, but IMHO, I think it's the wrong tool for the job.

I'm happy to keep sharing. If you haven't went down the path of XALAN, etc, you need to dig in there.

https://xtupleuniversity.xtuple.com/library/articles/yahoo-store-integration

Yahoo Store Set - up Question

Scott