Lightroom SQLite database binary XMP format

Question

Lightroom catalog is a SQLite database. Some of the metadata values are stored under Adobe_AdditionalMetadata.XMP column which is a BLOB data type.

When I save this blob, it is some binary file that I have no idea how to convert to/from an editable form.

According to the documentation, xmp file has XML format

Here is the example of such blob from my database

Answer 1

I was advised on the Lightroom forums, such columns use non-standard SQLite compress module.

Here is the link to the SQLite Windows binaries recompiled with non-standard compress module: https://github.com/mnaoumov/sqlite-lightroom-compress-fix

It can extract the xmp properties

select uncompress(xmp) from Adobe_AdditionalMetadata where id_local = 4539794;

But cannot write it back yet.

update Adobe_AdditionalMetadata
set xmp = compress('some valid xmp string value')
where id_local = 4539794;

It executes successfully but you won't be able to read it back with uncompress() function.

That's because the library from the link above fixes only uncompress() function. I am working to fix the compress() function as well