Post reply

Using XML Source to read XML file (funnily enough)

  • January 27, 2011 at 5:18 am

    #146859

    (Not sure if this should be a new topic in SQL20008 - Integration Services as it's not necessarily a Data Warehouse issue)

    Anyway, I can read the XML data with multiple Source outputs into multiple Merge Join Transformations, my issue is with a three-level piece of complex XML data.

    The 'grandparent' level has a grandparent_id (SortKey position 1), the 'parent' level has a grandparent_id (SortKey position 1) and a parent_id (SortKey position 2), the 'child' level has a Parent_id (SortKey position 1).

    When I try to Merge Join the Parent and Child outputs, I can't join the two Parent_Id values as they are different SortKey values. I also cannot change the SortKey value on parent_id at the child level to 2 as it has to be monotically sequential. (Would it be helpful if SQL had generated a grandparent_id at the child level to maintain continuity?)

    I have seen options in various discussions regarding cleaning up the data before the Merge Join but I have no knowledge of XSL & XSLT and time isn't on my side. I feel I am missing something easy so thought I would post on here while I go away and investigate other avenues.

    Any help would be appreciated, I feel I am missing something blindingly simple and BOL does not have much information on this transformation or the automatically generated _id columns. :crazy:

  • February 11, 2011 at 11:35 am

    #1285849

    I faced with a similar issue few weeks back. You need to use SORT for the child output and sort it by the Parent_Id. Then use MERGE JOIN to merge the output from the Parent output and the sort output.

  • February 14, 2011 at 3:17 am

    #1286354

    Thanks but I was trying to avoid SORT tasks for obvious reasons.

    I have actually managed to get it bottomed out. In the end I used an XSLT transformation to 'flatten' the XML which took away most of the problems. This then only left the issue of using variables in the XSLT Task, now that WAS a whole new ballgame. So glad of all the updates on this (and other) forums and the multitude of blogs that eventually got me to where I needed to be.

    Thanks for taking the time to reply anyway.:-)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply