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[^].