Click here to Skip to main content
Click here to Skip to main content
Go to top

Export data from SQL Server as XML

, 10 Aug 2011
Rate this:
Please Sign up or sign in to vote.
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[^].

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

seankk
Software Developer
United Kingdom United Kingdom
SQL Server developer and a geek

Comments and Discussions

 
QuestionWorked Perfectly! PinmemberMember 81519518-Apr-14 8:01 
GeneralMy vote of 5 Pinmemberawni22-Sep-13 0:49 
QuestionSQL to XML made simple - new open source tool available PinmemberDavid Webber11-Oct-12 15:55 
GeneralReason for my vote of 4 Good one PinmemberShahriar Iqbal Chowdhury10-Aug-11 23:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140926.1 | Last Updated 10 Aug 2011
Article Copyright 2011 by seankk
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid