Flattening a Product Catalog Feed With Repeating Fields

Product feeds are the XML most online shops touch. A marketplace or a comparison site publishes the vocabulary, the shop exports its catalog in it, and somewhere a script turns the file into rows for a database or a spreadsheet. The feed looks simple until you flatten it: a namespace you have to map, prices that carry their currency in the text, fields that appear zero, one or many times per product. This post flattens a small feed in the style of the common shopping feeds, shows each trap with the output, and ends with the tables.

The feed

Three products in an RSS envelope. The first has a sale price, three images and two shipping options. The second has a price with a thousands separator and no sale price. The third has no availability and a price without decimals.

<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0">
  <channel>
    <title>Shop</title>
    <item>
      <g:id>A1</g:id><title>Blue mug</title>
      <g:price>12.50 EUR</g:price><g:sale_price>9.90 EUR</g:sale_price>
      <g:availability>in stock</g:availability>
      <g:image_link>https://example.com/a1.jpg</g:image_link>
      <g:additional_image_link>https://example.com/a1b.jpg</g:additional_image_link>
      <g:additional_image_link>https://example.com/a1c.jpg</g:additional_image_link>
      <g:shipping><g:country>DE</g:country><g:price>4.90 EUR</g:price></g:shipping>
      <g:shipping><g:country>FR</g:country><g:price>6.90 EUR</g:price></g:shipping>
    </item>
    <item>
      <g:id>B2</g:id><title>Red &amp; white plate</title>
      <g:price>1,299.00 EUR</g:price>
      <g:availability>out of stock</g:availability>
      <g:image_link>https://example.com/b2.jpg</g:image_link>
    </item>
    <item>
      <g:id>C3</g:id><title>Tea tin</title>
      <g:price>7 EUR</g:price>
      <g:image_link>https://example.com/c3.jpg</g:image_link>
      <g:shipping><g:country>DE</g:country><g:price>0 EUR</g:price></g:shipping>
    </item>
  </channel>
</rss>

Notice two things. The product title is in the default (no) namespace, since it is the RSS title, while the other fields are g: fields. And the elements price appear at two levels: the product's g:price and the g:price inside each g:shipping. A path that says "price" without anchoring picks the wrong one.

Trap 1: the namespace

root.find("channel/item").findtext("price")

naive findtext('price') without namespace: None

None. The element is {http://base.google.com/ns/1.0}price, and the name price with no namespace matches nothing. Map the prefix once and use it in every query: NS = {"g": "http://base.google.com/ns/1.0"}, then findtext("g:price", namespaces=NS). Prefixes are local labels; the URI is the identity (selecting elements with a namespace in XPath). Take the URI from the feed's declaration, not from memory.

Trap 2: prices as text

The price is a string with a number and a currency: 12.50 EUR, 1,299.00 EUR, 7 EUR. Splitting on a space works until it does not. Use a strict pattern, and fail on anything else:

import re
def money(s):
    if s is None:
        return (None, None)
    m = re.fullmatch(r"\s*([\d.,]+)\s+([A-Z]{3})\s*", s)
    if not m:
        raise ValueError(f"bad price {s!r}")
    return (m.group(1).replace(",", ""), m.group(2))

It returns the amount as text and the currency as a three-letter code. The amount stays a string here on purpose, so the conversion to Decimal or a database NUMERIC is a separate, visible step and nothing turns it into a float on the way. Removing the comma assumes a US-style thousands separator. A feed for a European market may use 1.299,00 (dot for thousands, comma for decimals) and the same code would turn it into 1.29900, a wrong number that looks valid. The safe approach is to know the feed's convention from its documentation, assert it in the pattern, and reject what does not fit. A price that fails the pattern raises with the string in the message; add the product id to it so the report names the culprit.

Trap 3: optional fields

Only the first product has a sale_price, and the third lacks availability. findtext returns None for an absent element, and the table gets a null:

id             title   price sale_price currency availability
A1          Blue mug   12.50       9.90      EUR     in stock
B2 Red & white plate 1299.00        NaN      EUR out of stock
C3           Tea tin       7        NaN      EUR          NaN

The & in the title arrived as &, because the parser decodes entities; you do not unescape anything yourself. The nulls (NaN in the printed DataFrame) are honest: a missing sale price is not zero. Keep them as nulls so an average sale price is computed over the products that have one. Whether a missing availability means "unknown" or "in stock" is a business rule, to be decided and written down, not inferred by the parser.

Repeating fields go in child tables

g:shipping and g:additional_image_link occur zero, one or several times per product. Flattening them into columns (shipping1, shipping2, image1, image2) creates a table whose width depends on the largest product. Put them in child tables keyed by the product id:

ship, imgs = [], []
for it in root.iterfind("channel/item"):
    iid = it.findtext("g:id", namespaces=NS)
    for s in it.iterfind("g:shipping", NS):
        ship.append((iid, s.findtext("g:country", namespaces=NS), money(s.findtext("g:price", namespaces=NS))[0]))
    main = it.find("g:image_link", NS)
    imgs += [(iid, i, u.text) for i, u in enumerate([main] + it.findall("g:additional_image_link", NS)) if u is not None]

id country price
A1      DE  4.90
A1      FR  6.90
C3      DE     0
{'A1': 3, 'B2': 1, 'C3': 1}

Shipping has three rows: two for the first product, none for the second, one for the third. Images give three rows for the first product and one each for the others (the main image first, at position zero, then the additional ones in order). The shipping price is read relative to the g:shipping element, so it does not collide with the product price. The product that has no shipping element has no rows, which is the right way to say "none given" in a child table.

The result is three tables: products (one row per item), product_shipping (product id, country, amount) and product_images (product id, position, URL). A join on the id reassembles the product when needed (flattening repeating XML elements into rows discusses the grain).

Before loading

Run the usual checks. The number of item elements equals the rows in products, the number of g:shipping elements equals the rows in product_shipping, ids are unique (a duplicate id in a feed is a data error that a primary key will reject loudly), and no required field is null. Compare the feed's currencies: a feed that mixes EUR and USD cannot be summed or averaged without conversion. For big catalogs, stream with iterparse instead of building the tree (loading a product feed XML into PostgreSQL).

Related reading

This post belongs to XML problems worked from real data. The general techniques are in handling optional XML elements and importing XML product feeds for shopping platforms. The vocabulary is documented in Google's product data specification, and RSS 2.0 in its specification. Test the namespaced paths in the XPath tester.

When a product feed should not be flattened into one table

You only forward the feed. If the job is to pass a catalog from one platform to another, transform it with XSLT or copy it element by element. A relational detour adds a place to lose fields.

The vocabulary is large and evolving. Hundreds of optional attributes become hundreds of mostly null columns. Keep the stable core as columns and the rest as a JSON column.

Variants share a parent. Size and color variants of one product are a hierarchy, with a group id and per-variant fields. Model the group and the variant, not one flat row each.

Regional rules differ. Prices with tax in one market and without in another are different facts. Keep the market in the key.