Syntax for SQL Query using Collection

  • 15 March 2017
  • 4 replies
  • 38 views
F
Badge +7

Hi all,

I am using this example as a starting point to build an Insert query from SP into SQL:

Copying SharePoint list items to sql database ‌

My input values, however, are in a collection. Is it possible to use the query to grab a value from a specific index in the collection? For instance, in the linked example:

INSERT INTO Table1

(Column1, column 2, column 3)

VALUES(

'{WorkflowVariable:Collection*Index0*}',

'{WorkflowVariable:Collection*Index1*}',

'{WorkflowVariable:Collection*Index2*}'

);

I know the syntax is wrong but just wondering how i could do this, if it's even possible.

4 replies

P
Badge +4

I do not think you can directly reference the collection values like you mentioned. here are a couple of ways I can think of do it.

1) You can use the "For Each" action to loop through each value in collection, store it in a variable, then use that in the insert action.

2) Use Collection Operation to extract the value at a specific index, store it in a variable, then use that in the insert action.

You might have to include either of the above in a LOOP action, until all "rows" in your collection are taken care of.

E
Userlevel 5
Badge +14

unfortunatelly it's not possible to reference collection element at given index position directly.

you will have to use 3 collection operation actions to GET values from collection and them to variables.

then use variables in SQL statement.

F
Badge +7

Thanks Marian and Prasanna!

Marian: That is what I did, which is fine for my proof of concept, but will be pretty unwieldy with my actual workflow which will require alot more than 3 variables.

Prasanna: #2 is what i did for my testing, but like i said above, doing this with 25 variables is not going to be a good plan. #1 I suppose could work but it would seem to mean that I would need to have the execute SQL action WITHIN each loop to pass the temporarily stored variable into SQL. I was really hoping to that only one operation executing at the end of my UDA to store all the data into the SQL data row.

E
Userlevel 5
Badge +14

so what about make it a task for SQL server to parse out single values?

you could create a stored procedure to which you hand over data collected in workflow and let it process/handle there.

you can hand over collection itself (semicolon separated list of values) if you have ensured collection contains  a value for every column you need.

or you can turn it to some other structure (XML, column-value pairs...) and hand it over that way.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings