Loading Complex XML Using SSIS

In my previous article I showed you how the XML Source component can be used to load XML files into a SQL Server database, using fairly simple XML structures.  In this follow-up article I will demonstrate how to tackle the complex XML issue.

The Complex XML Example

You probably know that SSRS reports, RDLs, are actually XML files.  And they’re not the easiest types of XML files around.  To humans they are still readable but the structure can be quite complex.  So there we’ve got our example: an RDL.  More specifically I’ll be using the RDL that’s available for download in one of my earlier articles.

The Goal

Every good example has got a goal.  Our goal today is to retrieve a list of datasets and fields as defined in the RDL.  Shouldn’t be too difficult, right?

Using The XML Source Component

Let’s try to get this done through the XML Source component with which we’re very familiar by now.  You know the drill: drag an XML Source into your Data Flow, open it up and configure the XML and XSD locations.

Note: to be able to do this I cheated a bit by manually manipulating the RDL a little.  More precisely I removed all the namespace references from the <report> tag and further down the XML (removed “rd:”).

With both files configured, let’s have a look at the Columns page:

The XML Source component handling a really complex XML file

Look at that massive list of output flows!  In total I’ve gotten 45 of them, all for free!  Even if you’re up to the task of creating 45 output tables, do you really want to find out how to get these joined together?  To prevent creating that bunch of tables you may consider using the Merge Join component… 45 times in your data flow. Didn’t think so!

Sure, it would run fine if you manage to get it all constructed.  But in my opinion this is just too silly to try out because there’s an interesting alternative.

And that alternative is XSLT – eXtensible Stylesheet Language Transformations.

Using XSLT

With XSLT you describe what you want to retrieve from the XML document and what it should look like.  In this example we’ll be retrieving the list of datasets and their fields, in CSV format.  CSV stands for Comma-Separated Values, although I prefer the term “Character-Separated Values” as the separator is not always a comma.

To be able to write correct XSLT, you need to know what the XML structure looks like.  Here are the first 31 lines of the sample RDL file mentioned earlier.

<?xml version="1.0" encoding="utf-8"?>
  <InitialPageName>A Very Unique Name</InitialPageName>
    <DataSource Name="srcContosoDW">
    <DataSet Name="dsProductList">
        <CommandText>select DPC.ProductCategoryName, DPS.ProductSubcategoryName, DP.ProductName
from dbo.DimProduct DP
inner join dbo.DimProductSubcategory DPS
    on DPS.ProductSubcategoryKey = DP.ProductSubcategoryKey
inner join dbo.DimProductCategory DPC
    on DPC.ProductCategoryKey = DPS.ProductCategoryKey;</CommandText>
        <Field Name="ProductCategoryName">
        <Field Name="ProductSubcategoryName">

As you can see, the main node is called Report.  Nested under Report we’ve got DataSets, which can have several DataSet elements.  Each DataSet has a set of Fields with one or more Field elements.  Using that information we come to the following XSLT.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions">
  <xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>
  <xsl:template match="/">

    <xsl:for-each select="Report/DataSets/DataSet/Fields/Field">
      <xsl:value-of select="../../Query/DataSourceName"/>
      <xsl:value-of select="../../@Name"/>
      <xsl:value-of select="@Name"/>


So, what is the XSLT describing?  On line three, we say that the output should be text in UTF-8 encoding.  The “template match” on the fourth line takes the whole XML document into consideration, hence the forward slash.  Then on line five we start writing output through the xsl:text tag.  This is our header line.  As you can see we’re using the semi-colon as column separator in the CSV output.  Line six adds a CRLF (carriage-return + line feed) to the output.

Then the fun part starts.  If you have experience with XPath, the way XSLT walks through the XML document should look familiar to you.

The xsl:for-each tag loops over all the Fields in all the DataSets in the document.

Using the xsl:value-of tag, we can fetch values out of the XML.  The first value being retrieved is the name of the data source that dataset is using.  (I’ve added the retrieval of the data source to demonstrate how element values are retrieved.)  The path to the DataSourceName element is Report/DataSets/DataSet/Query/ so we use the double-dot syntax to navigate two levels up in the XML tree.  The value of the element itself is retrieved by just using its name, as demonstrated in the XSLT above.

The next value-of tag retrieves the Name attribute of the DataSet, hence the two levels up, and the final value-of fetches the Name attribute of the Field element.

