Variables can be of type XML. You then assign XML that is in string format to those variables. You can retrieve data from that variable. By default, it comes back in 1 row/column. This data can also be stored in the column if that column is of type XML.
There are multiple ways to retrieve data from a regular table into XML output format. The easiest is to SELECT the data FOR XML RAW. That by default produces elements that are named "row". The data in each element is a row from the table you are selecting from. There are options to change the name of the element, make the source columns be output elements, and so on.
Another way to retrieve data is to SELECT the data FOR XML AUTO. The first table selected will generate the outer elements. If you SELECT another table in the query, those records become nested elements in the output.
If you don't like elements and sub-elements, you can use FOR XML PATH. This gives you control over which source data becomes elements, and which becomes attributes in the output.
There is one more way to retrieve data into XML output. That is FOR XML EXPLICIT. This requires specific items being selected to control the output such as the root element tag name. It is not a common technique for grabbing XML data.
