XML Task – Changing Style of Data – XSLT

I wrote about Validation with XML Task in SSIS here before, now I want to write about XSLT in this post.

XSLT is an acronym of eXtensible Stylesheet
Language Transformation. XSLT can transform given XML document to new style.

this is very good way for reading complex XML documents where XML source has week ability to distinguish structure.

let’s do a sample with XSLT in XML Task…

Suppose this XML document ( theSource.xml ) :

<enterprise>
    <person>
        <sourcedid>
            <source>111111</source>
            <id>22222</id>
        </sourcedid>

    </person>
</enterprise>

As you see there are lots of data for each person in the xml document, sometimes we don’t need all of data and we can minimize the structure, sometimes we need to flat data for each person. there are lots of changing style works which we can do with any xml file, just we need to apply an appropriate XSLT on it.

in this sample we want to transform theSource.xml data to this format:

<enterprise>
  <person>
    <displayname>xxxxxxx</displayname>
    <firstname>zzzzzz</firstname>
    <lastname>yyyyy</lastname>
    <pass></pass>
    <email>xxxxxx@fffff.edu</email>
    <customrole>hhhhhh</customrole>
    <customrole>dddddd</customrole>
    <customrole>cccccc</customrole>
  </person>
  <person>
    <displayname>ZZZZZ</displayname>
    <firstname>zzzzzz</firstname>
    <lastname>yyyyy</lastname>
    <pass></pass>
    <email>xxxxxx@fffff.edu</email>
    <customrole>hhhhhh</customrole>
    <customrole>dddddd</customrole>
    <customrole>cccccc</customrole>
  </person>
</enterprise>

 so , we just need displayname, firstname, lastname, pass, email, and number of customroles for each person.

First of all we need an XSLT for this purpose, this is the XSLT we used here:

<?xml version=’1.0′ ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes" version="2.0" encoding="UTF-8" />
      <xsl:template match="/">
            <xsl:element name="enterprise">
        <xsl:for-each select="enterprise/person">
                  <xsl:element name="person">
                        <xsl:element name="displayname">
                              <xsl:value-of select="./name/fn"/>
                        </xsl:element>

            </xsl:for-each>
            </xsl:element>
      </xsl:template>
</xsl:stylesheet>

If you are not familiar with this structure , you need to read more about XSLT first, this is good tutorial on XSLT.

Let’s explain a bit about what this xslt do ,

<xsl:element name="lastname">

this will create an xml element with the name specified ( in this sample : lastname) for the output xml document

<xsl:value-of select="./name/n/family"/>

this will fetch specified data from the input xml document, the value will fetch from hierarchy specified ( in this sample : person->name->n->family )

<xsl:for-each select="enterprise/person">

this is structure of for-each loop in XSLT, this will loop through items in specified location in input xml file ( in this sample : enterprise/person )

That’s it. the above xslt is a simple xslt which just read firstname, lastname, pass , … and other data for each person, this has also a loop through customroles too.

now, let’s finish our work and run a test,

Create a SSIS package, add a XML task in it,double click on xml task and go to XML Task Editor, in the editor set these settings:

OperationType : XSLT

source: theSource.xml

SecondOperandType: direct input

SecondOperand: copy and paste whole xslt document from above sample here

SaveOperationResult: True

Destination: destxml.xml

That’s all. now run the package. you will see that style of data changed simply in destxml.xml file. XSLT is very powerful to do this, Isn’t it? you may use XML task with XSLT in many complex xml files to transform data.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.