Extracting data from a file using XPath or JSONPath

Gospel supports XPath and JSONPath readers for extracting data from XML and JSON source files. XPath and JSONPath are languages that provide a way to locate and process data from the source files, which are then imported to specific records in the Gospel platform. 

In simple words, the readers define the mapping required for retrieving data from the source files before importing records to the Gospel platform. The rules are defined in the definition.properties file.

It is assumed that you are familiar with:

  • The syntax of XPath and you have prior knowledge of the common XPath expressions
  • The syntax for JSONPath and JSON document structure

Using XPath for XML files

XPath (XML Path Language) provides a simple, concise path-like syntax to identify and select nodes in an XML document. The top-most element of the tree is called the root element. You can define the nearest parent selection in the definitions.properties.

XML Example

In the following example, the ancestors of the CityName,ID, EmployeeName,Department and Address elements are the EmployeesCity and the Country (root) elements: 

<?xml version="1.0" encoding="UTF-8"?>
<Country>
   <City>
      <CityName>London</CityName>
      <Employees>
         <ID>ID801</ID>
         <EmployeeName>John Smith</EmployeeName>
         <Department>Management</Department>
         <Address>
            <Street>Nine Elms Lane</Street>
            <Postcode>SW8 5AL</Postcode>
         </Address>
      </Employees>
   </City>
   <City>
      <CityName>Rome</CityName>
      <Employees>
         <ID>ID802</ID>
         <EmployeeName>Anne Stein</EmployeeName>
         <Department>Finance</Department>
         <Address>
            <Street>Greenwich</Street>
            <Postcode>SE10 9JN</Postcode>
         </Address>
      </Employees>
   </City>
   <City>
      <CityName>Berlin</CityName>
      <Employees>
         <ID>ID803</ID>
         <EmployeeName>Jack Aranda</EmployeeName>
         <Department>Human resources</Department>
         <Address>
            <Street>King's Cross</Street>
            <Postcode>N1 9AL</Postcode>
         </Address>
      </Employees>
   </City>
   <City>
      <CityName>Madrid</CityName>
      <Employees>
         <ID>ID804</ID>
         <EmployeeName>Rose Bush</EmployeeName>
         <Department>Human resources</Department>
         <Address>
            <Street>Atlas Road</Street>
            <Postcode>NW10 6DN</Postcode>
         </Address>
      </Employees>
   </City>
   <City>
      <CityName>Paris</CityName>
      <Employees>
         <ID>ID805</ID>
         <EmployeeName>Dennis Crawford</EmployeeName>
         <Department>Accounts</Department>
         <Address>
            <Street>Copperfield Road</Street>
            <Postcode>E3 4RR</Postcode>
         </Address>
      </Employees>
   </City>
</Country>

XML Definitions

To import the records in Record Definition Employees, in definitions file map the elements to the nearest parent as:

record.Employees.base = City
record.Employees.id = Employees/ID
record.Employees.fields.CityName = CityName
record.Employees.fields.EmployeeName = Employees/EmployeeName
record.Employees.fields.Department = Employees/Department
record.Employees.fields.Street = Employees/Address/Street
record.Employees.fields.Postcode = Employees/Address/Postcode

LedgerBridge Connector allows you to generate the value for a field by concatenating strings or elements. For example, you can generate the value for the Address field by concatenating the values in the Street and Postcode elements, and adding a string '-' to separate the two values.

record.Employees.fields.Address = concat(Employees/Address/Street, '-', Employees/Address/Postcode)

Using JSONPath for JSON files

JSONPath is a query language that lets you extract and import only the required data from the JSON source file. The top-most element of the tree is called the root element. JSON is in a hierarchical data format that is represented through a parent-child property relationship. It allows you to import any JSON data which has a recurring object structure.

JSON Example

In the following example, the parent elements of the CityName,ID,EmployeeName, Department and Address elements are the EmployeesCity and the Country (root) elements: 

 {
   "Country":{
      "City":[
         {
            "CityName":"London",
            "Employees":{
               "ID":"IDJ01",
               "EmployeeName":"John Smith",
               "Department":"Management",
               "Address":{
                  "Street":"Nine Elms Lane",
                  "Postcode":"SW8 5AL"
               }
            }
         },
         {
            "CityName":"Rome",
            "Employees":{
               "ID":"IDJ02",
               "EmployeeName":"Anne Stein",
               "Department":"Finance",
               "Address":{
                  "Street":"Greenwich",
                  "Postcode":"SE10 9JN"
               }
            }
         },
         {
            "CityName":"Berlin",
            "Employees":{
               "ID":"IDJ03",
               "EmployeeName":"Jack Aranda",
               "Department":"Human resources",
               "Address":{
                  "Street":"King's Cross",
                  "Postcode":"N1 9AL"
               }
            }
         },
         {
            "CityName":"Madrid",
            "Employees":{
               "ID":"IDJ04",
               "EmployeeName":"Rose Bush",
               "Department":"Human resources",
               "Address":{
                  "Street":"Atlas Road",
                  "Postcode":"NW10 6DN"
               }
            }
         },
         {
            "CityName":"Paris",
            "Employees":{
               "ID":"IDJ05",
               "EmployeeName":"Dennis Crawford",
               "Department":"Accounts",
               "Address":{
                  "Street":"Copperfield Road",
                  "Postcode":"E3 4RR"
               }
            }
         }
      ]
   }
}

JSON Definitions

To import the records in Record Definition Employees, in definitions file map the elements as:

record.Employees.base = $.Country.City[*]
record.Employees.id = $.Employees.ID
record.Employees.fields.CityName = $.CityName
record.Employees.fields.EmployeeName = $.Employees.EmployeeName
record.Employees.fields.Street = $.Employees.Address.Street
record.Employees.fields.Postcode = $.Employees.Address.Postcode
record.Employees.fields.Department = $.Employees.Department

Side by side comparison

This section provides a side-by-side comparison of the JSONPath syntax expressions with its XPath elements.

ActionDefinition JSONPathXPath
Return all records in the element Cityrecord.<record-definition-id>.base$.Country.City[*]

Map to the nearest parent element

City

Set the ID to an element in the source file or automatically generate the IDrecord.<record-definition-id>.id

$.Employees.ID


Employees/ID

record.<record-definition-id>.id%AUTOID%%AUTOID%
Map the fields in Gospel to child objects in the source filerecord.<record-definition-id>.fields.CityName

$.CityName

CityName

record.<record-definition-id>.fields.EmployeeName$.Employees.EmployeeNameEmployees/EmployeeName
record.<record-definition-id>.fields.Street $.Employees.Address.StreetEmployees/Address/Street

Import specific data from the source files.

In the sample data XML and JSON example, the data "John Smith" is returned.

Map the base record

record.<record-definition-id>.base 

$.Country.City[0]

City[0]

Map the objects/elements

record.<record-definition-id>.EmployeeName

$.Employees.EmployeeName

Set the field as

Employees/EmployeeName

Refer to the XPath and JSONPath documentation for more information on complex mapping syntax.