MSDN WebCast Demo: CLR UDF: Transforming XML with XSLT
-- CLR UDF: Transforming XML with XSLT
-- Execute every statement in the sample in order
-- (c) 2004, 2005 Microsoft Corp.
-----------------------------
--- Initialization for demo
-----------------------------
use
master
use msdn2005
go
-- First step: Compile TransformXML.cs to generate TransformXml.dll
IF
EXISTS (SELECT * FROM sys.objects
WHERE name='ApplyXslTransform' AND type='FS')
DROP FUNCTION ApplyXslTransform
GO
IF
EXISTS (SELECT * FROM sys.assemblies WHERE name='TransformXml')
DROP ASSEMBLY TransformXml
go
IF
EXISTS (SELECT * FROM sys.tables WHERE name='docs')
DROP TABLE docs
GO
-- Create Example Data
CREATE TABLE docs (id INT PRIMARY KEY, xbook XML)
GO
INSERT
INTO docs VALUES (1,
N'<book ISBN=''0-7356-1588-2''>
<chapter num=''1''>
<title>Background</title>
</chapter>
<chapter num=''5''>
<title>Epilogue</title>
</chapter>
</book>')
GO
INSERT
INTO docs
SELECT 2, xbook.x
FROM OPENROWSET
(BULK 'Insert your path here\xmlfile.xml'
, SINGLE_BLOB) AS xbook(x)
-- Create assembly for XSL transformation
create assembly TransformXml
from 'Insert your path here\TransformXml.dll'
with permission_set = UNSAFE
GO
----------------------------------------------------
-- Create functions on assembly
----------------------------------------------------
--- XSL transformation function definition
CREATE FUNCTION ApplyXslTransform(
@xData XML,
@xslPath nvarchar(1000))
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME TransformXml.TransformXml.ApplyXslTransform
GO
-- Table contents
SELECT *
FROM docs
GO
----------------------------------------------
----- Apply XSL transformation to query result
----------------------------------------------
DECLARE @xData XML
DECLARE @xslPath nvarchar(1000)
SELECT @xData = xbook.query('
<alltopics>{
/book[@ISBN = "0-1896-1899-3"]//chapter[@num >= 2]
}</alltopics>')
FROM docs
WHERE id=2
-- Set the path of the XSL transformation
SET @xslPath = 'Insert your path here\myxsl.xsl'
SELECT dbo.ApplyXslTransform (@xData, @xslPath)
GO
----------------------------------------
-- Apply XSL transform to any XML column
----------------------------------------
-- Apply XSL transformation to full document
SELECT id, dbo.ApplyXslTransform(xbook, 'Insert your path here\myxsl.xsl') AS NEWCOL
FROM docs
GO
-- Apply XSL transformation to first section only
SELECT id, dbo.ApplyXslTransform(xbook.query('//chapter[1]'), 'Insert your path here\myxsl.xsl')
FROM docs
GO