SOAPUI PRO - How to dynamically create soap request by adding collection objects based on the Data available in Database

Question

I have an Order Request which contains Item Collections - shown below

Each Item is saved as a single record in the database table which are bound together by OrderId for a specific Order

Request with two items

<orderRequest>         
            <orderTimestamp>                      
            <!--1 or more repetitions:-->
             <items>
               <item>
                  </itemName>
                  </quantity>
                  </unitPrice>  
               </item>
               <item>
                  </itemName>
                  </quantity>
                  </unitPrice>     
               </item>
            </items>         
      </orderRequest>     

I am using a DataSource step to pull the data from Database using a Query and have mapped the values from the query results onto the request elements.

However by doing this I am only able to add one item to the order request. Is there a way I can dynamically add objects to the item collection based on the number of rows being returned for a specific orderID?

Edit For Example purpose, I have taken sample values for two order Step 1: DataSource Step. Count in the first column defines the number of item for a given Order ID. Essentially the number of Item Collection to be added

Step 2: The SOAP Request Step - The elements value are directly mapped to the Datasource Step's column. However as indicated above, we would need to add the collection based on the number of items for a given order Ex: Order ID 1 has 2 items, Order ID 2 has 4 items. So two requests have to made, one with 2 item and second with 4 items. Currently though I have mapped it directly

<orderRequest>         
        <orderTimestamp>${OrderData#orderTimestamp}</orderTimestamp>                     
        <!--1 or more repetitions:-->
         <items>
           <item>
              <itemName>${OrderData#itemName}</itemName>
              <quantity>${OrderData#quantity}</quantity>
              <unitPrice>${OrderData#unitPrice}</unitPrice>
           </item>
        </items>         
  </orderRequest>

Step 3: DataSource Loop Step - This essentially loops through the above two steps for all data in the Datasource Step. So if I run as is, it will make 6 requests with one item each

Answer 1

Here is how I would achieve the same:

Following is the groovy script. This assumes with an arbitrary jdbc test step result set with list of items and build the dynamic xml snippet from jdbc result.

/**
* Below is the groovy script which builds data from jdbc result set 
* list of data into xml snippet
* and set xml snippet as test case property
**/
import groovy.xml.*
//For testing using the fixed jdbc result
def xml = '''<?xml version="1.0" encoding="utf-8"?>
<Results>
  <ResultSet fetchSize="100">
    <Row rowNumber="1">
      <ITEMNAME>item1</ITEMNAME>
      <QUANTITY>1</QUANTITY>
      <UNITPRICE>12</UNITPRICE>
    </Row>
    <Row rowNumber="2">
      <ITEMNAME>item2</ITEMNAME>
      <QUANTITY>1</QUANTITY>
      <UNITPRICE>120</UNITPRICE>
    </Row>
    <Row rowNumber="3">
      <ITEMNAME>item3</ITEMNAME>
      <QUANTITY>10</QUANTITY>
      <UNITPRICE>112</UNITPRICE>
    </Row>
  </ResultSet>  
</Results>'''
//if you want to pass the dynamic jdbc response instead of above fixed xml, then
//use below statements by uncommenting below one and replace value for JDBC_TEST_STEP_NAME and
//comment above xml statement
/**
def xml = context.expand('${JDBC_TEST_STEP_NAME#Response}')
**/

//parse the jdbc results
def results = new XmlSlurper().parseText(xml)
def writer = new StringWriter()
def userItems = new MarkupBuilder(writer)
//build items element
userItems.items {
  //loop thru each Row of the result set
  results.ResultSet.Row.each { row ->
    //building the item dynamically with data from jdbc result set
    item {
      //add value for elements
      itemName(row.ITEMNAME)
      quantity(row.QUANTITY)
      unitPrice(row.UNITPRICE)
    } 
​  }
}

log.info writer.toString()
//set xml snippet to the test case custom property
context.testCase.setPropertyValue('DYNAMIC_ITEMS', writer.toString())

By now, the dynamic built <items> will be part of test case level property DYNAMIC_ITEMS.

In your request, use property expansion i.e., where you need dynamic value list, just use as below:

<orderRequest>         
   <orderTimestamp>                      
   <!--1 or more repetitions:-->
   ${#TestCase#DYNAMIC_ITEMS}   
</orderRequest> 

When you hit the web service request, value will be replaced as you needed.

For quick test, the core part of the script is available here, so hit the link and see the result.

Hope this helps.