Error when loading XML data from REST service

[Mohammad8]

I have a custom table to which I want to load data from REST API (from 3rd party). I did the following but it didn't work:

1. I created a Data Source (System Import Sets -> Data Source)

2. I created a Transform Map to the data source which I created in the previous step

3. I added Field Maps for in the transform map which I created in the previous step

4. I went back to the data source and selected "Test Load 20 Records"

I got the following error:

com.glide.db.impex.datasource.DataSourceException: Unable to parse ids1f605b32db2563002c005a6adc961981.tmp

There are 3 weird things that are worth mentioning:

1. When I created the data source, I understood that the Import Set table should be created automatically, but I think it wasn't because when I take the name of the table and add ".do" to it in the Filter Navigator, it tries to search for it but it doesn't find it.

2. When I tried to create the Transform Map, I was supposed to find the link (Auto map matching fields) in the related lists according to this article Create a transform map, but I couldn't find it and I couldn't add it using the Configure menu.

3. When I tried to add the Field Maps, it asked me to select both the source table and the target table. I selected them, then I wanted to select the source field but the drop down list was empty.

[Chuck Tomasi]

I suspect the XPath field still has a value. It doesn't clear just because it's hidden.

Try this: Set it to XML, clear the XPath field, then set it back to JSON.

[Mohammad8]

Hi Chuck,

Thanks for your response. I already tried this but still it assumes it's empty 😞

Do you have the same issue at your environment. I mean does the XPath field hide once JSON is selected?

[Joel Millwood2]

The XPath for each row element should disappear when you change the Format to JSON as JSON does not use an XPath expression to select XML nodes which are then imported as rows in the Import Set table but instead uses a path for each row you want to import.

The following Product Documentation details how to import JSON data in ServiceNow and lists several examples of different data structures: Importing JSON files.

[Mohammad8]

Hi Joel,

Thanks for your response.

Yes I agree with that the XPath field should disappear and another field should appear instead, but what happens is that the XPath field disappears and the "Path for each row" doesn't appear. The article that you shared explains how to set the "Path for each field", but my problem is not in what value I should put in this field. It is rather in finding the field. Do you have the same behavior at your environment? (in other words: when you select JSON, do you see any field called "Path for each row"?)

[Joel Millwood2 · Accepted solution]

My apologies Mohammad, I did not realise that was the issue you were facing. I don't have the same problem in my environment, by default Path for each field is displayed when you select type JSON. If you check in a Personal Developers Instance you can see how it behaves without any customisations.

First, you will need to check to see if Path for each row is added to the Data Source Form Configure > Form Layout > Add Path for each row under XPath for each row:

Next, please check the UI Policies, Configure > UI Policies. There is a Policy called Format == JSON which makes the jpath_root_node (Path for each row) field visible and mandatory when the format is of type JSON. Make sure this Policy is active and that are not other policies making this field invisible.