I can’t believe I never used this handy SQL function before. I knew about it for a couple of years, but I never needed it. When I was doing the import of my Community Server blog to BlogEngine.Net I knew I needed some help stripping out serialized data in the CS_Posts PropertyNames and PropertyValues fields, so I went back to track it down. Specifically I needed to transfer author and website information of comments in cs_posts to the be_PostComment table.
I always enjoyed working with serialized data. I think its slick and efficient. This post from 2006 describes working with serialized weblog extended attributes. The only downside of serialized data as I see it is when you need to query it from a SQL table.
That’s where the FetchExtendedAttributes function saves the day by extracting data values by their key names. Below is how I used the function to pull author and website information from my old cs_posts table.
Here’s the location of the FetchExtendedAttributes function in the Community Server Media Gallery. You oughta grab it.