1 Reply Latest reply: May 9, 2016 9:30 PM by Tim Rosser RSS

    How to restructure REST API data

    Tim Rosser

      I'm using the REST API connector in sense but for each row the data has the format:


      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.









                          "attr:val" AS "val",



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



      LOAD    [val] AS [val],

          [@Content] AS [@Content],

          [__FK_FL] AS [__KEY_row]

      RESIDENT RestConnectorMasterTable

      WHERE NOT IsNull([__FK_FL]);


      DROP TABLE RestConnectorMasterTable;