Querydef

Parameters

queryDef object Query definition. Based on the queryDef language

Returns

DOMElement

Query result. As an DOM object.

Examples

xtk:queryDef is Adobe Campaign’s query builder: it compiles a declarative query schema, select, where, orderBy — into SQL and returns the result as XML. Create one with NLWS.xtkQueryDef.create(...), then call ExecuteQuery().

Pick the operation:

operation: "select" for a list, iterated with getElements().
operation: "get" for a single record (SQL LIMIT 1), throws if none; use "getIfExists" when it may be absent.
operation: "count" for a total via Count.

Find samples below on the xtk:option table for quick testing. Extend with nms:recipient or nms:delivery when you’re ready to scale.

Prefer JSON over XML for compatibility with formatter & linter. See the modern best practices article.

Select records

var query = NLWS.xtkQueryDef.create({
  queryDef: {
    schema: "xtk:option",
    operation: "select",
    select: { node: [{ expr: "@name" }, { expr: "@stringValue" }] },
  },
});
var res = query.ExecuteQuery(); // DOMElement
logInfo(res.toXMLString()); // <option-collection><option name="NmsServer_URL" stringValue="https://instance.com"/></option-collection>
res.getElements().forEach(function (option) {
  logInfo(option.$stringValue); // "https://instance.com"
});

Get 1 record

var query = NLWS.xtkQueryDef.create({
  queryDef: {
    schema: "xtk:option",
    operation: "get",
    select: { node: [{ expr: "@stringValue" }] },
    where: { condition: [{ expr: "@name = 'NmsEmail_DefaultFromAddr'" }] },
  },
});
var res = query.ExecuteQuery(); // DOMElement
logInfo(res.toXMLString()); // <option stringValue="[email protected]"/>
logInfo(res.$stringValue); // "[email protected]"

Count records

var query = NLWS.xtkQueryDef.create({
  queryDef: {
    schema: "xtk:option",
    operation: "count",
  },
});
var res = query.ExecuteQuery(); // DOMElement
logInfo(res.toXMLString()); // <option count="150"/>
logInfo(res.$count); // 150

Group records by, i.e. “Distribution of values”

var query = NLWS.xtkQueryDef.create({
  queryDef: {
    schema: "xtk:option",
    operation: "select",
    select: {
      node: [
        { expr: "Count()", alias: "@count" },
        { expr: "@dataType", groupBy: true },
      ],
    },
    orderBy: { node: [{ expr: "@dataType", sortDesc: true }] },
  },
});
var res = query.ExecuteQuery(); // DOMElement
logInfo(res.toXMLString()); // <option-collection><option count="100" dataType="3"/><option count="50" dataType="5"/></option-collection>
logInfo(res.getElements().length); // 2

Note: when executing “Distribution of Values”, the Client Console performs 2 queries: 1 “count” query to get the total row count, and 1 “group by” query to ge the distribution. It thens compute the percentage by dividing the group by counts for each row.

Select all fields

Call SelectAll before ExecuteQuery to query all fields from the schema. Useful for discovery and for tables with many fields (xtk:workflow, nms:delivery…).

SelectAll(duplicate: bool): xtkQueryDef

var query = NLWS.xtkQueryDef.create({
  queryDef: {
    schema: "xtk:option",
    operation: "get",
    // select: { }, // not needed for SelectAll
    where: { condition: [{ expr: "@name = 'NmsEmail_DefaultFromAddr'" }] },
  },
});
query.SelectAll(false); // select all fields of the schema
var res = query.ExecuteQuery(); // DOMElement
logInfo(res.toXMLString()); // <option stringValue="[email protected]" created=... lastModified=... longValue=.../>
logInfo(res.$stringValue); // "[email protected]"

SelectAll perfoms a SOAP call to the instance, so calling it with ExecuteQuery actually performs 2 API calls.

Discover all schemas

