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:

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