Sometimes it seems like a developer has gone out of their way to make life difficult for themselves (and us poor long suffering DBAs). For example we have an audit trail of user actions in the database that contains xml. This almost makes sense except that the xml is stored in a blob, something like this for example….
3C446F63756D656E743E0D0A20203C6669656C64204E616D653D2249223E444F3C2 F6669656C643E0D0A20203C6669656C64204E616D653D224E4F54223E4C494B453C 2F6669656C643E0D0A20203C6669656C64204E616D653D2244415441223E5045527 6593356745A5735305067304B494341385A6D6C6C62475167546D46745A54306951 6B564A546B6369506B466962475567644738676358566C636E6B6762586B675A474 630595477765A6D6C6C6247512B44516F384C305276593356745A57353050673D3D 3C2F66696C65643E0D0A3C2F446F63756D656E743E
As we have limited space these records are removed after 90 days but can be restored from the backups from up to ten years ago if required, however getting the data back is only part of the problem as we now need to read it. Being a helpful sort of chap I wrote a simple blob to clob function that allows us to get the xml out of the blob, what we get for the above is this:-
<Document> <field Name="I">DO</field> <field Name="NOT">LIKE</field> <field Name="DATA">PERvY3VtZW50Pg0KICA8ZmllbGQgTmFtZT0iQkVJTkciPkFibGUgdG8gcXVlcnkgbXkgZGF0YTwvZmllbGQ+DQo8L0RvY3VtZW50Pg==</filed> </Document>
So what is that DATA field in the middle?
Its a base64 encoded xml document, at this point I gave up and advised the developer that they would have to sort this out themselves as we have an xml document encoded into base64, put in a field in another xml document and then the whole thing is stored in a blob field. Attempting to get meaning out of this data via sql is going to be difficult and frustrating when we know exactly which record is required if someone wants to search the xml for a specific field then we are heading down the rabbit hole into madness especially as we have only attempted to decode a couple of entries so who knows what else is hiding in there.
So instead of shipping this data into the data warehouse we a stuck with backing it up onto tape and restoring it on request along with the front end tool that can read and display it, hopefully the development team won’t mind supporting ten years worth of front ends as well.