Loading a Product Feed in XML Into PostgreSQL With xmltable

Shopping platforms want product data as an XML feed, and a shop has to produce one, but the same format is how a supplier hands you their product list, so you often need the reverse direction: take a feed and put it in a table. The common dialect is an RSS 2.0 document where each product is an item and the product-specific fields sit in a namespace owned by the platform. Loading it is a good test of the whole idea, because it has a namespace, a compound value (price and currency in one string), optional fields and text that needs unescaping.

This is the straight-line version for PostgreSQL: one table, one INSERT ... SELECT with xmltable, and the checks that tell you whether it worked.

The feed

<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0">
  <channel>
    <title>Example shop</title>
    <item>
      <g:id>SKU-1</g:id>
      <title>Desk lamp &amp; shade</title>
      <g:price>19.99 USD</g:price>
      <g:availability>in_stock</g:availability>
      <g:gtin>0012345678905</g:gtin>
    </item>
    <item>
      <g:id>SKU-2</g:id>
      <title>Notebook</title>
      <g:price>4.50 USD</g:price>
      <g:availability>out_of_stock</g:availability>
    </item>
    <item>
      <g:id>SKU-3</g:id>
      <title>Pen set</title>
      <g:price></g:price>
      <g:availability>in_stock</g:availability>
      <g:gtin>0098765432109</g:gtin>
    </item>
  </channel>
</rss>

Look at what is in there before writing a line of SQL. The title has no prefix, because it belongs to RSS, while id, price, availability and gtin are in the g namespace. The price carries its currency inside the text. The second item has no g:gtin at all and the third has an empty g:price. GTINs begin with zeros, so they are identifiers and have to be text, not numbers.

The table

create table product(
  sku text primary key,
  title text not null,
  price numeric(10,2),
  currency char(3),
  in_stock boolean not null,
  gtin text
);

Price is nullable because the feed can omit it, and the in_stock column is not, because every product has to have an availability and a missing one should stop the load rather than become a guess. Making that decision in the table definition means the database enforces it for the next feed as well.

The load

psql can put a file into a variable with a backtick command, which keeps the example free of server-side file access:

\set doc `cat feed.xml`
insert into product
select x.sku, x.title,
       nullif(split_part(x.price, ' ', 1), '')::numeric,
       nullif(split_part(x.price, ' ', 2), ''),
       x.availability = 'in_stock',
       x.gtin
from xmltable(xmlnamespaces('http://base.google.com/ns/1.0' as g),
     '/rss/channel/item' passing xmlparse(document :'doc')
     columns sku text path 'g:id', title text path 'title', price text path 'g:price',
             availability text path 'g:availability', gtin text path 'g:gtin') x;

INSERT 0 3

xmlnamespaces binds the prefix g to the namespace URI for the whole call, and the prefix is only a local name: the document could have called it gm and the query would still work, because the match is on the URI. Every column reads as text, and the conversions happen in the outer SELECT. That split is deliberate, and the next section shows why.

select * from product order by sku;

  sku  |       title       | price | currency | in_stock |     gtin      
-------+-------------------+-------+----------+----------+---------------
 SKU-1 | Desk lamp & shade | 19.99 | USD      | t        | 0012345678905
 SKU-2 | Notebook          |  4.50 | USD      | f        | 
 SKU-3 | Pen set           |       |          | t        | 0098765432109
(3 rows)

Three things happened correctly. The title shows & and not &, because the text column holds the value and not its escaped form (why & is in the file at all). The GTINs kept their leading zeros. And SKU-3 has a NULL price and currency instead of a failure.

Why the columns are text first

The tempting version declares price numeric path 'g:price' and lets xmltable convert. On this feed it fails twice, for two different reasons:

select x.sku, x.price from xmltable(xmlnamespaces('http://base.google.com/ns/1.0' as g),
  '/rss/channel/item[g:id="SKU-3"]' passing xmlparse(document :'doc')
  columns sku text path 'g:id', price numeric path 'g:price') x;

ERROR:  invalid input syntax for type numeric: ""

That is the empty <g:price></g:price> of SKU-3. With all three items selected, the first failure is different, invalid input syntax for type numeric: "19.99 USD", because the currency is part of the text. Either way the statement stops, and the error does not say which item caused it. Reading text and converting in the SELECT makes the conversion visible and lets nullif and split_part decide what an empty value means. If the feed is large and you want to keep bad rows rather than lose the batch, the next step is a staging table of text columns and a second statement that moves good rows and rejects the rest, which is the pattern in turning XML feeds into relational tables.

Missing versus empty

The two optional fields behaved differently, and the difference matters when you check data quality.

select sku, gtin is null as gtin_null, gtin = '' as gtin_empty from product;

  sku  | gtin_null | gtin_empty 
-------+-----------+------------
 SKU-1 | f         | f
 SKU-2 | t         | 
 SKU-3 | f         | f
(3 rows)

SKU-2 has no g:gtin element, so the column is NULL. An element that is present and empty, such as SKU-3's price, arrives as an empty string, which is why the load wraps the price in nullif(..., ''). If you test only is null you will miss the empty ones, and if you test only = '' you will miss the absent ones. Normalizing both to NULL in the SELECT gives the table one way to say "unknown". The xmltable column option default fills a missing value instead (gtin text path 'g:gtin' default 'none' gave none for SKU-2 in my run), but it did not touch an element that was present and empty, so it is no replacement for nullif. More on this in handling optional XML elements and missing values.

Making the load repeatable

A feed is loaded again tomorrow, and the second run has to update what changed. Replace the plain INSERT with an upsert keyed on the SKU:

insert into product
select ... -- the same SELECT as above
on conflict (sku) do update
  set title = excluded.title, price = excluded.price, currency = excluded.currency,
      in_stock = excluded.in_stock, gtin = excluded.gtin;

Two refinements for a real product list. Run the whole statement inside a transaction with a row count check, so a feed that arrives truncated (a handful of items instead of 40,000) does not overwrite the table: compare the count of item elements against the previous load first. And decide what a product that vanished from the feed means. Deleting it is rarely right; a flag such as last_seen that you update on each load lets you list the missing ones without losing their history. For feeds that only list what changed, see incremental XML feed imports by last modified.

The function reference is the XMLTABLE section of the PostgreSQL manual, and the format of the Merchant feed itself is documented in the Google Merchant Center product data specification. If you are unsure whether a path will match in a namespace, try it in the XPath tester first. The namespace behavior that makes this work is explained in how XML namespaces work, and the general function in using xmltable in PostgreSQL to turn XML into rows.

When this is not the way to load a feed

The feed is large. xmlparse builds the whole document in memory on the server, and the \set variable holds it in the client. Past a few hundred megabytes, parse with a stream in a script and COPY the rows (loading large XML files into a database in chunks).

You need to keep the original. The load keeps the extracted fields only. If you must be able to show exactly what the supplier sent, store the file in object storage or a bytea column, with its checksum, next to the load log.

The supplier's feed is not well-formed. xmlparse refuses the whole document at the first error. Repair or reject the file before the database sees it, using the techniques in fixing XML parse errors fast.

You need many other fields. A real feed has dozens of optional attributes (color, size, shipping, sale price). Keep the columns you query and put the rest in one jsonb column built from the fields you did not name, rather than a table with sixty mostly-empty columns.