Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Можно ли заставить функцию Mnior'а по созданию Excel xml 2003 файла работать с #tmp?  [new]
uaggster
Member

Откуда:
Сообщений: 692
Вот всем известная (на этом форуме) функция по выводу содержимого табличной функции/view/таблицы в виде Excel-xml-2003:
Автоматизация вывода отчетов в файлы xls (Excel)
+
CREATE FUNCTION [dbo].[fnExcelQuery] (
	 @Object	SysName
) RETURNS NVarChar(max) AS BEGIN RETURN (
SELECT	(
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">' + C.name + '</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")," WITH(NoLock)
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],"
   <Row>",(/Name/text())[1],"</Row>
   {/Table/Row}
  </Table>
 </Worksheet>
</Workbook>''))")','NVarChar(max)')
) END
GO

Вопрос:
А можно ли ее заставить работать с временной таблицей?
Переписать каким то таким образом:
Create table #tmp (id int, com nvarchar(255), dt date)

insert into #tmp
Values (1, 'Два', Cast('20190101' as date))

SELECT	(
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">' + C.name + '</Data></Cell>'
		AS [Name/text()]
FROM	tempdb.sys.columns C
WHERE	C.[object_id] = object_id('tempdb.dbo.#tmp')
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	",#tmp," WITH(NoLock)
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=""",out,""">
  <Table>
   ",(/Style/text())[1],"
   <Row>",(/Name/text())[1],"</Row>
   {/Table/Row}
  </Table>
 </Worksheet>
</Workbook>''))")','NVarChar(max)')


Как обозначить здесь: FROM ",#tmp," WITH(NoLock) - что выборка идет из временной таблицы???
10 сен 19, 10:31    [21967500]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить функцию Mnior'а по созданию Excel xml 2003 файла работать с #tmp?  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1048
uaggster
Вот всем известная (на этом форуме) функция по выводу содержимого табличной функции/view/таблицы в виде Excel-xml-2003:

Как обозначить здесь: FROM ",#tmp," WITH(NoLock) - что выборка идет из временной таблицы???


Чета вы перемудрили...

+
Create table #tmp (id int, com nvarchar(255), dt date)

insert into #tmp
Values (1, 'Два', Cast('20190101' as date))

SELECT	(
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">' + C.name + '</Data></Cell>'
		AS [Name/text()]
FROM	tempdb.sys.columns C
WHERE	C.[object_id] = Object_ID('tempdb..#tmp')
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	#tmp WITH(NoLock)
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=""tmp"">
  <Table>
   ",(/Style/text())[1],"
   <Row>",(/Name/text())[1],"</Row>
   {/Table/Row}
  </Table>
 </Worksheet>
</Workbook>''))")','NVarChar(max)')
10 сен 19, 11:26    [21967531]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли заставить функцию Mnior'а по созданию Excel xml 2003 файла работать с #tmp?  [new]
uaggster
Member

Откуда:
Сообщений: 692
От жеж млять. По другому и не скажешь.

Minamoto, большое спасибо!
10 сен 19, 12:50    [21967649]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить