...

Extract data from XML and JSON

 

1. Methods

To extract data from XML and JSON:

Read the strings, parse them into the data of multi-layer structure with xml and json functions, and then calculate it into the required target.

2. Examples

2.1 Extract data from XML

There is an XML file xml.xml, and the data is as follows:

..

Extract the classification and details from the multi-layer XML to form a two-dimensional table, and the results are as follows:

..

The SPL script is:

A

1

=file("xml.xml").read().import@x()

2

=A1.xml.li.(form).new(h3,p.select.option)

3

=A2.news(option;h3:name,~:time)

4

=file("result.txt").export@t(A3)

A1 Read xml data into stings and parse them into a multi-layer table sequence according to xml

A2 Organize the table sequence according to multi-level nodes.

A3 Expand multiple option values in A2 table sequence and concatenate them into a new table sequence.

A4 Export the result to result.txt.

2.2 Extract data from json

There is a JSON file json.json, and the data is as follows:

..

This multi-layer JSON needs to be converted to a two-dimensional table and saved as CSV. There are two more fields in the identities field, which will be split to the same level as their upper level. The results are as follows:

..

The SPL script is:

A

1

=file("json.json").read()

2

=json(A1).users.new(displayName,givenName,surname,extension_user_type,identities.signInType:
  issuerType,identities.issuerAssignedId,extension_timezone,extension_locale,extension_tenant)

3

=file("json.csv").export@tc(A2)

Brief descriptions:

A1 Read the data in the json file into strings.

A2 The strings are parsed into a multi-layer table sequence according to the JSON format. The identities.signInType and identities.issuerAssignedId are split to the upper level using the new function.

A3 Export results to json.csv.


 
 •  163 View  •