Now that the XSLT is clear for everyone, how do we apply it to our XML document?  Here comes the time for SSIS once more!

Open up the BIDS with the Control Flow of an SSIS package active and throw in an XML Task component.

The XML Task, one of the Control Flow Items in Integration Services

Double-click the component to open up the XML Task Editor.  This is what it looks like by default:

XML Task Editor: default settings

As this is an all-round XML task that can handle several XML-related tasks, the first setting that we need to modify is called OperationType.  That’s not too complicated because it comes with a dropdown and XSLT is one of the possible values.

The different operation types supported by the XML Task

With XSLT selected, the editor transforms into the following:

The XML Task Editor with XSLT as OperationType

Now we need to configure where the task can find our XML file, through the Source property.  Click the Source textbox to make the dropdown appear and select <New File connection…>.

You can create a new File Connection through the XML Task Editor

In the File Connection Manager Editor, leave the Usage type at Existing file and select the RDL.

Next up we’re going to specify where the task can find the XSLT that needs to be applied to the XML.  That can be done through the Second Operand settings.  As SecondOperandType, select File Connection.  Use the dropdown of the SecondOperand property to create a second new file connection that points to your XSLT file.

With that set up as well, only one step remains.  The task still doesn’t know where the output should be saved.  Or that it actually should get saved.  So first switch the SaveOperationResult property to True.  As you can see, DestinationType is already set to File Connection, that’s what we need.  Use the dropdown of the Destination property to create a third new file connection.  This time however, Usage Type should be set to Create File.  Specify path and filename for the output file and click OK to close the File Connection Manager Editor.

This is what our XML Task now looks like in the editor:

The XML Task Editor with all input and output files specified, as expected for our XSLT experiment

As shown above, I’ve called the output file DatasetInfo.csv.

One more property that can be interesting is the OverwriteDestination property.  Setting it to True can ease the testing of your package if you need to execute it multiple times.  Which you’ll probably want when your XSLT is not giving the expected output.  Don’t forget to set it to False afterwards (depending on what behavior you actually expect from your package).

Okay, now close the XML Task Editor and execute the package.  If you haven’t made any mistakes, the task should color green and you should have an extra file on your hard drive somewhere.  Here’s what the content of my DatasetInfo.csv looks like:







Look at that, a list of fields, all part of the dsProductList dataset.

“Hang on, wasn’t this article going to demonstrate how to get complex XML files imported into our database?  And now you’re writing the data to a file?!”

Well yeah, you’re right.  Unfortunately the XML Task does not offer the possibility to write to a table in a database.  So to get the data imported into your database you’ll need to set up a Data Flow that imports the CSV files.  But that shouldn’t be too difficult to achieve, right?

Mission accomplished!


With this article I have shown how Integration Services can be used to retrieve data out of complex XML files, without actually using the XML Source component.  I hope you’ve enjoyed reading it as much as I had while writing.  Or maybe you know another interesting method to get complex XML imported.  Feel free to post comments!

If, after reading and applying the above technique, you are struggling with getting special characters such as é, è or ö and even ô, imported make sure to read my follow-up article on SSIS, Flat Files and Accents.  It also gives some more insight into what the above method actually produces (code page UTF-8 is a hint).

Have fun!



XSLT (Wikipedia)

CSV (Wikipedia)



