Hi,

In the Software Factory, we have an example of how you could implement this. When importing a project version, by selecting a model.dat file, that file is linked in the Task. This way you can utilize the Read disk file process action* to Read the file and store it inside a varchar(max) Edit, not varbinary(max) variable. EDIT: Nope… see reply This you then can decompress() and process. 

*Note that this method only works for Windows and Web GUI, and not for Universal GUI.

For context:
 

For Universal GUI

In case the file cannot be reached with the Read disk file, the newer alternative is to upload the file instead of linking it. With the upload, you'll have both the File name and the File data. Thereafter you can decompress and process the file data the same way as mentioned above. We'll need to change this too for the Software Factory as we work towards compatibility with Universal GUI. 

Hope this helps!

Allright, I’ll try this out and get back to you. Thank you

Hello kensonlatchmansing,

To add to Mark’s answer and remove some potential confusion, you will not need to call the decompress function for XML files, because they are not compressed. The task that imports project versions in the Software Factory calls the decompress function because .dat is a compressed (gzip) file format.

I hope this helps.

Ohja, thanks!  decompress is indeed not needed

Hey @kensonlatchmansing,

Has your question been resolved? 

Sorry, but I haven’t had the chance to try it out yet.

Hey @Mark Jongeling,

I’ve tried but without success unfortunately.
Here’s what I’ve got.

1. a task where the file would need to be selected

   

2. a process flow which is triggered when the task is executed (design img. 1)
   - process variable (pv): file_data = task param file_data eoutput]
   - use pv file_data as input in “Read disk file” (see img. 2)
   - set “Read disk file” doutput] to pv file_data (see img. 3)

   

   

   

3. a Task control procedure for debug purposes

   

What do I expect?
Well with this task I want the use to select a file which has a specific XML structure so I can use the XML data to run some SQL queries (i.e.: INSERT INTO ...) For now, I expect at least one company name to return in a message (img. 4).

But the message I get is just empty. If I remove the query on line 35 and use some dummy text instead, I do see that dummy text, which means that the select query does not return anything.

Also, when viewing the process flow monitor I get the following:

Please help, Thanks!

Hello @kensonlatchmansing,

I’m not sure what the Read disk file process action is achieving in your example. The Read disk file connector is used to read a file from disk given a file path and then output the file data. However, you already have the file data available in your task. Furthermore, you are supplying the Read disk file connector with the file data instead of a file path, this is why it is returning the -2 status code.

It appears to me that you don’t even need a process flow, just the task by itself would suffice. Inside of the task stored procedure, you can convert the varbinary file data to XML.

Depending on the encoding of the source file, you might have to perform a few additional steps in order to convert to XML. If the encoding is UTF-16, I expect that this will work right away. If the encoding is UTF-8, you might have to do an intermediate conversion to varchar using a UTF-8 encoding. More information on that can be found here:

I hope this helps.

After trying this, I still can’t seem to get it to work.

Here’s what I’ve got:

• this is my code behind my task (removed the process flow)

  

   

• now when running a query to see what is saved I get the following:

  

Also, the message still return anything. Am I still doing something wrong?

I'm unsure what is happening here:

Col is a varchar(max), but you insert @file_data as nvarchar(max) into col, then casting it to a varbinary(200) and placing it into an XML data type? 

Can you elaborate on the used data types and conversions?

Well, based on the answer of Vincent about encoding, I use the answer given. There N’this_is_some_string’  converts to string to nvarchar instead of varchar, so I convert my variable to nvarchar for the insert query. 

I converted back to varbinary so I could use the variable @xml_data as actual xml instead of varchar (text)

Hi @kensonlatchmansing,

In case the uploaded file is a (valid) .xml, you can capture the file data in the file_data column. This is probably a varbinary(max), meaning the "convert(varchar(max), @file_data)” will be needed.

What comes out of this convert is already XML/Text, so the encoding is not per se necessary I think. Otherwise inserting this text into the temporary table to change the collation of the column is possible. The cast as varbinary(200) is not necessary.

Example:

Code:

declare @file_data varbinary(max) = (select work_attachment_name_data from work_attachment where work_id = 2254)
select @file_data 

select convert(varchar(max), @file_data)

drop table if exists #temp
create table #temp (col varchar(max) collate LATIN1_GENERAL_100_CI_AS_SC_UTF8)
insert into #temp select replace(convert(varchar(max), @file_data), N'', '')

select * from #temp

It worked!

I changed the file_data task parameter to varbinary(max). And with the code below, I am able to continue my logic with the xml data saved in @xml_table. 

drop table if exists #temp
create table #temp (col varchar(max) collate LATIN1_GENERAL_100_CI_AS_SC_UTF8)
insert into #temp @file_data

declare @xml_data xml = (select convert(xml, col, 2) from #temp)

declare @xml_table table (
    name nvarchar(200)
    , searchcode nvarchar(50)
)

insert into @xml_table (
    name
    , searchcode
) select 
    row.value('./namen1]', 'nvarchar(200)')
    ,row.value('./searchcodec1]', 'nvarchar(50)')
    from @xml_data.nodes('/accounts/account') as c(row)

-- testing purposes only
declare @message nvarchar(max)
set @message = concat('<text>', (select top 1 name from @xml_table), '</text>')

exec tsf_send_message 'warning_msg', @message, 0

Thanks guys!