You can pull the id, subject, score, comments, and group. Here is what your XPath should look like:
/ticket/id /ticket/subject /ticket/score/text() /ticket/comments /ticket/group
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 Name||Changed 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.
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:
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!”