SSIS Step by Step 006 - Use XSLT to transform XML documents

Requirement & Preface

XSL stands for EXtensible Stylesheet Language, and is a style sheet language for XML documents.
XSLT stands for XSL Transformations. We have an XML file and want to transfer it to another style according to user-defined xslt document.

 

What's new in this post ?

1. How to use XML task to transfer XML style

Steps
1. Create a new package -CH04-DEMO01-XMLTask.dtsx

Drag an XML task to control flow.

2.  Edit it and create three XML file connection manager and configure then as below:

Operation Type - XSLT, we will use standard xslt document to transfer target XML file.

Source - U01-CH04-002-XMLDemo.xml

Codes:

<?xml version="1.0"?>

-<extract date="2007-12-05"> -<counters> -<counter name="server1" category="dispatcher"> <runtime>6</runtime> <queue>3</queue> <maxrequest>8</maxrequest> <color>blue</color> -<host> <name>svo2555</name> <path>\\dispatcher</path> <lastaccessed>2007-02-03</lastaccessed> </host> </counter> -<counter name="server1" category="gateway"> <runtime>1</runtime> <queue>10</queue> <maxrequest>10</maxrequest> <color>purple</color> -<host> <name>svo2555</name> <path>\\gateway</path> <lastaccessed>2007-02-03</lastaccessed> </host> </counter> </counters></extract>

Schema of xslt - U01-CH04-002-XMLSchema.xslt

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

        <xsl:output method="xml" indent="yes"/>

        <xsl:template match="/extract">

                <xsl:variable name="extractDate" select="/extract/@date" />   

                <counters>

                        <xsl:for-each select="counters/counter">

                                <counter>           

                                        <extractDate><xsl:value-of select="$extractDate"/></extractDate>           

                                        <category><xsl:value-of select="@category"/></category>           

                                        <name><xsl:value-of select="@name"/></name>           

                                        <runtime><xsl:value-of select="runtime"/></runtime>           

                                        <queue><xsl:value-of select="queue"/></queue>           

                                        <maxrequest><xsl:value-of select="maxrequest"/></maxrequest>           

                                        <color><xsl:value-of select="color"/></color>           

                                        <hostName><xsl:value-of select="host/name"/></hostName>           

                                        <path><xsl:value-of select="host/path"/></path>           

                                        <lastaccessed><xsl:value-of select="host/lastaccessed"/></lastaccessed>       

                                </counter>   

                        </xsl:for-each>   

                </counters>

        </xsl:template>

</xsl:stylesheet>

3. Execute package and get the output XML file

Get all SSIS Step by Step, refer to SIMON'S SSIS Step by Step Please let me know if you have any questions about this blog. Contact to me by email - simonlvpin@yahoo.com Or Skype - simonlvpin