An 3rd party API gets us a JSON with a base64 encoded file which I can save in a varbinary field. Now this file contains specific text we need to extract, and this is possible by saving the file first to a temporary location, and then use process action Readfile to get the content in a process variable and then process it in a stored procedure.
Can this be done without the temp saving of the file? It feels like i’m using 90’s technique but I cannot find any suggestion in documentation or community for decoding the file on-the-fly.
Page 1 / 1
Hi Tejo,
The returned Content of the API you can capture inside a variable which then can be converted to an usable format within a process procedure. This would eliminate the need of writing the data to a file and then reading it again. Would that suffice?
hi Mark,
So there is no database equivalent of the ‘read-file’ action process action? I tried an approach with a process procedure, and some t-sql I found for encoding the base64 into a string:
SELECT
@zpl_file_code = dbo.decode_utf8_string(result)
FROM (
SELECT
CONVERT
(
VARCHAR(MAX), CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
) AS result
FROM
(
SELECT
file_content AS BASE64_COLUMN
FROM
shipment_document
WHERE
document_id=@document_id
) a
) x
This works in order to get the zpl code from the file, but when executing in the process action, it takes almost a minute to process:
Any idea’s for this?
Hi Tejo,
The Read file process action only reads the file data of the given File and places it in the file data output parameter. This usually is a variable with datatype varbinary(max).
In case you have saved the Base64 data you have received from the 3rd party API, you can indeed decode the Base64 string as you explained.
Example Base64 string conversion
This process shouldn't take too long to execute, although I haven't tried using large files. I suspect the called function dbo.decode_utf8_string may be the issue. Could you execute the query and show the Execution plan? (Ctrl+M to turn on before execution)
The Execution plan can be used to see where SQL Server uses the most resources to execute the query. This may show the spot that causes the performance issue.
For example, the Execution plans for the above queries are as follows:
but I cannot find any suggestion in documentation or community for decoding the file on-the-fly. 
