0 Replies Latest reply: Jul 20, 2016 8:31 PM by Bob Fariss RSS

    Loading REST API data

    Bob Fariss

      I have a REST API XML Response which has levels as follows:

           Member (memberID)

                     Personal (First Name, Last Name)

                     Agreement (AgreementNumber, PaymentPlan)

       

      My goal is to Load a single table with one row for each member that looks like:

      memberID

      First Name

      Last Name

      AgreementNumber

      PaymentPlan

       

      The Data Load Editor produces code that creates 3 separate tables, one for each XML level. The generated code follows. Can anyone suggest how to modify this so that a single table is created instead?

       

      Thanks!

       

      --------------------------------------------------------------------------------------------

       

      RestConnectorMasterTable:

      SQL SELECT

        "__KEY_restResponse",

        (SELECT

        FROM "status"),

        (SELECT

        FROM "request"),

        (SELECT

        (SELECT

        "memberId",

        "__FK_member",

        "__KEY_member",

        (SELECT

        "firstName",

        "lastName",

        "__FK_personal"

        FROM "personal" FK "__FK_personal"),

        (SELECT

        "agreementNumber",

        "paymentPlan",

        "__FK_agreement"

        FROM "agreement" FK "__FK_agreement")

        FROM "member" PK "__KEY_member" FK "__FK_member")

        FROM "members" PK "__KEY_members" FK "__FK_members")

      FROM XML "restResponse" PK "__KEY_restResponse";

       

       

      [personal]:

      LOAD [firstName] AS [firstName],

        [lastName] AS [lastName]

      RESIDENT RestConnectorMasterTable

      WHERE NOT IsNull([__FK_personal]);

       

       

       

       

      [agreement]:

      LOAD [agreementNumber] AS [agreementNumber],

        [paymentPlan] AS [paymentPlan]

      RESIDENT RestConnectorMasterTable

      WHERE NOT IsNull([__FK_agreement]);

       

       

       

       

      [member]:

      LOAD [memberId] AS [memberId]

      RESIDENT RestConnectorMasterTable

      WHERE NOT IsNull([__FK_member]);

       

       

       

       

      DROP TABLE RestConnectorMasterTable;