// get all schemas
var query = NLWS.xtkQueryDef.create({
  queryDef: {
    schema: "xtk:srcSchema",
    operation: "select",
    select: { node: [{ expr: "@name" }, { expr: "@namespace" }] },
  },
});
var res = query.ExecuteQuery(); // DOMElement
logInfo(res.toXMLString()); // <srcSchema-collection><srcSchema namespace="nms" name="delivery"/>...
logInfo("Found " + res.getElements().length + " schemas"); // 333
// for each schema, get all fields
res.getElements().forEach(function (schema) {
  var schemaId = schema.$namespace + ":" + schema.$name;
  logInfo("Discovering schemaId " + schemaId);
  var query = NLWS.xtkQueryDef.create({
    queryDef: { schema: schemaId, operation: "select" },
  });
  // wrap in try/catch to avoid errors for schemas that cannot be queried
  try {
    query.SelectAll(false); // select all fields of the schema
    var res = query.ExecuteQuery(); // DOMElement
    logInfo(res.toXMLString());
  } catch (e) {
    logInfo("Error occurred while discovering schemaId " + schemaId + ": " + e);
  }
});

Reference

{
  operation: "select|get|count", // Query type
  schema: "namespace:name", // FROM clause
  lineCount: 60, // LIMIT clause
  startLine: 20, // OFFSET clause
  select: {
    node: [
      {
        expr: "", // XPath expression
        alias: "", // alias for the XML output
        groupBy: "true|false" // Group by this expression
      },
    ]
  },
  where: { // WHERE clause
    condition: [
      {
        expr: "",
        xpath: "",
        internalId: "", // auto-generated via default="Gid()"
        boolOperator: "AND|OR", // enum xtk:queryDef:boolOperator
        setOperator: "EXISTS|NOT EXISTS|COUNT|SUM|AVG|MIN|MAX|IN|NOT IN", // enum xtk:queryDef:setOperator
        ignore: true | false, // Ignore condition
        compositeKey: "",
        dependkey: "",
        enabledIf: "", // Enabling script
        noSqlBind: true | false, // No SQL binding on expression constants
        sql: "", // Native SQL expression
        aliasSqlTable: "", // Table Alias in the FROM clause
        "filter-name": "", // Name of the predefined filter
        filterLabel: "" // Predefined filter label
      }
    ]
  },
  orderBy: { // ORDER BY clause
    node: [
        { 
          expr: "@name", // XPath
          sortDesc: true | false // DESC
        }
    ]
  }
}

Going further

Prefer queryDef over raw 📕 sqlSelect: it stays within the data schema and is engine-independent.

For more patterns — XML/E4X syntax, raw-data workflows, value distribution, building a where step by step — see the queryDef tutorial.

Unit tests

Tested with acc by running acc instance exec -f test/integration/queryDef.spec.js in a sandbox:

loadLibrary("/nl/core/shared/nl.js"); // import NL.assert
// globals
context.test1 = NL.assert(typeof NLWS.xtkQueryDef === "object");
context.test2 = NL.assert(typeof NLWS.xtkQueryDef.create === "function");
// instantiate
const query = NLWS.xtkQueryDef.create({
  queryDef: {
    schema: "xtk:option",
    operation: "get",
    select: { node: [{ expr: "@stringValue" }] },
    where: { condition: [{ expr: "@name = 'NmsEmail_DefaultFromAddr'" }] },
  },
});
const result = query.ExecuteQuery();
// test result
context.test3 = NL.assert(typeof result === "object");
context.test4 = NL.assert(typeof result.tagName === "string");
context.test5 = NL.assert(result.tagName === "option");
context.test6 = NL.assert(NL.isXML(result) === false);
context.test7 = NL.assert(typeof result.toXMLString === "function");
context.test8 = NL.assert(result.toXMLString().indexOf('stringValue="') !== -1);
context.test9 = NL.assert(typeof result.$stringValue === "string");
context.test10 = NL.assert(result.$stringValue.toString().indexOf("@") !== -1);

queryDef - AC JS Reference