How to return XML in SQL Server?

Question

I got this problem with a SQL Server table, got this example table named "sales" that shows the sales per day for each vendor
this is the current table:
+-------+-----------+-----------+--------------+
| id | vendor | sales | date |
+-------+-----------+-----------+--------------+
| 1 | John | 10 | 07-20 |
| 2 | John | 5 | 07-20 |
| 3 | Jeff | 15 | 07-21 |
| 4 | Jeff | 20 | 07-21 |
| 5 | John | 5 | 07-21 |
| 5 | Jeff | 30 | 07-20 |

and I would like to transform it into this table below I need to group by vendor and compare the columns of the sales for each day
+-----------+--------------+-------------------+-----------
| vendor |sales 07/20 | sales 07/21 | Variance |
+-----------+--------------+-------------------+-----------
| John | 15 | 5 | -10 |
| Jeff | 30 | 35 | 5 |

Answer 1

You can do this by following SQL command

Select * from Table for xml Raw

It will give you data in XML format. By different options you can create your root elements as well.