In my last blog I briefly touched upon storing XML documents in MSSQL. Now let’s discuss creating them instead. Specifically I mean creating them using the FOR XML keywords in MSSQL. While this functionality exists in other database systems such as Oracle and DB2, their implementation is different and warrants a separate blog post.
Most of us are familiar with selecting data using a SQL command and retrieving some sort of result set. With XML being used so frequently for a wide range of applications, it is not uncommon for a programmer to want to build an XML message out of data stored in a database somewhere. Instead of using SQL queries and looping over result sets, it is possible in MSSQL to use the FOR XML declaration to actually return that data as pre-formatted XML. Continue reading