Data oriented APIs data-oriented-apis

Data oriented APIs let you address the entire datamodel.

Overview of the datamodel overview-of-the-datamodel

Adobe Campaign does not offer a dedicated read API per entity (no getRecipient or getDelivery function, etc.). Use the QUERY & WRITER data read and modification methods to access the data of the model.

Adobe Campaign lets you manage collections: queries enable you to recover a set of information collected throughout the base. Unlike access in SQL mode, Adobe Campaign APIs return an XML tree instead of data columns. Adobe Campaign thus creates composite documents with all the collected data.

This operating mode does not offer one-to-one mapping between the attributes and elements of the XML documents and the columns of the tables in the database.

XML documents are stored in MEMO type fields of the database.

Description of the model description-of-the-model

You must be familiar with the Adobe Campaign data model to be able to address the fields of the database in your scripts.

For a presentation of the data model, refer to the Adobe Campaign Data model description.

Query and Writer query-and-writer

The following introduction schema details low level exchanges for reading (ExecuteQuery) and writing (Writer) between database and customer (web pages or Adobe Campaign client console).

ExecuteQuery executequery

For columns and conditions, you can use Queries.

This lets you isolate the underlying SQL. The query language does not depend on the underlying engine: some functions will be re-mapped, which may generate several SELECT SQL orders.

For more on this, refer to Example on the ‘ExecuteQuery’ method of schema ‘xtk:queryDef’.

The ExecuteQuery method is presented in ExecuteQuery (xtk:queryDef).

Write write

Write commands let you write simple or complex documents, with entries in one or more tables of the base.

Transactional APIs let you manage reconciliations via the updateOrInsert command: one command lets you create or update data. You can also configure modification merging (merge): this operating mode lets you authorize partial updates.

The XML structure offers a logical view of the data and lets you sidestep the physical structure of the SQL table.

The Write method is presented in Write / WriteCollection (xtk:session).

ExecuteQuery (xtk:queryDef) executequery--xtk-querydef-

This method lets you perform queries from data associated with a schema. It takes an authentication string (must be logged in) and an XML document describing the query to be submitted as parameters. The return parameter is an XML document containing the result of the query in the format of the schema to which the query refers.

Definition of the “ExecuteQuery” method in the “xtk:queryDef” schema:

<method name="ExecuteQuery" const="true">
  <parameters>
    <param desc="Output XML document" name="output" type="DOMDocument" inout="out"/>
  </parameters>
</method>

Format of the XML document of the input query format-of-the-xml-document-of-the-input-query

The structure of the XML document of the query is described in the "xtk:queryDef " schema. This document describes the clauses of a SQL query: “select”, “where”, “order by”, “group by”, “having”.

<queryDef schema="schema_key" operation="operation_type">
  <select>
    <node expr="expression1">
    <node expr="expression2">
    ...
  </select>
  <where>
    <condition expr="expression1"/>
    <condition expr="expression2"/>
    ...
  </where>
  <orderBy>
    <node expr="expression1">
    <node expr="expression2">
    ...
  </orderBy>
  <groupBy>
    <node expr="expression1">
    <node expr="expression2">
    ...
  </groupBy>
  <having>
    <condition expr="expression1"/>
    <condition expr="expression2"/>
    ...
  </having>
</queryDef>

A sub-query ( <subquery> ) can be defined in a <condition> element. The syntax for a <subquery> element is based on the syntax of a <querydef>.

Example of a <subquery> : </subquery>

<condition setOperator="NOT IN" expr="@id" enabledIf="$(/ignored/@ownerType)=1">
  <subQuery schema="xtk:operatorGroup">
     <select>
       <node expr="[@operator-id]" />
     </select>
     <where>
       <condition expr="[@group-id]=$long(../@owner-id)"/>
     </where>
   </subQuery>
</condition>

A query must reference a start schema from the schema attribute.

The type of operation desired is entered in the operation attribute and contains one of the following values:

The XPath syntax is used to locate data based on the input schema. For further information about XPaths, refer to Data schemas.

Example with the ‘get’ operation example-with-the--get--operation

Retrieves the last name and first name of a recipient (“nms:recipient” schema) with a filter on the email.

<queryDef schema="nms:recipient" operation="get">
  <!-- fields to retrieve -->
  <select>
    <node expr="@firstName"/>
    <node expr="@lastName"/>
  </select>

  <!-- condition on email -->
  <where>
    <condition expr="@email= 'john.doe@aol.com'"/>
  </where>
