Solved

Add records from XML file

  • 14 September 2022
  • 13 replies
  • 156 views

Userlevel 1
Badge +4

Hi, how can I achieve this workflow?

  1. user clicks on task
  2. user selects a .xml file as parameter
  3. user executes the task
  4. 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!

icon

Best answer by kensonlatchmansing 8 November 2022, 12:01

View original

This topic has been closed for comments

13 replies

Userlevel 7
Badge +23

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:
 

File upload field
Output assignments of the Read disk file

 

 

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!

Userlevel 1
Badge +4

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

Userlevel 6
Badge +4

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.

Userlevel 7
Badge +23

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 .data is a compressed (gzip) file format.

I hope this helps.

Ohja, thanks! 😅 decompress is indeed not needed

Userlevel 7
Badge +23

Hey @kensonlatchmansing,

Has your question been resolved? 😄

Userlevel 1
Badge +4

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

Userlevel 1
Badge +4

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
    domain is just a database file upload
  2. a process flow which is triggered when the task is executed (design img. 1)
    - 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)
    img. 1
    img. 2
    img. 3
  3. a Task control procedure for debug purposes
    img. 4 (line 18 can be ignored as I do not not that)

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!

Userlevel 6
Badge +4

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.

Userlevel 1
Badge +4

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)
    [tabl].[tabl_blob] is a column that accepts VARBINARY(MAX)

     

  • now when running a query to see what is saved I get the following:
    the first row is from tabl_id = 2 

 

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

Userlevel 7
Badge +23

I'm unsure what is happening here:

Code

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?

Userlevel 1
Badge +4

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)

 

Userlevel 7
Badge +23

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:

Attachment (XML file) uploaded, input for varbinary(max) variable @file_data

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

 

 

Userlevel 1
Badge +4

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, 0

Thanks guys!