I'm using the REST API connector in sense but for each row the data has the format:
<Row#><fieldname><value><fieldname><value><fieldname><value><fieldname><value><fieldname><value>
The XML (or JSON) script imports this into three columns, record number, one with the repeated field names and one with the actual values. (See screen shot of a subset of the data attached).
How can I restructure this into a normal table?
Note, by default null fields are excluded so each row may have a different set of fields but I can send a parameter to pull out null values of a record if that helps.
Code inserted for reference below.
RestConnectorMasterTable:
SQL SELECT
"__KEY_response",
(SELECT
(SELECT
(SELECT
(SELECT
"attr:val" AS "val",
"@Content",
"__FK_FL"
FROM "FL" FK "__FK_FL" ContentFieldAlias "@Content")
FROM "row" PK "__KEY_row" FK "__FK_row")
FROM "Opportunities" PK "__KEY_Opportunities" FK "__FK_Opportunities")
FROM "result" PK "__KEY_result" FK "__FK_result")
FROM XML "response" PK "__KEY_response" ;
[FL]:
LOAD [val] AS [val],
[@Content] AS [@Content],
[__FK_FL] AS [__KEY_row]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_FL]);
DROP TABLE RestConnectorMasterTable;