</queryDef>

Example with the ‘select’ operation example-with-the--select--operation

Returns the list of recipients filtered on a folder and the email domain with a sort in descending order on date of birth.

<queryDef schema="nms:recipient" operation="select">
  <select>
    <node expr="@email"/>
    <!-- builds a string with the concatenation of the last name and first name separated by a dash -->
    <node expr="@lastName+'-'+@firstName"/>
    <!-- get year of birth date -->
    <node expr="Year(@birthDate)"/>
  </select>

  <where>
     <condition expr="[@folder-id] = 1234 and @domain like 'Adobe%'"/>
  </where>

  <!-- order by birth date -->
  <orderBy>
    <node expr="@birthDate" sortDesc="true"/> <!-- by default sortDesc="false" -->
  </orderBy>
</queryDef>

Expressions can be simple fields or complex expressions such as arithmetic operations or the concatenation of strings.

To limit the number of records to be returned, add the lineCount attribute to the <querydef> element.

To limit the number of records returned by the query to 100:

<queryDef schema="nms:recipient" operation="select" lineCount="100">
...

To retrieve the next 100 records, run the same query again, adding the startLine attribute.

<queryDef schema="nms:recipient" operation="select" lineCount="100" startLine="100">
...

Example with the ‘count’ operation example-with-the--count--operation

To count the number of records on a query:

<queryDef schema="nms:recipient" operation="count"">
  <!-- condition on the folder and domain of the email -->
  <where>
    <condition expr="[@folder-id] = 1234" and @domain like 'Adobe%'"/>
  </where>
</queryDef>

Data grouping data-grouping

To retrieve email addresses referenced more than once:

<queryDef schema="nms:recipient" operation="select">
  <select>
    <node expr="@email"/>
    <node expr="count(@email)"/>
  </select>

  <!-- email grouping clause -->
  <groupby>
    <node expr="@email"/>
  </groupby>

  <!-- grouping condition -->
  <having>
    <condition expr="count(@email) > 1"/>
  </having>

</queryDef>

The query can be simplified by adding the groupBy attribute directly to the field to be grouped:

<select>
  <node expr="@email" groupBy="true"/>
</select>

Bracketing in conditions bracketing-in-conditions

Here are two examples of bracketing on the same condition.

It is possible to replace the ‘OR’ operator with the ‘IN’ operation when several conditions apply to the same field:

<where>
  <condition>
    <condition expr="@age IN (15, 45)"/>
    <condition expr="@city IN ('Newton', 'Culver City')"/>
  </condition>
</where>

This syntax simplifies the query when more than two data are used in the condition.

Examples on links examples-on-links

Binding the parameters of the ‘where’ and ‘select’ clause binding-the-parameters-of-the--where--and--select--clause

The binding of parameters lets the engine set the values of the parameters used in the query. This is very useful, since the engine is in charge of the escaping of values, and there is the additional benefit of a cache for the parameters to be retrieved.

