ShipStation Advanced Configuration

XML Models

The connector models tables in CData Arc as XML. The modelling follows this structure:

A parent element identifies the table to insert into.
Each child element corresponds to a column in the target table.

This establishes a connection between XML elements and values, which allows the connector to read values from incoming XML documents and use them in INSERT or UPDATE queries. The XML Model for a table can be viewed by clicking </> Code above the configuration pane. The following XML is a simplified example model for a table called Account, which is modeled as the XML element <Account>:

<Items>
    <Account action="upsert">
        <Id key="true" />
        <account_c />
        <AccountNumber />
        <AccountSource />
    </Account>
</Items>

In the above example, Id, account_c, AccountNumber, and AccountSource are columns in the Account table. When the connector processes an XML file that matches this structure, it automatically inserts the values from that XML into the Account table. When the connector queries the Account table, it outputs XML that matches this structure.

Batching Input

Batching Output

Child Tables

Child Table XML

The Input Mapping XML structure is multi-layered when inserting into parent and child tables. The following XML snippet is an example Input Mapping for two tables related by a foreign key:

<Account action="upsert">
    <Id key="true" />
    <account_c />
    <AccountNumber />
    <AccountSource />
    <AccountPartner>
        <Id key="true" />
        <AccountFromId ref="@Id" />
        <IsPrimary />
        <OpportunityId />
        <Role />
    </AccountPartner>
</Account>

In the above example, Account is the parent table and AccountPartner is the child table. When the connector processes an input XML file that matches this structure, it updates both tables and ensures that the keys linking the two records are the same.

Using the ref Attribute

Some columns in child tables are marked with a ref tag. This tag indicates that these columns correspond to the primary key in the parent table. After making the insert into the parent table, and before making the insert into the child table, the connector queries the parent table for the primary key of the record it just inserted. The connector then uses this value when inserting into the child table. This is useful when the primary key of the parent table is generated by . In this case, this column is not passed in as input XML, so the connector does not know the value of the column until after it has inserted the record into the parent table. In the XML model, the ref attribute indicates that a column can be referenced later or that the value for a column should be read from another column in the mapping. In order to reference the value of columns that are generated by , the column element should include a ref attribute set to the reserved value LAST_INSERT_ID:

<Item action="upsert">
    <Id key="true" upsert="id" ref="@@LAST_INSERT_ID" />

This indicates that the id value will be retrieved after the insert and can be referenced later (usually in a child table). To reference this value later, set the ref element to the name of the relevant column:

<id key="true" ref="@id" />

In the above example, id is the name of the primary key in the parent table, and it is also the name of a column in the child table. The ref syntax instructs the connector to retrieve the id from the parent table after inserting, and then use that retrieved value as the id column in the child table.

Selecting From Child Tables

Only Process New or Changed Records

Using a Last Modified Column

Using a Custom Column

Pre-Processing and Post-Processing Queries

XML Attributes

Table Attributes

allColumns

insertQuery

outputResult

processChangesOnly

requireInput

selectQuery

table

upsertQuery

Column Attributes

aggregate

column

id

In a Select mapping, this tags the value of a column with an Id marker that can be referenced later in a child query using the syntax ${#ID}. This is useful when a column value is needed in a child query where matching column names in multiple tables can create ambiguity in the selection.

    <Shipments table="`Shipments`" selectQuery="SELECT * FROM `Shipments`">
        <ID type="integer" key="true" id="ShipID"/>
        ...
        <ShipmentOrders table="`ShipmentOrders`" selectQuery="SELECT * FROM `ShipmentOrders` WHERE `ShipmentID` = ${ID}">
            <ID type="integer" key="true" id="ShipOrderID"/>
            ...
            <ShipmentOrderItems table="`ShipmentOrderItems`" selectQuery="SELECT * FROM `ShipmentOrderItems` WHERE `ShipmentOrderID` = ${ID}">
                <ID type="integer" key="true" id="ShipOrderItemID"/>
                ...

key

ref

In an Upsert mapping, this attribute is used to provide values to upsert into the database that cannot be determined from the input XML alone. This is most commonly used in parent-child relationships to determine the database-generated primary key, or for one table to use a foreign key reference on a child table. For more information, see Using the ref Attribute.

   <Orders table="`Orders`" action="upsert">
        <ID type="integer" key="true" ref="@@LAST_INSERT_ID" />
        ...
        <OrderLines table="`OrderLines`" action="upsert">
            <ID type="integer" key="true" />
            ...
            <OrderID type="integer" ref="@ID" />
        </OrderLines>
    </Orders>

Getting Started

Flows

EDI Trading Partner Management Console

Connectors

Mapping

Admin API

How-To Guides

Scripting

Software License and Third-Party Copyrights

XML Models

Batching Input

Batching Output

Child Tables

Child Table XML

Using the ref Attribute

Selecting From Child Tables

Only Process New or Changed Records

Using a Last Modified Column

Using a Custom Column

Pre-Processing and Post-Processing Queries

XML Attributes

Table Attributes

allColumns

insertQuery

outputResult

processChangesOnly

requireInput

selectQuery

table

upsertQuery

Column Attributes

aggregate

column

id

key

ref

timeCheck

type

updateValue

​XML Models

​Batching Input

​Batching Output

​Child Tables

​Child Table XML

​Using the ref Attribute

​Selecting From Child Tables

​Only Process New or Changed Records

​Using a Last Modified Column

​Using a Custom Column

​Pre-Processing and Post-Processing Queries

​XML Attributes

​Table Attributes

​allColumns

​insertQuery

​outputResult

​processChangesOnly

​requireInput

​selectQuery

​table

​upsertQuery

​Column Attributes

​aggregate

​column

​id

​key

​ref

​timeCheck

​type

​updateValue

XML Models

Batching Input

Batching Output

Child Tables

Child Table XML

Using the ref Attribute

Selecting From Child Tables

Only Process New or Changed Records

Using a Last Modified Column

Using a Custom Column

Pre-Processing and Post-Processing Queries

XML Attributes

Table Attributes

allColumns

insertQuery

outputResult

processChangesOnly

requireInput

selectQuery

table

upsertQuery

Column Attributes

aggregate

column

id

key

ref

timeCheck

type

updateValue