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