Since SQL SERVER 2005 it is possible to store XML data in database by using dedicated data type - XML. This type solved many programming problems such storing XML data as simple text which was very unpleasant to maintenance and detecting errors in document structure. Now stroring XML data is very simple and requires only the creation of column in XML data type. But sometimes we may want to do reverse operation. I mean generating XML document or fragment from non XML columns in a single SELECT statement. Of course SQL Server meets our expectations and allow to transform any results of SELECT statement (which always returns table) to XML structure. To start transforming selected results to XML format we must use FOR XML clause right after last statemnt in normal SELECT. For example instead of using: Code Snippet SELECT ProductNumber , Name , ListPrice , Color FROM Production . Product we should use: Code Snippet USE [AdventureWorks] SELECT ProductNumber ,
Damian Zapart, Principal Engineering Manager @ Microsoft - official blog.