Tags: , ,

  1. Mickey’s avatar

    Thanks Valentino for sharing this. Really nicely written and explained article. Not being critical, but the only slight downside risk i can see is how can you be sure that your data doesn’t contain a semi-colon, and therefore upset your data import. From that perspective it might be safer to rewrite it as a simpler flat xml file and then to import that xml.


  2. ap’s avatar

    Hi Valentino,
    I’m trying to parse a similar xml with smilar layout, but i keep getting this error – [XML Task] Error: An error occurred with the following error message: “The ‘xsl:value-of’ start tag on line 18 does not match the end tag of ‘xsl:for-each’. Line 36, position 22.”. I’m not sure what this means. I thought I was following the proper levels. can you advise?


  3. Abhishek Tripathi’s avatar

    Hey Valentino,
    Can you please give an example where source is a XML column in a SQL table?



  4. Joshua Pierce’s avatar

    @Abhishek – If you look at the image right underneath

    “With XSLT selected, the editor transforms into the following:”

    There is a parameter SourceType which needs to be set to variable, then choose the variable name that you load your xml into.

    Typically, you will use a Execute SQL task to return a full result set into an object variable, then you will use a foreach sequence container of type Foreach ADO Enumerator and loop over your result set reading the xml into a variable, and then performing this function on that variable.

    This should get you going,

    Good luck


  5. Anil Kumar’s avatar

    I tried with the same xml and xslt file, but added the “xmlns” attribute and the package is not working. If I remove “xmlns” then working fine, but unfortunately my xml files have this attribute. Anybody can help me to convert this file. Thanks in advance.

    A Very Unique Name


    select DPC.ProductCategoryName, DPS.ProductSubcategoryName, DP.ProductName
    from dbo.DimProduct DP
    inner join dbo.DimProductSubcategory DPS
    on DPS.ProductSubcategoryKey = DP.ProductSubcategoryKey
    inner join dbo.DimProductCategory DPC
    on DPC.ProductCategoryKey = DPS.ProductCategoryKey;




    1. Valentino Vranken’s avatar

      Hi Anil,

      For your namespace issue have a look at the following post: http://blog.hoegaerden.be/2012/06/11/removing-namespaces-through-xslt/



  6. Sri Ram R’s avatar

    I want to split this element 111112:0045 into two elements like ArrivalDate and ArrivalTime using XSLt.
    Can any one help me?


    1. Valentino Vranken’s avatar

      That should be possible through the tokenize function. There’s some good info on that method here: http://stackoverflow.com/questions/584082/xslt-best-way-to-split-and-render-comma-separated-text-as-html


  7. Ann’s avatar

    This is not in sql server 2005 right?


    1. Valentino Vranken’s avatar

      What I’ve used is SQL Server 2008 R2, but the same functionality should exist in 2005.


  8. Bharat Ram’s avatar

    I was creating an SSIS project for the first time for the purpose of migrating data from an Xml to an SQL table and I did that by referring to your post on http://sqlserverpedia.com/blog/sql-server-bloggers/loading-xml-using-ssis/
    It was really great, simply put and so I understood easily.
    Thank you very much.

    - Bharat


  9. Raghu’s avatar

    I have an xml file similar to the one in your previous article. I need to do some operations only if data coming in one particular xml tag (Say I need to some operation only if data under ‘Book’ is coming) otherwise I need to skip that operation. Is there a way to acheive this task.

    Thanks in Advance,


    1. Valentino Vranken’s avatar

      Hi Raghu,

      Using the Conditional Split transformation you should be able to split your data flow up into “those with Book data” and “those without Book data”. Add any transformation you need for your Book-related operation. Finally use the Union All transformation to add the split-up data flow back into one pipe.

      Reference material:
      Conditional Split – http://technet.microsoft.com/en-us/library/ms137886.aspx
      Union All – http://technet.microsoft.com/en-us/library/ms141020.aspx

      Good luck,


      1. Raghu’s avatar

        Thanks Valentino, I achived this using a variable. Will try your suggestion also to compare performance and go with the better performing way.



  10. Mau’s avatar

    Hi Valentino

    I just cant believe mi good luck finding this !

    for like a hundred rdls i have to map CommandType, CommandText and Code ! ! ! it has to be finished for tomorrow, thank you so much !


  11. Ajmal’s avatar

    Hi Valentino!

    How would you use XML Task with a foreach file container? I have hundreds of Complex XML Files that I want to import into a SQL table using SSIS. I am able to replicate the above solution for 1 XML file but not for multiple XML in a particular folder. I have already tried it using Foreach file container with XML Source and it works. But as my XML is really complex , i think XML Task is a better way to go. Any suggestion would be really appreciated. Thanks!


    1. Sachin’s avatar

      Hi, I got stuck with my xml. I am not able to store it in sql server table. Xml is having complex structure as shown in the example. Can you let me know the steps to import it into database table please?


  12. Ajmak’s avatar

    Never mind. I got it working.


  13. CyanideLancer’s avatar

    Hi, I have an XMl file at this location


    Can you help me to flatten it out with required columns to be loaded into database with the query?


    Im completely new to XML query hence, need your extensive help in achieving this! Thanks in advance :) 


    1. CyanideLancer’s avatar

      Or if only XSLT is provided, it would be great


    2. Valentino Vranken’s avatar

      As you’ve discovered I don’t really have the time to answer a question as extensive as this one, sorry for that. Freelancers would even call this a project on its own… I advise you to seek some help on writing XSLT through some forum. Feel free to post a link to it here if you want me to have a look at it as well.


  14. sqldaddy’s avatar

    I have gone through your post and got relevant and the way you have described Loading Complex XML Using SSIS. This will be helpful. I have also a package of same task where all the things is discussed in wll proper way with a relevant video. There are various features of this package that I am going to mention here.
    1.Read XML data from any SOAP/REST API Web Service using methods such as GET/POST.
    2.Read XML data from single or multiple XML files (use of wildcard allowed. e.g. c:\data\*.xml)
    3.Support for Path expression to extract data from any level (e.g. Extract Orders nested under Customer Node).
    4.Support for passing custom headers to SOAP or REST Web service
    5.Support for looping through multiple files using wildcard pattern (e.g. *.xml).
    6.REST API Paging support to loop through multiple requests (see)
    7.Support for SQL Server 2005, 2008, 2012, 2014 (32 bit and 64 bit)

    If you need more help.


  15. niki’s avatar

    Is there a way to read XML file, replace special characters like & before loading it into database?


  16. Bob’s avatar

    I have a xml file as below. I tried to parse it using XSLT (Extensible Stylesheet Language Transformations). However the syntax may be incorrect as I can not retrieve any values out of xml file. please refer below for my xml file and xslt template I wrote. thanks for your help.
    ——-below is xml file—————————————————————————————–




    LocalHost ers_s_land





    –Below is my xslt template———————————————————————————



  17. Bob’s avatar

    wow, my previous reply has been cut off. I will post my question in a forum and then come back here, thanks in advance.


  18. halloween’s avatar

    Very rapidly this site will be famous among all blog viewers,
    due to it’s pleasant posts


  19. Alex’s avatar

    I couldn’t find the XML you mentioned to test with your example, cause I am trying with my own XML and I couldn’t made it work. So I want to try with your to see what I am doing wrong. Please if possible please share your XML and XSLT with me by email.

    Thanks and regards,


  20. John’s avatar

    Hi Valentino,

    Really good example, I had the same situation and your approach worked like a charm.

    You need to understand your XML’s schema in order to apply this method, that’s all.

    Than you, my good Sir!

    All the best,


  21. Elephant fish’s avatar

    I am really delighted to glance at this website posts which includes lots of helpful data, thanks for providing such information.


  22. hawaii’s avatar

    What a data of un-ambiguity and preserveness of precious knowledge concerning unpredicted emotions.


  23. birthday wishes card’s avatar

    This paragraph will assist the internet viewers for creating new
    webpage or even a weblog from start to end.


  24. ban lai 3 pn can ho richstar tan phu’s avatar

    If some one wants expert view on the topic of blogging afterward i propose him/her
    to go to see this web site, Keep up the pleasant job.


  25. kansas’s avatar

    Excellent blog! Do you have any suggestions for aspiring
    writers? I’m planning to start my own blog soon but I’m
    a little lost on everything. Would you advise starting with a free platform like WordPress or go for a paid option?
    There are so many options out there that I’m totally confused ..
    Any recommendations? Appreciate it!


  26. nevada it's where my story begins’s avatar

    I am really enjoying the theme/design of your blog. Do you ever run into any
    internet browser compatibility problems? A number of my blog audience have complained about
    my site not operating correctly in Explorer but looks great
    in Safari. Do you have any tips to help fix this problem?


  27. baby name’s avatar

    Hi there, every time i used to check webpage posts here in the early hours in the dawn, since
    i love to find out more and more.


  28. indiana’s avatar

    What i do not understood is in truth how you are not actually much more neatly-favored than you might be right now.
    You’re so intelligent. You understand therefore considerably in relation to this topic, produced
    me in my opinion imagine it from so many varied angles.
    Its like men and women aren’t interested unless it’s something to do
    with Woman gaga! Your individual stuffs outstanding.
    Always deal with it up!


  29. hawaii’s avatar

    My brother suggested I may like this web site. He used to be totally right.
    This publish actually made my day. You can not consider just how a
    lot time I had spent for this info! Thank you!


© 2008-2019 BI: Beer Intelligence? All Rights Reserved