JSON/XML File load into Caching Database module

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

This module contains the following values:

Value	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 large file and want to filter out specific subnodes of the file. NOTE: 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 string. If the value node has a value of null, the value is <null>.

  Let's see the difference between a missing node (NULL) and a value node with the value of null (<null>).

  You use the JSON/XML File Load into Caching Database module to load the following content:

  Copy

  {
     Items:
     [
        {
           "Id" : 1,
           "Name"  : "Bill",
           "Position" : "Manager"
        },
        {
           "Id" : 2,
           "Name" : "Kirsten",
        },
        {
           "Id" : 3.
           "Name" : "Jeff",
           "Position" : null
     ]
  }

  The Position column in the resulting table looks as follows:

  • Id 1 has the value Manager.

  • Id 2 has the value NULL.

  • Id 3 has the value <null>.

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