Help on REST Connector and XML Data Load

angmarceau1971 — Tue, 28 Dec 2021 20:34:17 GMT

I'm having difficulties parsing my XML which is resulting from a REST Connector Query. I'm needing some help from smarter people than me 🙂

REST Connector is generating this SQL Load statement

*************START****************

RestConnectorMasterTable:
SQL SELECT
"__KEY_Envelope",
(SELECT
(SELECT
(SELECT
(SELECT
FROM "header"),
(SELECT
"__KEY_records",
"__FK_records",
(SELECT
"__KEY_values_u0",
"__FK_values_u0",
(SELECT
"@Content" AS "@Content_u0",
"__FK_data_u0"
FROM "data" FK "__FK_data_u0" ContentFieldAlias "@Content_u0")
FROM "values" PK "__KEY_values_u0" FK "__FK_values_u0")
FROM "records" PK "__KEY_records" FK "__FK_records")
FROM "return" PK "__KEY_return" FK "__FK_return")
FROM "getReportResultResponse" PK "__KEY_getReportResultResponse" FK "__FK_getReportResultResponse")
FROM "Body" PK "__KEY_Body" FK "__FK_Body")
FROM XML "Envelope" PK "__KEY_Envelope";

[records]:
LOAD [__KEY_records],
[__FK_records] AS [__KEY_return]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_records]);

[data_u0]:
LOAD [@Content_u0],
[__FK_data_u0] AS [__KEY_values_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_data_u0]);

[values_u0]:
LOAD [__KEY_values_u0],
[__FK_values_u0] AS [__KEY_records]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_values_u0]);

*************END****************

This is a sample of the XML response I'm getting.

******START XML**********

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
<env:Header/>
<env:Body>
<ns2:getReportResultResponse xmlns:ns2="http://service.com/">
<return>
<header>
<values>
<data>DATALABEL</data>
<data>DATAVALUE</data>
</values>
</header>
<records>
<values>
<data>LABEL1</data>
<data>VALUE-123456789</data>
</values>
</records>
<records>
<values>
<data>LABEL1</data>
<data>VALUE-567891234</data>
</values>
</return>
</ns2:getReportResultResponse>
</env:Body>
</env:Envelope>

******END XML**********

I need a table with two columns. DATALABEL & DATAVALUE. I can't figure it out. Any help would be appreciated.

topic Help on REST Connector and XML Data Load in Connectivity & Data Prep

Help on REST Connector and XML Data Load