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;
