Example of a format descriptor file for the JSON import
file :
<LOADFORMAT>
<TABLE PATH="" NAME="ORGANISATION" ROOTTABLE="1">
<TABLE PATH="Employees">
<COLUMN PATH="Name" />
<COLUMN PATH="Function" />
<COLUMN PATH="Address.Street" NAME="ADDRESS_STREET"/>
<COLUMN PATH="Address.ZipCode" NAME="ADDRESS_ZIPCODE"/>
<COLUMN PATH="Address.City" NAME="ADDRESS_CITY"/>
</TABLE>
<COLUMN PATH="Name" NAME="ORGANISATION_NAME" />
</TABLE>
</LOADFORMAT>
Example of a JSON file to load :
{"Name":"Selligent","Employees":[
{"Name":"Name 1","Function":"Function
1","Address":{"Street":"Street 1","ZipCode":"1000","City":"City
1"}},
{"Name":"Name 2","Function":"Function
2","Address":{"Street":"Street 2","ZipCode":"1001","City":"City
2"}},
{"Name":"Name 3","Function":"Function
3","Address":{"Street":"Street 3","ZipCode":"1002","City":"City
3"}},
{"Name":"Name 4","Function":"Function
4","Address":{"Street":"Street 4","ZipCode":"1003","City":"City
4"}},
{"Name":"Name 5","Function":"Function
5","Address":{"Street":"Street 5","ZipCode":"1004","City":"City
5"}},
{"Name":"Name 6","Function":"Function
6","Address":{"Street":"Street 6","ZipCode":"1005","City":"City
6"}},
{"Name":"Name 7","Function":"Function
7","Address":{"Street":"Street 7","ZipCode":"1006","City":"City
7"}},
{"Name":"Name 8","Function":"Function
8","Address":{"Street":"Street 8","ZipCode":"1007","City":"City
8"}},
{"Name":"Name 9","Function":"Function
9","Address":{"Street":"Street 9","ZipCode":"1008","City":"City
9"}},
{"Name":"Name 10","Function":"Function
10","Address":{"Street":"Street 10","ZipCode":"1009","City":"City
10"}}]}
The descriptor XML starts with a root element <LOADFORMAT>.
Then there are TABLE and COLUMN elements.
- COLUMN elements must be part of a TABLE element.
- TABLE elements can be nested (parent-child relations).
TABLE elements map to JSON arrays only, except the root table indicated with the ROOTTABLE="1" attribute. It maps to the root json element, hence it will only contain one csv/database table row.
There can only be one root table (a descriptor TABLE element with attribute ROOTTABLE="1"). If multiple root tables are defined, an exception will be thrown (and the task will fail).
Every TABLE and COLUMN element can have the following attributes :
-
PATH — The path of the json element to match. For root table descriptor elements the path will be typically empty. For columns and child table descriptor elements the path is relative against its respective parent table.
-
Path attribute values are separated by dots ".", without array tokens "[" and "]" (if any) (e.g.,<COLUMN PATH="Address.Street" NAME="ADDRESS_STREET"/>).
-
Path names are not case sensitive.
-
-
NAME
-
For TABLE descriptor elements the name is used to derive the CSV filename and corresponding database table name.
-
For COLUMN descriptor elements the name is used to define the CSV column header and corresponding database table column names. If the name attribute is not defined or empty, it will be derived from the path attribute value (last substring after the last path dot ".", or if no dot in the path just the path name itself).
If the name is still empty after derivation an exception will be thrown during the load of the descriptor. -
TABLE names of the same level (root tables, child tables) must be unique (derived or not).
-
Column names within a table must be unique as well (derived or not). If the uniqueness is not fulfilled it may go wrong — there is no logic that will check the rules for the uniqueness of names.
-
-
KEY — This attribute maps to the underlying column name that should be used a column key value. A column key value is used for the relation of its child tables and will be present as a foreign key ('without constraints') in the child database table.
This descriptor format should be straight-forward, although it may still be difficult to determine the path attribute values. However it should be a lot less complex compared to XML descriptors.
