Yesterday I had been working in SQL to create an xml document from a query I needed to make key value pairs out of my column list using one attribute as the key and the element content as the value. Pretty common pattern in XML right? I am aware of and have used the PIVOT key word however using it to describe what I am doing here seemed like the best choice of overloaded term. After several of failed attempts and some googling I threw the following query below into the management studio and finally got the output I had been looking for. The solution? simply add a '' between your elements telling sql server that you have a whitespace value between the two elements.
