Export data from SQL Server as XML

If you want to export data from a SQL Server in into XML, you can use the bulk copy utility (BCP) and FOR XML syntax. For example, lets say you want to export your internet sales by region from AdventureWorksDW as XML. Here is the query
 

SELECT 
	SalesRegion.SalesTerritoryRegion as Region,
	SUM(InternateSales.SalesAmount)  as SalesAmount
FROM dbo.FactInternetSales  InternateSales
 
LEFT JOIN dbo.DimSalesTerritory SalesRegion
ON InternateSales.SalesTerritoryKey = SalesRegion.SalesTerritoryKey
 
GROUP BY SalesRegion.SalesTerritoryRegion
 
FOR XML AUTO,TYPE, ELEMENTS ,ROOT('RegionSales')

 
This will give you result in XML in SSMS. If you want the result to be exported as separate XML document, put this query in BCP like this
 

BCP "SELECT 	SalesRegion.SalesTerritoryRegion as Region,	SUM(InternateSales.SalesAmount)  as SalesAmount FROM AdventureWorksDW2008.dbo.FactInternetSales  InternateSales LEFT JOIN AdventureWorksDW2008.dbo.DimSalesTerritory SalesRegion ON InternateSales.SalesTerritoryKey = SalesRegion.SalesTerritoryKey GROUP BY SalesRegion.SalesTerritoryRegion FOR XML AUTO,TYPE, ELEMENTS ,ROOT('RegionSales')" QUERYOUT "C:\SalesByRegion.XML" -c -t -T -S localhost

 
Replace the server name with you server name and location of XML to where you want it to be.
 
Read more about BCP here[^].
Read more about FOR XML clause here[^].