2 Replies Latest reply on Jan 9, 2018 12:24 AM by Vladislav Ponomarenko

    Lookup on XML Soucre table

    john S Active Member
    john S Jan 5, 2018 5:07 AM

      I have an XML source file with Child and Parent details , I loaded the into table is as below :

       

      Child Number     Child Name         Entity      Parent Number

      C1                ABC                100            P1

      P1                DEF                50            B1

      B1                GHI                20            Q1

       

      I added one more column as "Parent Name" inorder to get name of the parent and the values should be stored as below :

       

      Child Number     Child Name         Entity      Parent Number     Parent Name

      C1                ABC                100            P1                DEF

      P1                DEF                50            B1                GHI

      B1                GHI                20            Q1               

       

      I need your help on how to get the Parent Name from the source table. (FYI, Source is an XML)

      • 767 Views
      • Categories: Administration, Configuration, Connectors, Installation, Performance, Product Feature, Upgrade
      • Tags:
        • 1. Re: Lookup on XML Soucre table
          Nico Heinze New Member
          Nico Heinze Jan 5, 2018 6:39 AM (in response to john S)

          For an "easy" solution you should first stage the data in some DB table as they are. Leave the PARENT column empty for now.

          Take the Loop Transformations (Preliminary Version) from the Marketplace; one of the sample cases in this package shows exactly what you need to do here, namely a recursive lookup.

           

          Beware that the Loop Transformations as they are published on the Marketplace only work on Windows. If you need them for Unix/Linux, let me know, then I'll try to rebuild them next week; the necessary change isn't really big, but I have been drowning in work lately, so I simply didn't have the time during the past weeks to implement this (fairly small) change yet.

           

          If you don't want to use the Loop Transformations, you can take a look at the following Velocity article:

          https://velocity.informatica.com/index.php/best-practices-all/149-development-techniques/639-guidelines-sql-overrides

          It's very lengthy, no discussion, but the last use case shows exactly how you can perform a hierarchical lookup in PowerCenter without the Loop Transformations.

           

          Regards,

          Nico

          • 2. Re: Lookup on XML Soucre table
            Vladislav Ponomarenko Guru
            Vladislav Ponomarenko Jan 9, 2018 12:24 AM (in response to Nico Heinze)

            Hi,

             

            You can try to use Pipeline Lookups .

            Put XML Source to mapping with it's source qualifier SQ_XMLFile.

            Set lookup Source Type property to "Pipeline" (or "Source Qualifier", I don't remember exactly) and next choose your SQ_XMLFile in proper field.

             

            Look Pipeline Lookups in Help for more details.

             

            Regards, Vlad