Hallo KM,

ich habe mir die Emarsys API angeschaut und es gibt auch die Möglichkeit JSON Daten abzufragen,
https://help.emarsys.com/hc/en-us/ar...mail-Campaigns
das sieht dann so aus

ich habe es zunächst mit einer SQL Variable getestet

Code:
-- SQL-Variable erstellen
create or replace variable tsto.json_var varchar(10000) ccsid 1208;

values(tsto.json_var);

-- Variable mit JSON-Daten laden
set tsto.json_var =
'{
    "replyCode": 0,
    "replyText": "OK",
    "data": [
        {
            "id": "12345",
            "language": "en",
            "created": "2011-08-12 18:12:23",
            "deleted": "",
            "fromemail_host": "emarsys",
            "name": "be_afraid_email",
            "status": "3",
            "api_status": "2",
            "api_error": "0",
            "fromemail": "malekith@example.com",
            "fromname": "Malekith",
            "subject": "convergence",
            "email_category": "111111111",
            "filter": "222222222",
            "contactlist": "0",
            "template": "333333333",
            "cc_list": 0,
            "source": "userlist",
            "html_source": "Hello $First Name$...",
            "text_source": "Hello $First Name$..."
        },
        {
            "id": "67890",
            "root_campaign_id": "67890",
            "language": "en",
            "created": "2011-08-12 18:20:23",
            "deleted": "2011-11-07 08:11:57",
            "fromemail_host": "emarsys",
            "name": "angry_email",
            "status": "3",
            "api_status": "2",
            "api_error": "0",
            "fromemail": "bruce.banner@example.com",
            "fromname": "Hulk",
            "subject": "A new business",
            "email_category": "111111111",
            "filter": "222222222",
            "contactlist": "0",
            "template": "0",
            "cc_list": 564365356,
            "source": "api",
            "html_source": "Hello $First Name$...",
            "text_source": "Hello $First Name$...",
            "version_name": "A version"
        }
    ]
}';

-- JSON_TABLE aus Variable mit JSON-Daten laden

Select x.*
  from JSON_TABLE(
     tsto.json_var, 
     '$'
     Columns(
        nested '$.data[*]' columns(
            "name"          varchar(50)             path 'lax $.name',
            "Status"        varchar(01)             path 'lax $.status',
            "Contactlist"   varchar(50)             path 'lax $.contactlist',
            "Subject"       varchar(50) ccsid 1208  path 'lax $.subject'    
        )
    )
  ) as x;
JSON Parsing mit HTTPGETCLOB sieht dann so aus
HTML-Code:
Select x.*
  from JSON_TABLE(
     SYSTOOLS.HTTPGETCLOB('https://api.emarsys.net/api/v2/email/?fromdate=2013-01-01&todate=2013-02-01',''),
     '$'
     Columns(
        nested '$.data[*]' columns(
            "name"          varchar(50)             path 'lax $.name',
            "Status"        varchar(01)             path 'lax $.status',
            "Contactlist"   varchar(50)             path 'lax $.contactlist',
            "Subject"       varchar(50) ccsid 1208  path 'lax $.subject'    
        )
    )
  ) as x;

Viele Grüße
Rainer