XML Task – Changing Style of Data – XSLT

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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>

        <name>
            <fn>xxxxxxx</fn>
        <n>
            <family>yyyyy</family>
            <given>zzzzzz</given>
        </n>
        </name>
        <demographics>
            <gender>2</gender>
        </demographics>
        <email>xxxxxx@fffff.edu</email>
        <adr>
            <street>cccccc</street>
            <locality>ccccc</locality>
            <region>cccc</region>
            <pcode>ccccccc</pcode>
        </adr>
        <academics>
            <academicmajor>gggggg</academicmajor>
            <customrole>hhhhhh</customrole>
            <customrole>dddddd</customrole>
            <customrole>cccccc</customrole>
        </academics>

    </person>

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

        <name>
            <fn>ZZZZZ</fn>
        <n>
            <family>yyyyy</family>
            <given>zzzzzz</given>
        </n>
        </name>
        <demographics>
            <gender>2</gender>
        </demographics>
        <email>xxxxxx@fffff.edu</email>
        <adr>
            <street>cccccc</street>
            <locality>ccccc</locality>
            <region>cccc</region>
            <pcode>ccccccc</pcode>
        </adr>
        <academics>
            <academicmajor>gggggg</academicmajor>
            <customrole>hhhhhh</customrole>
            <customrole>dddddd</customrole>
            <customrole>cccccc</customrole>
        </academics>

    </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:element name="firstname">
                              <xsl:value-of select="./name/n/given"/>
                        </xsl:element>

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

                        <xsl:element name="pass">
                              <xsl:value-of select="./userid/@password"/>
                        </xsl:element>

                        <xsl:element name="email">
                              <xsl:value-of select="./email"/>
                        </xsl:element>
         <xsl:for-each select="./academics/customrole">
             <xsl:element name="customrole">
                              <xsl:value-of select="."/>
                        </xsl:element>
                     </xsl:for-each>
                  </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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply

Your email address will not be published. Required fields are marked *