You got XML in my database

You got your database in my XML! And if you haven't tried the dark chocolate Reese's peanut butter cups, I highly suggest you try them. They're delicious.

In SQL Server 2005, Microsoft introduced the XML datatype and while I'm still unconvinced that it's a needed datatype, it does present some interesting possibilities. I have a Post It note on my work computer that simply says "FOR XML 2009-04-24" We had to provide an XML file to a third party that contained a list of all of our advisors and their reporting hierarchy. I knew there was something in SQL server for handling that type of work and my quick look at BOL turned up the FOR clause you can add to a query to transform the resultset into an XML format. It's really that easy. Take your favorite SELECT statement and at the end, add "FOR XML AUTO" and run it. My query generated

<defaults col1="Hello" last_user="tom thumb"/>
<defaults col1="Hola" last_user=""/>
<defaults col1="Hai" last_user="ANGBANDVM\bfellows"/>

That may or may not be what you are interested in. It was not, the vendor had some rules on how the data needed to be formatted and my coworker wasn't interested in exploring this fun new feature. I am slowly getting around to tearing that Post It down and as I work through the power of FOR and OPENXML, I"ll be writing posts on it. Another coworker has a project where they'll be importing hierarchical-type data (NSCC) and it seems like this might be a good project to work with the XML possibilities of SQL Server as SSIS isn't going to be very practical due to the input file being in a painful format.