How to insert multiple rows into database

[mikep35030180]

I have a form with a repeatable section for Other Names (user entered).  I want to take the entries and write to a SQL database.

Using AEM 6.2 Workbench, in my Execute SQL Statement in Transaction service operation, if I hard-code for each row occurrence it would look like this:

insert into Other_Names
(Process_ID, FirstName, MiddleName, LastName, NickName)
values
(
'{$ /process_data/@id $}',
'{$ /process_data/formXml/form1/Page1/Other_Names[1]/Other_Name/Other_FirstName $}',
'{$ /process_data/formXml/form1/Page1/Other_Names[1]/Other_Name/Other_MiddleName $}',
'{$ /process_data/formXml/form1/Page1/Other_Names[1]/Other_Name/Other_LastName $}',
'{$ /process_data/formXml/form1/Page1/Other_Names[1]/Other_Name/Other_Nickname $}'
),
(
'{$ /process_data/@id $}',
'{$ /process_data/formXml/form1/Page1/Other_Names[2]/Other_Name/Other_FirstName $}',
'{$ /process_data/formXml/form1/Page1/Other_Names[2]/Other_Name/Other_MiddleName $}',
'{$ /process_data/formXml/form1/Page1/Other_Names[2]/Other_Name/Other_LastName $}',
'{$ /process_data/formXml/form1/Page1/Other_Names[2]/Other_Name/Other_Nickname $}'
),
(
'{$ /process_data/@id $}',
'{$ /process_data/formXml/form1/Page1/Other_Names[3]/Other_Name/Other_FirstName $}',
'{$ /process_data/formXml/form1/Page1/Other_Names[3]/Other_Name/Other_MiddleName $}',
'{$ /process_data/formXml/form1/Page1/Other_Names[3]/Other_Name/Other_LastName $}',
'{$ /process_data/formXml/form1/Page1/Other_Names[3]/Other_Name/Other_Nickname $}'
)

How could I accomplish this without knowing the number of row occurrences?  If I must create a loop to write each row separately, is there a Workbench variable or function that will tell me the number of occurrences of a repeating field?

[Tankadus]

I am not to this point yet, but I do need to write to a SQL DB. Did you figure out the best way to do this and loop it? I assume you have to loop it and give it a sequence number... Maybe each row created in the form could auto-append a sequence number in the background.

[mikep35030180]

Yes, and you're exactly right.  I had to create a loop.  I initially get the number of occurrences for a repeating field in a SetValue service like ...

Location: /process_data/@TestCnt

Expression: count(/process_data/formXml/form1/Page1/MainSubform/TestSubform)

Then in the loop of the workflow I have a JdbcService with a SQL statement like ...

insert into TestTable

(Process_ID, Name, Street, City, State, Zip)

values

(

'{$ /process_data/@id $}',

'{$ /process_data/formXml/form1/Page1//TestSubform[number(/process_data/@TestCnt)]/Name $}',

'{$ /process_data/formXml/form1/Page1//TestSubform[number(/process_data/@TestCnt)]/Street $}',

'{$ /process_data/formXml/form1/Page1//TestSubform[number(/process_data/@TestCnt)]/City $}',

'{$ /process_data/formXml/form1/Page1//TestSubform[number(/process_data/@TestCnt)]/State $}',

'{$ /process_data/formXml/form1/Page1//TestSubform[number(/process_data/@TestCnt)]/Zip $}'

)

And note the use of the 'number' function for datatype conversion.