JSON/XML Files

Use the JSON/XML File Load into Caching Database Module to load a JSON or XML file into an SQLite database.

Typical use cases for this functionality are:

• To verify the content in a JSON or XML file, e.g. how many records are present in general or how many records of a certain type are present.

• To assist in the above mentioned use case and provide full flexibility, the content is loaded into a database that you can query through SQL. This allows you to run all sorts of standard tests of Tosca DI, including Metadata field tests and Row by Row Comparison.

Configure the path of the SQLite caching database in the Settings dialog in Tosca Commander (see chapter "Settings - Tosca Data Integrity").

JSON/XML File Load into Caching Database Module

The JSON/XML File Load into Caching Database Module contains the following attributes:

ModuleAttribute	Description	Optional
File Name	Enter the full file path and file name of the JSON or XML file you want to load.
File Format	Define the file format of your file. The supported formats are JSON and XML.
JPath/XPath	Define the JPath or XPath expression to navigate through elements and attributes in an XML or JSON file. You can define the JPath/XPath, if you have a large file and want to filter out specific subnodes of the file. If you define XPath, namespaces are not supported.	X
Table Prefix	To avoid overwriting existing tables with the same name, specify a table prefix. This prefix is used for all tables created.	X

Logic for creating tables

After loading an XML file into the caching database, the content of the file is being converted into a table in the following manner:

• Every node in the file that has child nodes, is being converted into a table.

• All child nodes that are value nodes, become columns in the parent table. If a value node appears more than once in its parent or has an attribute, it will have its own table.

• If a tag appears in different places under n different parents, it has foreign key columns in the following format: ___id format for primary key. Parent1TableName___id for foreign key.

• If you specify an XPath, the result is wrapped inside an artificial root, called Entity to make it a valid XML. As a result, an Entity table is created.

• If the value node is missing in one of the occurrences of its parent, the value is NULL. If it is present, but has no text, it has an empty value.

• To query the content you have loaded into the caching database through SQL, use the foreign key in the child table to join the child table with the parent table, for instance.

  The query could include the following SQL statement:

SELECT * FROM address
JOIN person ON address.Person___id = Person.___id

In the example below, you can see the structure of an XML file containing three value nodes for Address:

<Person>
	<Name>John</Name>	
	<Address>
		<Street>Hill</Street>
		<City>Sydney</City>
		<Country>AUS</Country>
	</Address>
	<Address>
		<Street>Street 2</Street>
		<City>City 2</City>
		<Country>HG</Country>
	</Address>
</Person>

___id column is created as a primary key for each table. The Address node has three Street, City and Country value nodes.

If you want to display how the actual tables are linked, you can use the SchemaCrawler.

Example: Work with the JSON/XML File Load into Caching Database Module

This example loads the file Person Table.xml into the caching database.

The XML file contains repeating value nodes that you want to convert into a Person, Address and Street table.

<Person>
	<Name>Kolmogorov</Name>	
	<Address>
		<Street>Hill</Street>
		<Street>Crescent</Street>
		<City>Sydney</City>
		<Country>AUS</Country>
	</Address>
</Person>

After you run the TestCase in ScratchBook, you get the following result:

The Loginfo column in the ScratchBook displays the execution result and which tables have been created: Person, Address, Street.

In your installed DB Browser for SQLite you can open the database you have loaded and view the tables that have been created:

As shown in the example, ___id column is created as a primary key for the tables. The Address node has two Street value nodes. Since they are repeating, a separate table for the Street node is created. To link the Street table to the Address table, a foreign key Address___id is added. In this example, both Hill and Crescent belong to the same Address.