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