recently I ventured into the field of REST APIs, putting together a workflow to retrieve data from public web services (PubChem in this specific case). The API works just fine, however, I encountered a problem I was not able to solve so far:
To reduce server load and traffic, I am using a Chunk Loop to feed a long list of chemical names to the PubChem server bit by bit. The table contains names for chemical substances that may result in a hit in PubChem, but not all of them will be recognized.
As long as there is a single hit (i.e. code 200 and data retrieved) in the chunk, everything works fine.
However, if there are only 404 returns for a certain chunk, the loop will fail, as the data type for the “body” column in the output of the GET Request will convert to BLOB instead of XML, and thus, the data types of the Body column are disparate.
Is there any way to force the GET Request to ALWAYS produce an output of type “XML” in Body? Alternatively, is there a functionality to post-hoc force the data type of the body column to XML, even for empty (missing) values?
Thank you in adcance for your help,
thank you again for your support and help - Ivan’s solution worked out for me, although I agree with you, it is very tedious - there should be an easier way to accomplish this.