MySQL - convert varbinary to XML file

Question

I am programming a PHP intranet and have problems with extracting of data from older system. It was created with Sharepoint and MSSQL. It stores the data terribly unclean. I found the desired data searching by keyword only by accident with help of HeidiSQL (db management tool). Its fulltext filter is searching also in varbinary columns and one time it converted the varbinary content to meaningful xml data (I saw header + 50 next letters), but only for a second, then it disappeared back to the original value.

I moved data from SQLserver to MySQL via CSV export, so I can work with PHP.

The varbinary contains this value:

0xEFBBBF2F2A205F6C6369643D223130353122205F76657273696F6E3D2231342E302E34373632220D0A5F4C6F63616C42696E64696E67202A2F0D7461626C652E6D732D646973632D6261720D7B0D206261636B67726F756E642D636F6C6F723A233539353935393B0D206261636B67726F756E642D696D6167653A75726C28717569636B6C61756E63686865616465722D44434239423136342E6769662E706E673F63746167293B0D6261636B67726F756E642D7265706561743A7265706561742D783B0D20626F726465723A31707820736F6C696420233236323632363B0D746578742D616C69676E3A6C6566743B0D7D0D7461626C652E6D732D646973632048520D7B0D6865696768743A3170783B0D20636F6C6F723A233539353935393B0D7D0D7461626C652E6D732D646973632074647B0D666F6E742D73697A653A3870743B0D666F6E742D66616D696C793A7461686F6D612C73616E732D73657269663B0D7D0D7461626C652E6D732D646973632074640D7B0D766572746963616C2D616C69676E3A746F703B0D7D0D2E6D732D6469736320617B0D20636F6C6F723A234138323230433B0D746578742D6465636F726174696F6E3A6E6F6E653B0D7D0D2E6D732D6469736320613A686F7665727B0D20636F6C6F723A233030303B0D746578742D6465636F726174696F6E3A756E6465726C696E653B0D7D0D2E6D732D6469736320613A766973697465647B0D20636F6C6F723A234445343631433B0D746578742D6465636F726174696F6E3A6E6F6E653B0D7D0D2E6D732D6469736320613A766973697465643A686F7665727B0D20636F6C6F723A233030303B0D746578742D6465636F726174696F6E3A756E6465726C696E653B0D7D0D7461626C652E6D732D646973632074640D7B0D70616464696E673A303B0D20636F6C6F723A233346334633463B0D7D0D2E6D732D646973632D6E6F7061642C7461626C652074722074642E6D732D646973632D6E6F7061642C7461626C652E6D732D646973632D6E6F7061642074640D7B0D70616464696E673A3070783B0D7D0D7461626C652074722074642E6D732D646973632D70616461626F76657B0D70616464696E673A3570782035707820357078203570783B0D7D0D7461626C652E6D732D646973632D6261722074640D7B0D766572746963616C2D616C69676E3A6D6964646C653B0D6865696768743A323270783B0D70616464696E672D6C6566743A3570783B0D7D0D7461626C652E6D732D646973632D62617220494D470D7B0D766572746963616C2D616C69676E3A6D6964646C653B0D7D0D7461626C652E6D732D646973632D62617220420D7B0D766572746963616C2D616C69676E3A3130253B0D70616464696E672D72696768743A3570783B0D7D0D74642E6D732D646973632D626F7264657265642C74642E6D732D646973632D626F7264657265642D6E6F6C6566740D7B0D20626F726465723A30707820736F6C696420233539353935393B0D626F726465722D746F702D77696474683A3070783B0D7D0D7461626C652074722074642E6D732D646973632D626F7264657265642D6E6F6C6566740D7B0D626F726465722D6C6566742D77696474683A3170783B0D70616464696E673A30707820313070782035707820313070783B0D666F6E742D66616D696C793A76657264616E613B0D666F6E742D73697A653A31656D3B0D746578742D616C69676E3A6C6566743B0D7D0D7461626C652074722074642E6D732D646973632D626F7264657265647B0D70616464696E673A3070782030707820357078203570783B0D7D0D6469762E6D732D646973632D726F6F742D626F6479206469763A66697273742D6368696C6420703A66697273742D6368696C647B0D6D617267696E2D746F703A3070783B0D7D0D

How can I convert it back to meaningful XML? I tried these PHP functions: hexdec(), base64_encode(), base64_decode() - but none is working.

There is 10.000 rows there... Please help me :) Thank you in advance.

If you are retrieving data from SharePoint you should never hit the SQL database, use the API's instead. — Eric Herlitz
– Eric Herlitz, Commented Jan 28, 2014 at 9:46
Sorry, i dont have the access to Sharepoint. I only have the database, which i imported to MS SQLexpress 2008. I found software SSMSBoost for SSMS 2008 and it can visualise the varbinary to text, but only for single item in time. So i am looking for solution which can store 10000 files and not doing it manually. — Gabriel
– Gabriel, Commented Jan 28, 2014 at 10:33
I am using PHP 5.3 and there is no hex2bin, so i found an alternative on PHP manual site: if ( !function_exists( 'hex2bin' ) ) { function hex2bin( $str ) { $sbin = ""; $len = strlen( $str ); for ( $i = 0; $i < $len; $i += 2 ) { $sbin .= pack( "H*", substr( $str, $i, 2 ) ); } return $sbin; } } — Gabriel
– Gabriel, Commented Jan 28, 2014 at 11:00

Gabriel · Accepted Answer · 2014-01-28 11:01:49Z

1

This works well also in PHP <5.4, thank you for your time:

if ( !function_exists( 'hex2bin' ) ) {
    function hex2bin( $str ) {
        $sbin = "";
        $len = strlen( $str );
        for ( $i = 0; $i < $len; $i += 2 ) {
            $sbin .= pack( "H*", substr( $str, $i, 2 ) );
        }

        return $sbin;
    }
}

answered Jan 28, 2014 at 11:01

Gabriel

412 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - convert varbinary to XML file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related