As explained CrossGenerate needs three inputs, being a model, a template and a configuration. In the following example we will take an existing fragment of code as a starting point; transform this to a model and a template and then add the required configuration to be able to generate the original code fragment and eventually extend it with additional functionality.

Original code to model and template

In this example you see an example piece of SQL code which creates two tables for the system ‘ExampleSource’. For this example we want to re-create the code using CrossGenerate. First step is to split the information which resides in the original code into an information model and a template.

  • From the original code we transform all names and data types to the model, as can be seen below.
    • Note: how you structure the XML in the model is totally up to you, this is just one way of storing the information in a model.
  • Also from the original code we extract the code structure in a template. We re-write the code so every construct only appears once and all names are translated into placeholders. So for example in the original code ‘ExampleSource’ is the schema, in the template we have a placeholder for this called ‘schema_name’. Placeholders are created for the table and column information as well.
    • Note: a placeholder is made up of an object name (for example ‘schema’) followed by the property of the object to which it should resolve (for example ‘name’).
      • In this example the ‘_’ is used to split the object name from the object property name. The ‘_’ is in this context called an ‘accessor’. The accessors used in CrossGenerate can be configured to your needs.
      • The object name does not need to be the same as the model element name. In this example the schema object from the original code is called ‘system’ in the model while its named ‘schema’ in the template. The relation between the model object and template objects is configured using binding configuration, which is explained in the section named Model section binding.

Template sections

In the previous step we have transformed some example code into a model and a template. In this step we need to enrich the template with section information so the generator knows what parts of the template need to be repeated.

Looking at the template compared with the original code we can identify three sections:

  • Schema section
    • The whole template is at the level of a schema (named ‘system’ in the model).
    • With CrossGenerate, a template always has one implicit section, being the whole template (root section). This is what we will be using for the schema section.
  • Table section
    • The ‘CREATE TABLE …’ part needs to be repeated for every table (named ‘entity’ in the model). So for the table section we add a section annotation in the template named ‘CreateTable’. The section starts right after the annotation (so we don’t need to specify a begin of the section) and it ends on first line which contains “GO”. This results in the @XGenTextSection annotation as can be seen in the example below.
  • Column section
    • The ‘[column_name] …’ row needs to be repeated for every column of the current table (named ‘attribute’ in the model). So for the column section we add a section annotation in the template where we name the section ‘TableColumn’. The section starts right after the annotation and is only one line, which is the default bounds of a section annotation. Next to the bounds we need to specify a suffix for all repetitions but the last since there needs to be a comma between all column definitions. This results in the @XGenTextSection annotation as can be seen in the example below.

Model section binding

With the model and template ready, next up is creating the configuration for binding the model and the template so the generator knows how template sections are bound to model elements.

  • Template level binding
    • As explained in the first section, the template itself has one implicit section being the whole template itself (root section). The name of this section can be defined on the ‘TextTemplate’ element in the configuration. In this example we will name the root section “StagingScript”.
      • <TextTemplate rootSectionName=”StagingScript”>
    • Now the name of the section is defined we can start creating a binding for this section. We want to bind the ‘system’ element from the model onto this section using placeholderĀ  ‘schema’.
      • <SectionModelBinding section=”StagingScript” modelXPath=”/system” placeholderName=”schema”>
  • Create table binding
    • In the template we have created a section named “CreateTable”. We want to bind this section on the model elements named ‘entity’ and use the placeholder ‘table’.
      • <SectionModelBinding section=”CreateTable” modelXPath=”entity” placeholderName=”table”>
        • Note: the modelXPath in this example is relative to its parent section (StagingScript), so it will search for elements named ‘entity’ within the parent ‘system’ element.
    • Within the ‘CreateTable’ section we also want to use the ‘schema’ placeholder. For this we need to create a placeholder definition withing the SectionModelBinding definition.
      • <Placeholder name=”schema” modelXPath=”../../system” />
        • Note: again here the modelXPath is relative to its parent SectionModelBinding, so relative to the ‘entity’ element in the model. A non-relative modelXPath would result in incorrectly generated code since attributes would be outputted outside of their parent entity context.
  • Table column binding
    • In the template we have created a section named “TableColumn” which needs to be bound to the model element named ‘attribute’ and using the placeholder name ‘column’.
      • <SectionModelBinding section=”TableColumn” modelXPath=”./attribute” placeholderName=”column”>

See the section and model binding pictured in the following animation:


Create a new XGenConfig where we configure the TextTemplate, set the comment prefix for SQL and the output type to ‘single_output’.

Set the name of the root section. This is the section which encapsulates the whole template (green area).
Create a section and model binding (SectionModelBinding) to bind the ‘StagingScript’ section (root) to the model element ‘system’ and assign the placeholder ‘schema’ so it can be referenced from the template.
Create a section and model binding for the ‘CreateTable’ section (defined in the template) for the ‘entity’ elements from the model and assign it the ‘table’ placeholder so it can be used in the template.
To be able to use the ‘system’ element information from the model, create a placeholder named ‘schema’ within the ‘CreateTable’ section which points to the parent ‘system’ element of the ‘entity’ element.
Create a section and model binding for the ‘TableColumn’ section (defined in the template) for the ‘attribute’ elements from the model and assign it the ‘column’ placeholder so it can be used in the template.


Generation step

Since we now have created are model, template and configuration we can run the generator and get our original code again. See the results in the following image.

Enrich the model and template

Now we are able to re-create the original code using CrossGenerate, we can enrich the model and template a bit to create a new version of the script.

For this example we add some attributes to both entities (in the model) and add a technical column to all tables (in the template) to store the date and time a record is staged.

See the enrichment’s marked in the following image. We also need to change the ‘TableColumn’ section annotation a bit: The suffix (the ‘,’ after every column) is now part of the template since there is always a fixed column after the last one that is substituted from the model. This means the suffix and suffixStyle are no longer needed. Note that the configuration doesn’t change.

Using the enriched model and template we can execute the generator again and we will get the following results.

When we now want to add more tables or columns we only have to update the model and re-generate. Also using the same model we can create another template, for example for loading the tables, and generate the required code. The latter guarantees all code is always in sync, since they are all generated using the same model.

More examples

When you want to look at more examples please read the documentation. In the online documentation examples are also given for different technologies like Microsoft SSIS, Informatica PowerCenter & IBM DataStage.