Page tree

Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.


You can pull the idsubjectscorecomments, and group. Here is what your XPath should look like:

No Format

Each line puts the path for the record. For example, the id tag lives within the ticket tag, so you need to specify this with /ticket/id. The score path is a little different because you want it to grab the text value and not the data type. You do not have to worry about this for the other data since there is only text value.

Once you have this configured, click Next.


Now you will see a preview of your data. The only thing you need to do here is change the column names. This is only to identify them more easily through this tutorial. 

Original NameChanged Name

Click Next and then through to the end of the wizard. Save the file as XMLParseExample. The file should automatically run after saving.

Extract data using JSON functions

Add the data into a new workbook.

Right click on the source sheet and select Duplicate.

Bring over all columns so you can clean up the data with JSON functions.

Name the duplicated sheet CleanUp


Click in the next empty column to bring up the formula builder. Select the JSON_VALUE function. You will be using this on the Comments column to strip out the list of the comments. The JSON key should be

comment. Name this column Comment.

The workbook should look like this:


Duplicate the CleanUp sheet, but only bring over the ID column and Comment column.



Now, you will break the comment array up into separate rows. Think of this as doing a reverse GROUPBY - you are exploding out the data.

Click in the next empty column and find the JSON_ELEMENTS function. You will be using this on the Comment column. 



You will notice that now, instead of 2 rows, you now have 4. This means that there is more than one comment in each ticket. 


In the next open column, you will be pulling out the type of comment (whether it is public or private). There are 2 levels of this, so you will be making a nested function.

First, click in the next open column and use JSON_VALUE on the Comment1 column. The JSON Key will be is-public

Name this column IsPublic.


Now, you will nest the function to pull out the TRUE from is-public. In the formula bar, edit the formula and add another JSON_VALUE around the function:



Your results will strip the “true” or “false” out of the JSON content:


In the next 2 columns, you will also use JSON_VALUE.

For the next column, you will pull out the type of comment. This will be from the column Comment1.


Name this column Type. Name the next column Text.


JSON_VALUE uses the provided key to pull out specific data in the JSON string. The keys indicate specifically what data to extract. Your columns should look like this after performing these functions:

Name this sheet TicketExtraction.

You will now duplicate the CleanUp sheet, but only bring over ID and Group.


Now that you have a new sheet, you want to break up the array of data into rows from the Group column. Click in the column next to Group and find JSON_ELEMENTS in the formula builder.


Name the new column GroupRow

This will break up the grouping of each ticket and breaks it down into rows. Your data will look similar to this:


Now, you will extract the data from your GroupRow column using id as the JSON key. Click in the next empty column and find the JSON_VALUE function in the builder just like you did on the previous sheet.


Name this column GroupName as you have extracted the name of each group.


In the next column, you will do the same thing to extract capability.


Name this column Capability. Your new columns will look something like this:



 Now that you have a Capability column, you want to extract all capabilities. Click in the next available column and find JSON_ELEMENTS in the formula builder. 


This extracts all capabilities into rows. Name column CapabilityRow.


Now you want to extract data from CapabilityRow. Click in the next available column and find JSON_VALUE again. You will now be finding the name of the capabilities.


Name this column CapName.

You will now do the same thing to get the data for each capability in the next column by using the JSON Key value.


Name the column CapValue. Your new columns will look similar to this:


Name this sheet Capabilities.

Now you can save and run your workbook.


 You have successfully parsed XML during your import and extracted data using JSON functions!


Check out the documentation on Importing XML Files and Using XPath

 Have additional questions about this guide or anything to do with Datameer? Join the 

“Got a question? Have an answer? Join the Datameer Community!