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

SSIS Step by Step 005 - Load data from XML file

Requirement & Preface

Load all data from an XML file.
The XML source is from the output of previous demo.
http://simonlv.blogspot.com/2012/08/ssis-step-by-step-004-output-xml-data.html

What's new in this post ?

1. How to load data from an XML file.

Steps

1. Create a new package  - CH03-DEMO02-XMLToData.dtsx and add this two component in control flow.

2.  In EXT_TruncateOrders, truncate table order first.

3.  Data flow Task - DFT_XML_DataToDB

Added a XML source in data flow task.
Notice - there's no XML destination task in data flow task but it has XML source.

4. In XML Source editor, the data access mode is 'XML file location', we select the location which is ouputed by previous demo.

The XSD location will be generated automaticlly when you select an XML location.

See the columns of XML file.

5.  ADO.NET Destination

 

 

6. Execute package and check the results

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

SQL - XML Auto

USE AdventureWorks2008R2

GO

-- Auto with ELEMENTS

SELECT p.BusinessEntityID,

       p.FirstName,

       p.MiddleName,

       p.LastName

FROM Person.Person AS p -- The element name will be same with 'p'

FOR XML AUTO,

ROOT('Persons'),

ELEMENTS XSINIL

/** -- Output --

<Persons xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

   <p>

    <BusinessEntityID>285</BusinessEntityID>

    <FirstName>Syed</FirstName>

    <MiddleName>E</MiddleName>

    <LastName>Abbas</LastName>

  </p>

  <p>

    <BusinessEntityID>293</BusinessEntityID>

    <FirstName>Catherine</FirstName>

    <MiddleName>R.</MiddleName>

    <LastName>Abel</LastName>

  </p>

  <p>

    <BusinessEntityID>295</BusinessEntityID>

    <FirstName>Kim</FirstName>

    <MiddleName xsi:nil="true" />

    <LastName>Abercrombie</LastName>

  </p>

</Persons>

**/

-- Auto ELEMENTS

SELECT person.BusinessEntityID AS ID,

       person.FirstName        AS firstName,

       person.MiddleName       AS middleName,

       person.LastName         AS lastName

FROM Person.Person             AS person  -- Row Name

FOR XML AUTO,

ROOT('Persons'),

ELEMENTS XSINIL

/** -- Output -- Notice the output element name

<Persons xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <person>

    <ID>285</ID>

    <firstName>Syed</firstName>

    <middleName>E</middleName>

    <lastName>Abbas</lastName>

  </person>

  <person>

    <ID>293</ID>

    <firstName>Catherine</firstName>

    <middleName>R.</middleName>

    <lastName>Abel</lastName>

  </person>

  <person>

    <ID>295</ID>

    <firstName>Kim</firstName>

    <middleName xsi:nil="true" />

    <lastName>Abercrombie</lastName>

  </person>

</Persons>

**/