Hi, how can I achieve this workflow?
- user clicks on task
- user selects a .xml file as parameter
- user executes the task
- write logic behind the task which inserts some data into tables based on the information given from the .xml file
(used for Web GUI)
If not so clear let me know. Thanks!
Best answer by kensonlatchmansingView original
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 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
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.
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 .data is a compressed (gzip) file format.
I hope this helps.
Ohja, thanks! 😅 decompress is indeed not needed
Has your question been resolved? 😄
Sorry, but I haven’t had the chance to try it out yet.
I’ve tried but without success unfortunately.
Here’s what I’ve got.
- process variable (pv): file_data = task param file_data [output]
- use pv file_data as input in “Read disk file” (see img. 2)
- set “Read disk file” [output] to pv file_data (see img. 3)
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!
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:
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)
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.
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.