For one of our clients we are trying to extract data from a somewhat old fashioned REST API which returns an XML response. We have already discovered that the REST API connector cannot be used for this purpose as it can only handle JSON responses. We therefore switched to the HTTP connector instead which is giving back data, which we were then able to write to an Azure SQL DB sink. The strange thing is that the ADF output is always in JSON, despite the API response being XML (confirmed in Postman).
At first we didn't think this was a problem but I just discovered that array handling is not being done correctly. If multiple records are returned everything is fine, but when only a single record is returned by the API the square brackets around the array are removed and ADF no longer recognizes it correctly in the mapping because it expects an array. As a result the single record is not written to the SQL database.
We have tried using the Content-Type: application/xml and Accept: application/xml header to force an XML output, but that also didn't work. As the API really returns XML, it seems that ADF is converting this to JSON on its own accord and not doing it correctly. Is there any way that we can handle an XML API in ADF using standard connectors? We don't have programmers on our team who can create custom connectors so that's not an option. Any insights would be greatly appreciated.