Loading REST API data

Thu, 21 Jul 2016 00:31:19 GMT

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

"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;

topic Loading REST API data in App Development

Loading REST API data