Skip to content

SQL - Simple Staging example

Model

In this example we will be generating staging tables for each entity defined in the Source model.

Template

We create a template for creating a staging table, in the file name we use the 'system_name' placeholder to have the system name in the resulting output file. We define the @XGenTextSection annotation to define the 'CreateTable' section to repeat for each table. We also define the section named 'TableColumn' to repeat for each column in the table.

Staging_Tables_system_name.sql

-- @XGenTextSection(name="CreateTable" literalOnLastLine="GO")
CREATE TABLE [system_name].[entity_name] (
  -- @XGenTextSection(name="TableColumn")
  [attribute_name]           attribute_fulldatatype        NULL,
  [StageDateTime]            datetime2(2)              NOT NULL  
);
GO

Documentation

For documentation on templates, please see Template.

Config

In the config we:

  • set the FileFormat templateType to 'text' and set the comment and annotation format.
  • add a SectionModelBinding element to bind the 'CreateTable' section (defined in the template) to each 'entity' element in the model.
  • within the 'CreateTable' section binding add a SectionModelBinding to bind the 'TableColumn' section (defined in the template) to each 'attribute' element in the model.
<?xml version="1.0" encoding="UTF-8"?>
<XGenConfig>
  <Model/>
  <TextTemplate rootSectionName="System">
    <FileFormat currentAccessor="_" singleLineCommentPrefix="--" annotationPrefix="@XGen" annotationArgsPrefix="(" annotationArgsSuffix=")" />
    <Output type="single_output" />
  </TextTemplate>
  <Binding>
    <!-- Bind the 'System' template section on the /modeldefinition/system elements in the model. -->
    <SectionModelBinding section="System" modelXPath="/modeldefinition/system" placeholderName="system" variableName="system">
        <!-- Bind the 'CreateTable' template section on the mappableObjects/entity elements in the model. -->
        <SectionModelBinding section="CreateTable" modelXPath="mappableObjects/entity" placeholderName="entity">
          <Placeholders>
            <!-- For the placeholder the modelXPath is relative to its section model XPath and can use variables defined in the parent section or it's ancestors. 
                 In the example below, modelXPath refers to variable system, which resolves to the modelXPath of the previously defined section named "System". -->
            <Placeholder name="system" modelXPath="$system" />
          </Placeholders>
          <!-- Bind the 'TableColumn' template section on the attributes/attribute elements in the model. -->
          <SectionModelBinding section="TableColumn" modelXPath="attributes/attribute" placeholderName="attribute" />
        </SectionModelBinding>
    </SectionModelBinding>
  </Binding>
</XGenConfig>

Documentation

For documentation on the configuration, please see Config.

Output

When running CrossGenerate the output using the given Model, Template and Config will be as follows:

Staging_Tables_ExampleSource.sql

CREATE TABLE [ExampleSource].[Order] (
  [Id]           int        NULL,
  [OrderDate]           datetime        NULL,
  [OrderNumber]           varchar(50)        NULL,
  [CustomerId]           int        NULL,
  [TotalAmount]           decimal(12,2)        NULL,
  [StageDateTime]            datetime2(2)              NOT NULL  
);
GO
CREATE TABLE [ExampleSource].[Customer] (
  [Id]           int        NULL,
  [FirstName]           varchar(50)        NULL,
  [LastName]           varchar(100)        NULL,
  [City]           varchar(50)        NULL,
  [Country]           varchar(3)        NULL,
  [Phone]           varchar(20)        NULL,
  [StageDateTime]            datetime2(2)              NOT NULL  
);
GO

Attachments

The sample solution with the template database scripts can be found in the following zip file: