title: Unserialize PHP serialized data in T-SQL
body: <p>I am trying to extract a gift card code from a Magento order. Some other code uses the Magento API to retrieve the order info as XML from Magento and insert the XML into a MS SQL Server record. Using T-SQL I can use XML functions to parse the XML that was retrieved from the Magento API and get almost everything I need, but the only place the actual gift card code is stored is in the gift_cards field which happens to be a php serialized string.</p>
  
  <p>Examples:</p>
  
  <pre><code>a:1:{i:0;a:5:{s:1:"i";s:1:"1";s:1:"c";s:12:"00XCY8S3ZXCU";s:1:"a";d:119;s:2:"ba";d:119;s:10:"authorized";d:119;}}
  a:3:{i:0;a:5:{s:1:"i";s:2:"10";s:1:"c";s:12:"045EMJJWRCF1";s:1:"a";d:100;s:2:"ba";d:100;s:10:"authorized";d:100;}i:1;a:5:{s:1:"i";s:2:"11";s:1:"c";s:12:"06DUQ7Z5GVT7";s:1:"a";d:101;s:2:"ba";d:101;s:10:"authorized";d:101;}i:2;a:5:{s:1:"i";s:2:"12";s:1:"c";s:12:"07A6MRYW511J";s:1:"a";d:102;s:2:"ba";d:102;s:10:"authorized";d:102;}}
  </code></pre>
  
  <p>The gift card code is the value in the array with the key of "c" such as:
  <code>00XCY8S3ZXCU</code> <code>045EMJJWRCF1</code> <code>06DUQ7Z5GVT7</code> <code>07A6MRYW511J</code></p>
  
  <p>I'm currently trying to tackle this by parsing the value using a T-SQL function, which is like trying to drive a nail with a screw driver. Apparently this has been asked before <a href="https://stackoverflow.com/questions/7779408/unserialize-php-using-microsoft-sql-query">here</a> and the only suggestion was to build a parser from scratch in T-SQL, but that using PHP to unserialize it was the better option.</p>
  
  <p>It would be nice if Magento didn't store PHP serialized data in their database, and then serve it out still serialized in their web services, but that's what I have to work with. I would consider using C# to convert it and store it as a separate field in the database, but it would be a lot more convenient to be able to parse the data in T-SQL. If I were to use C# to parse and unserialize the PHP object I'd probably store it as XML back in the database because that's a much better format to exchange the data in.</p>