Using sqlSelect in Adobe Campaign Classic
The sqlSelect is not really a straightforward JSAPI function, at least not from the documentation we were provided. Let me give you a working examples that you can use to set up your future sqlSelect queries.
The function executes a raw SQL query on the Adobe Campaign database and returns the results as an XML object.
sqlSelect (format, query [, dataSource ])Let’s look at the example from documentation.
var xmlRes = sqlSelect("name:string,file:string:64","select relname, relfilenode from pg_class WHERE relkind='r' AND pg_get_userbyid(relowner)<>'postgres';");This will return a number, not an XML object.
08/27/2025 6:57:32 PM js2 6.9424382240254e-310What is the problem here? It’s crucial to add a root or collection element in front of the format argument, under which all the rows will be grouped in the XML result. In our case we have added data.
var xmlRes = sqlSelect("data,name:string,file:string:64","select relname, relfilenode from pg_class WHERE relkind='r' AND pg_get_userbyid(relowner)<>'postgres';");Now with that the query returns XML object with data.
08/27/2025 6:58:21 PM js2 <select> <data> <name>xtkaudit</name> <file>16466</file> </data> <data> <name>xtkconflict</name> <file>16480</file> </data> <data> <name>xtkcounter</name> <file>16491</file> </data> <data> <name>xtkdashboard</namWe have data returned and can finally traverse them!
There are two ways how to traverse XML object in Adobe Campaign Classic.
- We can use Adobe Campaign’s function for each
- We can directly traverse XML results like an array with for (;;;)
Using Adobe Campaign’s for each loop
This is Adobe Campaign’s built in function to loop over XML objects. You could find examples of the function in the queryDef documentation.
var xmlRes = sqlSelect("data,name:string,file:string:64","select relname, relfilenode from pg_class WHERE relkind='r' AND pg_get_userbyid(relowner)<>'postgres';");
for each (var item in xmlRes.data)
logInfo("Name: " + item.name);
Using for loop
Using conventional for (;;;) to get same results
var xmlRes = sqlSelect("data,name:string,file:string:64","select relname, relfilenode from pg_class WHERE relkind='r' AND pg_get_userbyid(relowner)<>'postgres';");
for (var i = 0; i < xmlRes.data.length();i++){
logInfo(xmlRes.data[i].name);
}