When a query is constructed, the “bound” values are replaced by a character (? in ODBC, #[index]# in postgres…) in the body of the SQL query.

<select>
  <!--the value will be bound by the engine -->
  <node expr="@startDate = #2002/02/01#"/>
  <!-- the value will not be bound by the engine but visible directly in the query -->
  <node expr="@startDate = #2002/02/01#" noSqlBind="true"/>
</select>

To avoid binding a parameter, the “noSqlBind” attribute must be populated with the value ‘true’.

Query-building tip: query-building-tip-

To help with the syntax of a query, you can write the query using the generic query editor in the Adobe Campaign client console ( Tools/ Generic query editor… menu). To do this:

Output document format output-document-format

The return parameter is an XML document in the format of the schema associated with the query.

Example of a return from the “nms:recipient” schema on a “get” operation:

<recipient email="john.doe@adobe.com" lastName"Doe" firstName="John"/>

On a “select” operation, the document returned is an enumeration of elements:

<!-- the name of the first element does not matter -->
<recipient-collection>
  <recipient email="john.doe@adobe.com" lastName"Doe" firstName="John"/>
  <recipient email="peter.martinez@adobe.com" lastName"Martinez" firstName="Peter"/>
  <recipient...
</recipient-collection>

Example of a document returned for “count” type operation:

<recipient count="3"/>

Alias alias

An alias lets you modify the location of data in the output document. The alias attribute must specify an XPath on the corresponding field.

<queryDef schema="nms:recipient" operation="get">
  <select>
    <node expr="@firstName" alias="@firstName"/>
    <node expr="@lastName"/>
    <node expr="[folder/@label]" alias="@My_folder"/>
  </select>
</queryDef>

Returns:

<recipient My_folder="Recipients" First name ="John" lastName="Doe"/>

Instead of:

<recipient firstName="John" lastName="Doe">
  <folder label="Recipients"/>
</recipient>

Example of SOAP messages example-of-soap-messages

Write / WriteCollection (xtk:session) write---writecollection--xtk-session-

These services are used to insert, update, or delete an entity (“Write” method) or a collection of entities (“WriteCollection” method).

The entities to be updated are associated with a data schema. The input parameters are an authentication string (must be logged in) and an XML document containing the data to be updated.

This document is supplemented by instructions for configuring the write procedures.

The call does not return any data, except errors.

Definition of the “Write” and “WriteCollection” methods in the “xtk:session” schema:

<method name="Write" static="true">
  <parameters>
    <param name="doc" type="DOMDocument" desc="Difference document"/>
  </parameters>
</method>
<method name="WriteCollection" static="true">
  <parameters>
    <param name="doc" type="DOMDocument" desc="Difference collection document"/>
  </parameters>
</method>

Overview overview

Data reconciliation operates based on the definition of the keys entered in the associated schema. The write procedure looks for the first eligible key based on the data entered in the input document. The entity is inserted or updated based on its existence in the database.

The key of the schema of the entity to be updated is completed based on the xtkschema attribute.

The reconciliation key can therefore be forced with the _key attribute containing the list of XPaths that make up the key (separated by commas).

It is possible to force the type of operation by populating the _operation attribute with the following values:

Example with the ‘Write’ method example-with-the--write--method

Updating or inserting a recipient (implicit “insertOrUpdate” operation) with email address, date of birth and town:

<recipient xtkschema="nms:recipient" email="john.doe@adobe.com" birthDate="1956/05/04" folder-id=1203 _key="@email, [@folder-id]">
  <location city="Newton"/>
</recipient>

Deleting a recipient:

<recipient xtkschema="nms:recipient" _operation="delete" email="rene.dupont@adobe.com" folder-id=1203 _key="@email, [@folder-id]"/>

Example with the ‘WriteCollection’ method example-with-the--writecollection--method

Update or insertion for several recipients:

<recipient-collection xtkschema="nms:recipient">
  <recipient email="john.doe@adobe.com" firstName="John" lastName="Doe" _key="@email"/>
  <recipient email="peter.martinez@adobe.com" firstName="Peter" lastName="Martinez" _key="@email"/>
  <recipient ...
</recipient-collection>

Example on links example-on-links

Example 1 example-1

Associating the folder with a recipient based on its internal name (@name).

<recipient _key="[folder/@name], @email" email="john.doe@adobe.net" lastName="Doe" firstName="John" xtkschema="nms:recipient">
  <folder name="Folder2" _operation="none"/>
</recipient>

The “_key” and “_operation” attributes can be entered on a linked element. The behavior on this element is the same as on the main element of the input schema.

The definition of the key of the main entity (“nms:recipient”) consists of a field from a linked table (element <folder> schema “xtk:folder”) and the email.

Example 2 example-2

Updating the company (linked table in “cus:company” schema) from a recipient:

<recipient _key="[folder/@name], @email" email="john.doe@adobe.net" lastName="Doe" firstName="John" xtkschema="nms:recipient">
  <company name="adobe" code="ERT12T" _key="@name" _operation="update"/>
</recipient>

Example 3 example-3

Adding a recipient to a group with the group relation table (“nms:rcpGrpRel”):

<recipient _key="@email" email="martin.ledger@adobe.net" xtkschema="nms:recipient">
  <rcpGrpRel _key="[rcpGroup/@name]">
    <rcpGroup name="GRP1"/>
  </rcpGrpRel>
</recipient>

XML collection elements xml-collection-elements

By default, all the collection elements must be populated in order to update the XML collection elements. Data from the database will be replaced with data from the input document. If the document contains only the elements to be updated, you must populate the “_operation” attribute on all collection elements to be updated in order to force a merge with the XML data of the database.

Example of SOAP messages example-of-soap-messages-1