Collation

Collation script helps to gather scattered data. It is designed to be used for merging tables containing related data. It works in a similar way as a standard import and export, however it requires 3 files to be created (all of them should have the same names – e.g.my_collation.scriptmy_collation.destinationmy_collation.collation ):

 

  • *.script – it contains information about a source and destination for imported data.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    <?xml version="1.0"?> 
    <Script xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
      <Actions> 
        <Action xsi:type="Load">
          <Source>D:\my_collation.collation</Source> 
          <Destination>my_collation</Destination> 
        </Action> 
      </Actions> 
    </Script>
  • *.destination – this file contains information about database connection.
    1
    user id=USER;password=PASSWORD;data source=dbserver\sqlexpress;integrated security=False;pooling=true;enlist=false;initial catalog=statmap;cartridge=SqlServer;schema=dbo
  • *.collation – Collation file is consisted of Master Table and unlimited Detail Tables.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    <?xml version="1.0" encoding="utf-8"?> 
    <Collation> 
      <MasterTable> 
        <ConnectionString>user id=USER;password=PASSWORD;data source=dbserver\sqlexpress;table name=table;initial catalog=statmap;cartridge=SqlServer;schema=dbo</ConnectionString>
        <ColumnNames> 
          <string>Id</string> 
          <string>Location</string> 
        </ColumnNames> 
      </MasterTable> 
      <DetailTable> 
        <ConnectionString>data source=\\SDrive\data\LLPG\Tables;table name=National_Coverage;cartridge=MapInfo Tab</ConnectionString> 
        <DetailColumnName>Location</DetailColumnName> 
        <MasterColumnName>Location</MasterColumnName> 
        <ColumnNames> 
          <string>Geometry</string> 
        </ColumnNames> 
      </DetailTable> 
    </Collation>

MasterTable contains a ConnectionString and ColumnNames string array.

DetailTables have a ConnectionStringMasterColumnNameDetailColumnName and ColumnNames string arrays.

  • ConnectionString is a set of parameters required to connect to database or to local file,
  • ColumnNames is a list of column, which will be inserted in resultant ‘collated’ table,
  • MasterColumnName – is a unique column from MasterTable, which provides a link to DetailColumnName,
  • DetailColumnName – is a unique column from each DetailTable, which provides a link to MasterColumnName.

Simple example showing usage of the Collation Script presented above: