Transfer XML API response as file to FTP with SSJS

Question

Context: I am looking to implement a daily automated import of a product catalog. I can only access the catalog via an API endpoint that cannot be filtered/paginated and gives ±100K rows of XML as response. Given the size of the XML and the lack of native XML handling in SSJS I'm suspecting using Eliot Harper's solution is going to cause timeouts in my SSJS activity. I've previously worked with a data extract "convert XML" activity that worked great with large volumes. In this case, the XML structure is also really simple, max 2 levels deep. So my idea is, do the API call to the product feed with an SSJS activity and store the output as a file on the sFTP. Then use data extract to convert the XML to csv and then use an import to import to a DE.

My question now is, how can I place the XML output I get via the API as a file on the FTP? I found some code snippets that imply it's possible using new FileTransfer() but I can't find any documentation on this Class at all. Any help is greatly appreciated!

Answer 1

I'm not sure if the array/BuildRowSetFromXML/BuildRowSetFromString to InsertData is performant enough to handle 100k+ lines, but if so, you could try and insert one xml line as one row to a DE. If you upload it to a DE with one column, you will not get any unnecessary commas in the file after you data extract + file transfer to SFTP.

Answer 2

Talked to Adam Spriggs via Slack, according to him it cannot be done -

You can't interact with the SFTP via API -- it's hosted in an Akamai CDN.

Update with alternative solution found by running AMPscript content block in SSJS activity.

Script:

<script runat="server">

Platform.Load("Core", "1.1.1");

try {

    var url = 'https://www.exmaple.nl/xxx';
    var headerNames = ["Authorization"];
    var headerValues = ["Basic xxxxxxxx"];
    var response = HTTP.Get(url, headerNames, headerValues);

    Variable.SetValue("@xml", response.Content);
    var ampscriptCode = Platform.Function.ContentBlockByKey("process_XML"); 

 } catch (e) {
    Write(Stringify(e));
 }
</script>

AMPscript block:

SET @idrows = BuildRowsetFromXml(@xml, "/Models/Model/Id", 1)
SET @makerows = BuildRowsetFromXml(@xml, "/Models/Model/Make", 1)
SET @modelrows = BuildRowsetFromXml(@xml, "/Models/Model/Model", 1)
then loop through one of them (either one) and grab the values
SET @rowcount = RowCount(@idrows)
FOR @i = 1 TO @rowcount DO
    SET @id = Field(Row(@idrows, @i), "Value")
    SET @make = Field(Row(@makerows, @i), "Value")
    SET @model = Field(Row(@modelrows, @i), "Value")
    UpsertDE('Retention_ProductCatalog', 3, 'Id', @Id, 'Make', @Make, 'Model', @Model)
NEXT @i

I checked the result and the order of the data is exactly the same for each rowset so it seems to keep that consistent based on the input. Bit of a hack but it’s superfast, script runs in less than a minute rather than timing out after 30 min.