Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.