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.script, my_collation.destination, my_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.1user 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 ConnectionString, MasterColumnName, DetailColumnName 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: