MySQL 5.0: Output BLOB data in (well-formed) XML format?

Question

I'm writing a web interface to MySQL that executes a couple queries and returns some XML data with the results of the queries. Right now, the table the query is running on has three LONGBLOB columns (for storing image data and the like).

However, when I try to parse the XML, I run into trouble with certain characters in the BLOB columns: it looks like MySQL is just dumping the straight hex data into the stream, which is not being accepted by my parser.

Is there a way I can have MySQL output well-formed XML that contains hex data? I looked at the mysqldump --hex-blob option, but I'm working with the mysql program since I need to run queries on tables, not just get the entire table's contents. I'd be OK with a string representation, an xs:hexBinary element in the <field> tag, or anything similar.

Edit: I'm working in PHP 5.2.9. The background of the question is to provide a sort of Digg-like API with multiple HTTP-accessible endpoints, e.g. /objects, /edit, and so forth. The idea is that a number of client applications across multiple platforms can use the API as a common ground to access the same dataset without having to care about the database backing it (which is subject to change).

One of the object types I'm storing is a (small) image, and I'd like to do so directly in the database using the MySQL LONGBLOB type. (I'm only responsible for making the API work with the MySQL backend database, so answers can take the DB type, language, and utilities as givens.) Basically what I'm looking to do is find a way to provide the image back to the requesting application within the same XML document as some other mixed-type data (mostly strings).

Edit: I'm getting the XML out of MySQL using a combination of the mysql --xml command-line program and the PHP passthru function. This was the easiest way to get done what I wanted at the time I was developing it; is there a better way? Should I be building XML in PHP from the MySQL fields rather than depending on MySQL to do it for me? And if so, would that make sending image data in the XML easier?

Answer 1

XML doesn't support storing binary data directly, use Base64 encoding

Answer 2

You can't put straight binary data into an XML document. You can put any character that's valid in your encoding (probably UTF-8) in an XML document if you place it inside a CDATA section (and it doesn't happen to contain the string "< ! [CDATA["), but raw binary data will have invalid UTF-8 sequences in it. You will need to convert the binary data into some kind of textual representation.

Using xs:hexBinary format (i.e., [0-9A-F]{2}) is going to produce 2 characters (bytes) for each byte of binary data.

Using Base64 encoding uses 4 characters for every 3 bytes of data (2^8/2^6), so it's a more efficient format. Unfortunately, doing this from MySQL is messy, so it's probably better to extract the data from MySQL in binary format and encode it into Base64 in your web app.

Edit: SO seems to parse the CDATA section. I added spaces around the "!" to make it stop parsing it and effing up the formatting.

edit:

Without knowing much about what you're doing, let me give an outline of one way to do this:

// ... setup and exec query...
while ($row = mysql_fetch_assoc($rs)) {
    print('<item>');
    print('<date>' . $row['date'] . '</date>');
    // ... etc. ...
    // more efficient to use mysql_result but this is simpler
    $img = $row['image'];
    print('<image>' . base64_encode($img) . '</image>');
    // CDATA is *probably* not needed
    print('<image><![CDATA[' . base64_encode($img) . ']]></image>');
    // or, if you decide you want hex format anyway...
    print('<image>' . unpack('H*', $img) . '</image>');
}

This should produce strictly valid XML. Come to think of it, the hex data you have now should be fine in XML. What parser are you using?

Answer 3

It would probably make UTF8-encoding the image easier, and it would also make your site more responsive if you did the mysql query and built the xml in php... Fork & exec are expensive operations....

You could just use simpleXml and do something like:

$myxml->addChild('imageBlob', base64_encode($mysql_row['imageBlob']));