MySQL Advanced Configuration - CData Arc Documentation

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

In Upsert Mappings

In an Upsert mapping, it is possible to issue queries that automatically take place before or after all records are upserted into a table. To configure this feature, first click </> Code in the Upsert Configuration pane.

This opens a text editor that allows you to edit advanced settings for a table mapping. The text in the editor might resemble this snippet:

<Items>
    <Names table="`dbo`.`Names`" action="upsert">
        <ID key="true" />
        <Name />
        …
    </Names>
</Items>

To issue a query before or after any columns are added to the table, define an element with the following attributes:

requireInput: Set to false. This attribute value instructs the connector to issue the query even without any matching input element in the input file.
insertQuery: Contains the query that you want to execute either before or after the UPSERT query.
table (optional): Set to the name of the table that you want to target with the extra query. (In the examples below, the table name is updateTable.) If you issue the query at the root level of the mapping—that is, as a child of the <Items> element—this attribute is not required. If you issue a query as a child to another table, setting the table attribute ensures that the query is distinguished from a column of that table.

Place the newly defined element before or after the UPSERT query, depending on when you want it to execute. In this example, the element <runFirst> executes the query EXEC [dbo.FlushTemp] before any UPSERT requests to the table:

<Items>
    <runFirst table="updateTable" insertQuery="EXEC [dbo].[FlushTemp]" requireInput="false" />
    <Names table="`dbo`.`Names`" action="upsert">
        <ID key="true" />
        <Name />
        …
    </Names>
</Items>

In this example, the element <runLast> executes the query EXEC [dbo].[CommitTemp] after all UPSERT requests to the table are completed.

<Items>
    <Names table="`dbo`.`Names`" action="upsert">
        <ID key="true" />
        <Name />
        …
    </Names>
    <runLast table="updateTable" insertQuery="EXEC [dbo].[CommitTemp]" requireInput="false" />
</Items>

The element names runFirst and runLast are placeholders. You can modify them to meet your requirements.

PreProcessing and PostProcessing queries in an Upsert mapping do not accept input elements that are not hardcoded in the query, and they do not generate output data.

In Select Mappings

In a Select mapping, it is possible to issue queries that automatically take place before or after queries are issued to the targeted table. To configure this feature, first click </> Code in the Select Configuration pane.

This opens a text editor that allows you to edit advanced settings for a table mapping. To issue a query before or after any columns are added to the table, define an element with the following attributes:

outputResult: Set to false. This attribute value instructs the connector not to return any data in the output file as a result of the query.
selectQuery: Contains the query that you want to execute either before or after the SELECT query.
table (optional): Set to the name of the table that you want to target with the extra query. (In the examples below, the table name is selectTable.) If you issue the query at the root level of the mapping—that is, as a child of the <Items> element—this attribute is not required. If you issue a query as a child to another table, setting the table attribute ensures that the query is distinguished from a column of that table.

Place the newly defined element before or after the SELECT query, depending on when you want it to execute. In this example, the element <runFirst> executes the query EXEC [dbo].[prepTable] before the SELECT query is issued against the table:

<Items>
    <runFirst table="selectTable" selectQuery="EXEC [dbo].[prepTable]" outputResult="false" />
    <Names table="`dbo`.`Names`" action="select">
        <ID key="true" />
        <Name />
        …
    </Names>
</Items>

In this example, the element <runLast> executes the query EXEC [dbo].[cleanTable] after the SELECT query and any child queries are issued against the table:

<Items>
    <Names table="`dbo`.`Names`" action="select">
        <ID key="true" />
        <Name />
        …
    </Names>
    <runLast table="selectTable" selectQuery="EXEC [dbo].[cleanTable]" outputResult="false" />
</Items>

The element names runFirst and runLast are placeholders. You can modify them to meet your requirements.

PreProcessing and PostProcessing queries in a Select mapping do not accept input elements that are not hardcoded in the query.

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>

​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

​In Upsert Mappings

​In Select Mappings

​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

In Upsert Mappings

In Select Mappings

XML Attributes

Table Attributes

allColumns

insertQuery

outputResult

processChangesOnly

requireInput

selectQuery

table

upsertQuery

Column Attributes

aggregate

column

id

key

ref

timeCheck

type

updateValue