Monthly Archives: April 2022

Generating a Google Merchant Feed using just SQL

Leave a reply

Example of using SQL with explicit xml with grouping sets to build the nested elements
and to include the namespaces on only the top elements

SELECT CASE
WHEN GROUPING_ID(link) = 0 THEN
3
WHEN GROUPING_ID(rs.[title]) = 0 THEN
2
ELSE
1
END AS tag,
CASE
WHEN GROUPING_ID(link) = 0 THEN
2
WHEN GROUPING_ID(rs.[title]) = 0 THEN
1
ELSE
NULL
END AS parent,
NULL AS [rss!1],
rs.[title] AS [channel!2!title!element],
rs.[channel_link] AS [channel!2!link!element],
rs.link AS [item!3!link!element],
rs.[id] AS [item!3!g:id!element],
rs.[price] AS [item!3!g:price!element],
rs.[sale_price] AS [item!3!g:sale_price!element],
rs.[quantity] AS [item!3!g:quantity!element],
rs.[availability] AS [item!3!g:availability!element],
rs.[gtin] AS [item!3!g:gtin!element],
rs.[brand] AS [item!3!g:brand!element],
'http://base.google.com/ns/1.0' AS [rss!1!xmlns:g]
FROM rs
GROUP BY GROUPING SETS((), (rs.[title], rs.[channel_link]), (rs.link, rs.[id], rs.[price], rs.[sale_price], rs.[quantity], rs.[availability], rs.[gtin], rs.[brand]))
ORDER BY rs.[id]
FOR XML EXPLICIT, TYPE;