Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 И все же, попытка сделать генерацию отчетов, используя sql-процедуры и xml  [new]
Виктор_sql
Member

Откуда:
Сообщений: 98
Добрый день,
За основу был взят приводимый по ссылке пример от mnior:
https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=713044&msg=7958157

Сделана тест.проц-ра, выдающая данные для отчета:
+
-------------------------------------------------------------------------------------
CREATE PROCEDURE common.pTest @Xml XML = NULL OUTPUT 
AS

  BEGIN
	
	SELECT  @Xml = ( SELECT
							--''''+',(/Row/text())[1],'+''''
							 12345			AS idp
							,GETDATE()		AS sdate
							,5.15			AS sumplan
							,'Просто тест'	AS Столб
					FOR XML Path('')
				   )
  END
-------------------------------------------------------------------------------------

-- [b]Далее сделана проц-ра вместо ф-ции из взятого примера[/b]

PROCEDURE [common].[pGet_XmlReport]   @Xml		XML
										 ,@Object	SYSNAME								-- Объект БД (проц-ра/таблица/представление/ф-ция)
										 ,@Query	VARCHAR(MAX)	= NULL				
										 ,@Name		NVARCHAR(MAX)	-- = NULL			-- Наименование отчета									 
										 ,@Header	NVARCHAR(MAX)	= NULL				-- Заголовок отчета
										 ,@Footer	NVARCHAR(MAX)	= NULL				-- Блок-окончание отчета
										 ,@Xsl		NVARCHAR(MAX)	= NULL				
AS 
 BEGIN 
    -- SET @Xsl = ''

/*			SELECT	",sql:variable("@ResOut"),"
			.query(''
*/
    
/*
			SELECT	",(/Row/text())[1],"
			FROM	",sql:variable("@Object"),sql:variable("@Query"),"
			FOR XML Path(''Row''),Root(''Table''),Type),'''').query(''			
*/

DECLARE @ResOut  NVARCHAR(MAX) -- XML 
EXEC common.pTest @ResOut OUTPUT 
--SELECT ResOut = @ResOut
	
DECLARE	@Result	NVARCHAR(MAX)

SELECT @Result =	 (									
			SELECT							
			@Xml.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=""WINDOWS-1251""?><?mso-application progid=""Excel.Sheet""?>'' + CONVERT(NVarChar(MAX),ISNULL((
			SELECT	",(/Row/text())[1],"
					",sql:variable("@ResOut"),"
			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>
			  <Style s:ID=""s24"">
								   <Alignment s:Horizontal=""Center""/>
								   <Font s:Bold=""1""/>
			  </Style>   								   		  
			  <Style s:ID=""s25"">
								   <Alignment s:Horizontal=""Left""/>
								   <Font s:Bold=""1""/>
			  </Style>
			 </Styles>
			 <Worksheet s:Name=""",sql:variable("@Name"),""">
			  <Table>
						  
				",(/Style/text())[1],"   
				
				<Row><Cell s:Index=""3"" s:StyleID=""s25""><Data s:Type=""String"">",sql:variable("@Header"),"</Data></Cell></Row>
				<Row></Row>
				
				<Row s:StyleID=""s24"" >	 ",(/Name/text())[1],"  </Row>
				
			   {/Table/Row}

			   <Row></Row>
			   <Row><Cell s:StyleID=""s25""><Data s:Type=""String"">",sql:variable("@Footer"),"</Data></Cell></Row>			   

			  </Table>
			 </Worksheet>
			</Workbook>''))")'
		  ,'NVarChar(max)'		)
		)
     
   EXEC (@Result)   
END
----------------------------------------------------------------------------------------------------------------------------------------
/* Создал представление, только для работы по приводимому прежнему примеру*/
CREATE VIEW [dbo].[vTest]
AS
SELECT     12345 AS idp, GETDATE() AS sdate, 123.11 AS sumplan, 'Тест' AS Столб


------------------------------------------------------------- Далее выполнение всего ----------------------------------------------------
GO
-----------------------------------  Нужно помещать в процедуры, где запрашиваются стат.данные(взято за пример) ----------------------------------------
-- Вместо приводимого блока из взятого примера(по ссылке), где опрашивается sys.columns, в моем случае явно задаются разные наборы определений, соответствующие получаемым наборам данных в исполняемой процедуре
DECLARE @Xml		XML
SELECT @Xml = (
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''  '									-- Если не находим такой тип данных, ставим по умолчанию тип 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('')),'''',''') + '</Data></Cell>'
				  AS [Name/text()]
					
			FROM	sys.columns C	
			WHERE	C.[object_id] = Object_ID('common.vTest')
			ORDER BY C.column_id
			FOR XML Path(''), Type			-- Type задает получаемый результат в виде строки, как тип xml			
)			

-----------------------------------------------------------------------------------------------------------------------------------------

EXEC [common].[pGet_XmlReport]
								  @Xml
								 ,'common.vTest' --@Object	SYSNAME								-- Объект БД (проц-ра/таблица/представление/ф-ция)
								 ,'' --@Query	VARCHAR(MAX)	= NULL				-- Параметр запроса, если такой понадобится
								 ,'Test' --@Name		NVARCHAR(MAX)	-- = NULL			-- Наименование отчета									 
								 ,'Заголовок' --@Header	NVARCHAR(MAX)	= NULL				-- Заголовок отчета
								 ,'Футер' --@Footer	NVARCHAR(MAX)	= NULL				-- Блок-окончание отчета
								 ,'' --@Xsl		NVARCHAR(MAX)	= NULL	

----------------------------------------------------- Ошибка выполнения -----------------------------------
--Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query


Возможно кого-либо заинтересует такой же вопрос и есть желание пояснить, что в написанном неправильно. И вообще, насколько правильно выбран способ
Модератор: Используйте, пожалуйста, тег src для кода, и тег spoiler для портянок кода.


Сообщение было отредактировано: 15 янв 15, 13:20
15 янв 15, 13:17    [17122046]     Ответить | Цитировать Сообщить модератору
 Re: И все же, попытка сделать генерацию отчетов, используя sql-процедуры и xml  [new]
Glory
Member

Откуда:
Сообщений: 104751
Виктор_sql
Возможно кого-либо заинтересует такой же вопрос и есть желание пояснить, что в написанном неправильно.

Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query
15 янв 15, 13:19    [17122059]     Ответить | Цитировать Сообщить модератору
 Re: И все же, попытка сделать генерацию отчетов, используя sql-процедуры и xml  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8826
Виктор_sql,
репортинг сервисез для слабаков, что ли?
15 янв 15, 13:45    [17122220]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить