Error when loading XML data from REST service

Mohammad8 · ‎11-07-2018

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.

Joel Millwood2 · ‎11-09-2018

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.

View solution in original post

Chuck Tomasi · ‎11-07-2018

I would go back to the data source and validate the table name to ensure that you spelled it correct. I know I've been guilty of typos in table names and other places that have caused confusion when I thought a was b and b was a all because b was d, or something else. 🙂

If the table name is spelled correctly, you can verify that it does exist by going to System Import Sets> Import Sets and if it's not listed there, you don't have the import set table created yet.

if it hasn't been created, I recommend trying it from the start again with a new data source with different name. If it fails to create the import set table after test loading 20 rows, then check the error logs.

Mohammad8 · ‎11-07-2018

Thanks for your reply Chuck

I didn't type the import set table name, it was automatically generated once I typed the import set table label. However, I tried a new data source as you can see in the screenshot. I followed the same steps but still I faced the same issues. The import set is not created. The "Auto map matching fields" doesn't appear. The Source Field drop down list is empty.

As for the error, I also checked the import log and it doesn't provide more details. Please see the screenshot.

There must be something wrong I'm doing but I don't know what it is. I'm new to ServiceNow. I do really appreciate any help you provide.

Joel Millwood2 · ‎11-07-2018

Hi Mohammad,

The three strange things you mention are all because the import set table was not created. You are correct in that the import set table is created automatically however it is only created when ServiceNow is able to import data from your Data Source so that is where we need to start.

When importing data via a Data Source the usual order of operations is a little different to what you have done:

Create the data source
Test load some records and verify
Create the transform map and any required field map scripts
Import all records and review the imported data
Run the transform map
Review the transform logs
Review the data in the target table

If you follow the above steps you will be able to use the auto mapping assist functionality because the import set table is created and has fields with data populated. As your table was not created and had no fields, ServiceNow was not able to pair any source fields with fields in the target table.

Looking at your Data Source, your File Retrieval Method is HTTP and your File Path is api/projects/projects. This looks to be what is causing the issue, ServiceNow is not able to pull any XML data from that path and hence is not creating an Import Set Table with the data in question.

Perhaps start again with a new Data Source and follow the steps above. Get the Data Source working first and make sure you can test load some records in and the Import Set Table is being created then go on to create the Transform Map etc.

Mohammad8 · ‎11-08-2018

Hi Joel,

Thanks for your response. The structured way that you provided helped me find the problem, but I didn't know how to solve it yet.

I've found out that the source file format is JSON not XML. I was deceived by the browser formatting it as XML.

I thought that the solution to this problem would be simply by changing the file format to JSON, but when I selected JSON, the XPath field disappeared. I tried to do load test data and it gave me an this error:

com.glide.db.impex.datasource.DataSourceException: org.jaxen.XPathSyntaxException: Unexpected ''

All the blog posts and articles assume that I have to set the XPath. For me, this field disappears once I select JSON as a file format.