تحويل XSL على MS SQL بدون CLR

CREATE FUNCTION [dbo].[f_Helper_XSLTransform]
(
	@XMLData	XML,
	@XSLTemplate	XML
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

	IF @XMLData IS NULL OR @XSLTemplate IS NULL
		RETURN 'XML or XSL data is NULL'
		 
	DECLARE	@OLEActionName	VARCHAR(128),
		@PropOrMethodName	VARCHAR(128)

	DECLARE @hr				INT, 
		@dummy			INT,
		@ObjXML			INT, 
		@ObjXSL			INT,
		@ObjStream		INT,
		@BuffSize			INT

	DECLARE @Result			NVARCHAR(MAX) = N'',
		@Chunk			NVARCHAR(4000),		-- VVVV should match VVVV
		@ChunkCharSize	INT = 4000,			-- ^^^^^^^^^^^^^^^^^^^^^^
		@ResultBuff		NVARCHAR(MAX) = N''	-- chunk concat buffer

	-- create XMLDOM object for @XMLData
	SET @OLEActionName 	= 'sp_OACreate'
	SET @PropOrMethodName 	= 'Microsoft.XMLDOM'
	
	EXEC @hr = sp_OACreate @PropOrMethodName, @ObjXML OUT
	IF @hr <> 0 
	GOTO FUN_ERROR

	-- create XMLDOM object for @XSLData
	EXEC @hr = sp_OACreate @PropOrMethodName, @ObjXSL OUT
	IF @hr <> 0 
		GOTO FUN_ERROR

	-- create ADODB.Stream object as transformation buffer
	SET @PropOrMethodName 	= 'ADODB.Stream'

	EXEC @hr = sp_OACreate @PropOrMethodName, @ObjStream OUT
	IF @hr <> 0 
		GOTO FUN_ERROR

	-- load XML data
	SET @OLEActionName 	= 'sp_OAMethod'
	SET @PropOrMethodName 	= 'LoadXML'

	EXEC @hr = sp_OAMethod @ObjXML, @PropOrMethodName, @dummy OUT, @XMLData
	IF @hr <> 0 
		GOTO FUN_ERROR

	-- load XSL data
	EXEC @hr = sp_OAMethod @ObjXSL, @PropOrMethodName, @dummy OUT, @XSLTemplate
	IF @hr <> 0 
		GOTO FUN_ERROR

	-- open the stream
	SET @PropOrMethodName 	= 'Open'
	
	EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, NULL
	IF @hr <> 0 
		GOTO FUN_ERROR

	-- trying to do XSL transformation, using the stream as the receiver to work around 4/8K limitation
	SET @PropOrMethodName 	= 'TransformNodeToObject'

	EXEC @hr = sp_OAMethod @ObjXML, @PropOrMethodName, NULL, @ObjXSL, @ObjStream
	IF @hr <> 0 
		GOTO FUN_ERROR

	-- get the size of the stream to read back
	SET @OLEActionName 	= 'sp_OAGetProperty'
	SET @PropOrMethodName 	= 'Size'
	
	EXEC @hr = sp_OAGetProperty @ObjStream, @PropOrMethodName, @BuffSize OUT
	IF @hr <> 0 
		GOTO FUN_ERROR

	-- re-position the stream to the head..
	SET @OLEActionName 	= 'sp_OASetProperty'
	SET @PropOrMethodName 	= 'Position'

	EXEC @hr = sp_OASetProperty @ObjStream, 'Position', 0	-- offset = 0
	IF @hr <> 0 
		GOTO FUN_ERROR

	-- ..and then read chunk by chunk
	SET @OLEActionName 	= 'sp_OAMethod'
	SET @PropOrMethodName 	= 'ReadText'

	WHILE @BuffSize > @ChunkCharSize
	BEGIN
		-- read chunk
		EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, @Chunk OUT, @ChunkCharSize
		IF @hr <> 0 
			GOTO FUN_ERROR
	
		-- append it to the accumulated buffer contents..
		SET @ResultBuff += @Chunk
		-- ..and correct the char counter by the # of chars retrieved
		SET @BuffSize -= @ChunkCharSize
	END

	-- read the last chunk
	EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, @Chunk OUT
	IF @hr <> 0 
		GOTO FUN_ERROR

	-- append the last chunk to the buffer
	SET @ResultBuff += @Chunk

	-- close the stream
	SET @PropOrMethodName = 'Close'
	EXEC @hr = sp_OAMethod @ObjStream, @PropOrMethodName, NULL
	IF @hr <> 0 
		GOTO FUN_ERROR

	-- everything is ok, copying buffer to result
	SET @Result = @ResultBuff
	
	-- resulting doc is in @Result, cleaning up and exiting..
	GOTO OLE_RELEASE

FUN_ERROR:

	SET @Result = '#### Error ' + CONVERT(VARCHAR, CONVERT(VARBINARY(4), @hr)) + ', Action ' + @OLEActionName + ', Method/Property ' + @PropOrMethodName + ' ####'
	-- fall through OLE release

OLE_RELEASE:

	-- destroying XML..
	SET @OLEActionName 	= 'sp_OADestroy'
	SET @PropOrMethodName 	= 'Microsoft.XMLDOM'
	IF ISNULL(@ObjXML, 0) <> 0
	BEGIN
		EXEC @hr = sp_OADestroy @ObjXML
		IF @hr <> 0 
			GOTO OLE_RELEASE_ERROR
	END

	-- ..and XSL objects
	IF ISNULL(@ObjXSL, 0) <> 0
	BEGIN	
		EXEC @hr = sp_OADestroy @ObjXSL
		IF @hr <> 0 
			GOTO OLE_RELEASE_ERROR
	END	 

	-- and the stream obj
	SET @PropOrMethodName 	= 'ADODB.Stream'
	IF ISNULL(@ObjStream, 0) <> 0
	BEGIN	
		EXEC @hr = sp_OADestroy @ObjStream
		IF @hr <> 0 
			GOTO OLE_RELEASE_ERROR
	END	 

	-- exiting with returning the resulting document
	RETURN @Result

OLE_RELEASE_ERROR:

	-- OLE release error, exiting with error info
	RETURN '#### Error ' + CONVERT(VARCHAR, CONVERT(VARBINARY(4), @hr)) + ', Action ' + @OLEActionName + ', Method/Property ' + @PropOrMethodName + ' ####'

END

declare @xml xml = convert(xml, '<root><item id="1" name="john"/><item id="2" name="bob"/></root>')

declare @templatetext varchar(max) = '<?xml version=''1.0'' encoding=''UTF-8''?>
						<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
						<xsl:template match = ''item''>
						<tr><td><xsl:value-of select = ''@id'' /></td><td><xsl:value-of select = ''@name'' /></td></tr>
						</xsl:template>
						<xsl:template match = ''/''>
							<HTML>
							  <BODY>
								<TABLE>
								  <TR><TD colspan=''2''>Item List</TD></TR>
								  <xsl:apply-templates select = ''root'' />
								</TABLE>
							  </BODY>
							</HTML>
						  </xsl:template>
						</xsl:stylesheet>'

declare @xsl xml = convert(xml, @templatetext)

select dbo.f_Helper_XSLTransform(@xml, @xsl)

<HTML><BODY><TABLE><TR><TD colspan="2">Item List</TD></TR><tr><td>1</td><td>john</td></tr><tr><td>2</td><td>bob</td></tr></TABLE></BODY></HTML>