SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

---------------------------------------
-- Usage:
-- 
-- Declare @ret int
-- exec @ret = [dbo].[OutToExcelFile] 'dbo.test', '', 'D:\MMSSQL\test.xml'
-- Select @ret
-- 
-- Параметры:
-- @object - table, view или функция, возвращающая табличное значение
-- @query - дополнительный отбор, должен выглядеть как 'Where ... and ...'
-- @path - Имя файла и путь. Папка может быть сетевой, но SQLSERVER должен иметь в нее разрешения. 
---------------------------------------

CREATE proc [dbo].[OutToExcelFile]
	 @object sysname
	,@query nvarchar(4000)
	,@path sysname
as
Begin
	set nocount on;
	set xact_abort on;
	
	DECLARE	@Result Int
	Declare @Command nvarchar(4000)

	SET	 @Command = 'bcp "' 
	+ Replace(Replace(dbo.fnExcelQuery(@object, @query),'"',''''''),'
',' ') -- должно быть именно так, это перевод строки!			
	+ '" queryout "' + @path + '" -S . -T -q -w'

	--print @Command
	EXEC @Result = master.dbo.xp_CmdShell @Command, no_output
		
	IF (@Result = 0) return 0
		ELSE return -1 -- была ошибка
End
GO

CREATE FUNCTION [dbo].[fnExcelQuery] (
	 @Object	SysName
	,@Query		NVarChar(max)
) RETURNS NVarChar(max) AS BEGIN RETURN ((
SELECT	  CASE	WHEN C.column_id = 1
		THEN ' '
		ELSE '
	,'	END + 'NULL AS [text()],'
	+ CASE	WHEN C.system_type_id IN (48,52,56,59,60,62,106,108,122,127)
							THEN '''Number''  '
		WHEN C.system_type_id IN (58,61)	THEN '''DateTime'''
		WHEN C.system_type_id = 104		THEN '''Boolean'' '
							ELSE '''String''  '
		END + 'AS [Cell/Data/@s:Type],'
	+ QuoteName(C.name) + Space(Max(Len(C.name))OVER() - Len(C.name)) + ' AS [Cell/Data/text()]'
		AS [Row/text()]
	, IsNull('<Column s:StyleID="'
	+ CASE	WHEN C.system_type_id IN (48,52,56,59,62,106,108,127)
							THEN 'Number'
		WHEN C.system_type_id IN (60,122)	THEN 'Currency'
		WHEN C.system_type_id IN (58,61)	THEN 'DateTime'
		WHEN C.system_type_id = 104		THEN 'Boolean'
		END + '" />','<Column />')
		AS [Style/text()]
	,'<Cell><Data s:Type="String">' + RePlace((SELECT C.name AS [*] FOR XML Path('')),'''','&apos;') + '</Data></Cell>'
		AS [Name/text()]
FROM	sys.columns C
WHERE	C.[object_id] = Object_ID(@Object)
ORDER BY C.column_id
FOR XML Path(''),Type).query('(<Row>{/Row/text()}</Row>,<Style>{/Style/text()}</Style>,<Name>{/Name/text()}</Name>)').value('fn:concat("
;WITH XMLNAMESPACES(Default ''urn:schemas-microsoft-com:office:spreadsheet'',''urn:schemas-microsoft-com:office:spreadsheet'' AS s)
SELECT	N''<?xml version=""1.0"" encoding=""UTF-16""?><?mso-application progid=""Excel.Sheet""?>'' + Convert(NVarChar(max),IsNull((
SELECT	",(/Row/text())[1],"
FROM	",sql:variable("@Object"),sql:variable("@Query"),"
FOR XML Path(''Row''),Root(''Table''),Type),'''').query(''
<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:s=""urn:schemas-microsoft-com:office:spreadsheet"">
 <Styles>
  <Style s:ID=""Boolean""><NumberFormat s:Format=""True/False"" /></Style>
  <Style s:ID=""Currency""><NumberFormat s:Format=""Currency"" /></Style>
  <Style s:ID=""Number""><NumberFormat s:Format=""General Number"" /></Style>
  <Style s:ID=""DateTime""><NumberFormat s:Format=""General Date"" /></Style>
 </Styles>
 <Worksheet s:Name=""",sql:variable("@Object"),""">
  <Table>
   ",(/Style/text())[1],"   &lt;Row&gt;
   ",(/Name/text())[1],"   &lt;/Row&gt;
   {/Table/Row}
  </Table>
 </Worksheet>
</Workbook>''))")','NVarChar(max)')
) END
GO