Loading XML data from REST API into ServiceNow custom table

[Mohammad8]

I created a new table in ServiceNow, let's call MyTable. I also have a restful API which I created on another website that returns data in XML format. I need to load the data from this API into MyTable.

[Joel Millwood2]

Hi Mohammad,

What have you tried so far?

There are a couple of ways to achieve this, you can create an XML data source with a file retrieval method of HTTP/HTTPS, see the following product documentation for more information: Create an XML data source. Once you have created this data source and a transform map you can setup a Scheduled Import (System Import Sets > Administration > Scheduled Import) to bring in this data on a periodic basis.

Should the above not suffice, you could create a Scheduled Script Execution (System Definition > Scheduled Jobs) that would fire off the REST request again periodically and use the ImportSetUtil Script Include to process the xml which is returned into the target table.

Another option would be to post data from your other website into a import set / staging table in ServiceNow using the Import Set API, it really depends on what your requirements are.

[Mohammad8]

Hi Joel,

Thanks for your answer.

This clears out a lot of the ambiguity I had when searching for the way of implementing this task. I was overwhelmed and confused by the many articles that I thought were related but apparently they're explaining different ways of doing this. Now I understood from you that there are 3 ways to implement this. I'll try the first approach and I hope it works. Thanks!

[Mohammad8]

Hi Joel,

I created the Data Source and then created the Transform Map and I wanted to test my work before I create the Scheduled Job so I clicked on "Test Load 20 Records" and it ran successfully but it showed that it inserted 0 records. I also noticed the following error in the log

Import set not created, invalid target table: "my_table_name"

[Mohammad8]

Hi Joel,

I'm sorry for disturbing you and for sending too many questions. I tried to follow what you suggested but it didn't work. I managed to fix the previous error which I posted about in an earlier reply but still it didn't work. Here' exactly what I did:

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.