Hi, how can I achieve this workflow?
(used for Web GUI)
If not so clear let me know. Thanks!
Best answer by kensonlatchmansing
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('./name[1]', 'nvarchar(200)')
,row.value('./searchcode[1]', '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, 0Thanks guys!
+23Hi,
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.
+23Hello 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 .data is a compressed (gzip) file format.
I hope this helps.
Ohja, thanks! 😅 decompress is indeed not needed
Hey
I’ve tried but without success unfortunately.
Here’s what I’ve got.
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
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?
+23I'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)
+23In 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('./name[1]', 'nvarchar(200)')
,row.value('./searchcode[1]', '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, 0Thanks guys!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.