/*
To export the data inb Xml format:
 1) Store the Xml into a Xml object
 2) Create a global temp table and populate it with the Xml
 3) Bcp the data out from the global temp table
 4) Drop the global temp table
*/

DECLARE @xmlData XML

SELECT @xmlData =
	(SELECT
	  '@Id' = CustomerId,
	  '@CompanyName' = CompanyName,
	  'Orders' =
		(SELECT
		   '@Orderid' = o.OrderId,
		   '@OrderDate' = o.OrderDate,
		   '@OrderLineCount' = COUNT(od.OrderId)
		 FROM
		   dbo.Orders o
		   JOIN
		   dbo.[Order Details] od on o.OrderId = od.OrderId
		 WHERE
		   c.CustomerId = o.CustomerId
		 GROUP BY
		   o.OrderId,
		   o.OrderDate
		 FOR
		   XML PATH('Order'),
		   TYPE
		)
	FROM
	  dbo.Customers c
    ORDER BY
      c.CustomerId
	FOR
	  XML PATH('Customer'),
	  ROOT('doc')
	)

create table ##tempXmlData (XmlData Xml)

insert into ##tempXmlData (XmlData)
select @xmlData

declare @cmd nvarchar(4000)
select @cmd = 'bcp "##tempXmlData" out c:\data.xml –w -t "|" -S' + @@servername + ' -T'
exec master..xp_cmdshell @cmd

drop table ##tempXmlData

go