cancel
Showing results for 
Search instead for 
Did you mean: 

[TechCorner Challenge #4] How to Generate a Request Body Based on Data from Datasource

SOLVED
Highlighted
Community Manager

[TechCorner Challenge #4] How to Generate a Request Body Based on Data from Datasource

Hi everyone,

 

Thank you for your TechCorner input! It has been awesome an extremely helpful. I've got more challenges for you🙂

 

A request body can be dynamic, and it is not always possible to use the existing ReadyAPI functionality to create it. One use case example could be changing data before sending a request. This is when users need to come up with a custom script.


Your task for today is to write a script that will read data from an Excel file and generate a request body for the SOAP TestStep based on it. The number of rows in the Excel file is dynamic.

DifficultyStar_Gold.pngStar_Gold.pngStar_gray.png

 

Tips:

 

https://smartbear-cc.force.com/portal/KbArticleViewer?name=Example-of-using-third-party-Java-librari...

 

https://community.smartbear.com/t5/SoapUI-Open-Source/Dynamic-request-creation-with-Groovy-element-C...

 

Excel example:

 

Excel-example.png

 

 

 

 

 

 

 

 

Request body example:

 

 

 

 

 

 

 

<?xml version="1.0"?>
<soap:Envelope
xmlns:soap="http://www.w3.org/2003/05/soap-envelope/"
soap:encodingStyle="http://www.w3.org/2003/05/soap-encoding">
	<soap:Header>
		<m:Trans xmlns:m="https://www.w3schools.com/transaction/"
  soap:actor="https://www.w3schools.com/code/">234
		</m:Trans>
	</soap:Header>
	<soap:Body>
		<CATALOG>
			<CD>
				<TITLE>Empire Burlesque</TITLE>
				<ARTIST>Bob Dylan</ARTIST>
				<PRICE>10.90</PRICE>
			</CD>
			<CD>
				<TITLE>Hide your heart</TITLE>
				<ARTIST>Bonnie Tyler</ARTIST>
				<PRICE>9.90</PRICE>
			</CD>
			<CD>
				<TITLE>Greatest Hits</TITLE>
				<ARTIST>Dolly Parton</ARTIST>
				<PRICE>9.90</PRICE>
			</CD>
			<CD>
				<TITLE>Still got the blues</TITLE>
				<ARTIST>Gary Moore</ARTIST>
				<PRICE>10.20</PRICE>
			</CD>
		</CATALOG>
	</soap:Body>
</soap:Envelope>

 

 

 

 

 

 

 

 

Have fun when solving this!😊


Sonya Mihaljova
Community and Education Specialist

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today
Tags (1)
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Leader

Task: Your task for today is to write a script that will read data from an Excel file and generate a request body for the SOAP TestStep based on it. The number of rows in the Excel file is dynamic.

 

This is a solution created for [TechCorner Challenge #4]

 

Hi @sonya_m ,

 

Here is my code which will generate XML out of Data present in Excel file, not matter how many rows are there, it will append the XML according to number of Rows be dynamic.

 

Pre-requisite : Need to add Groovy Script and Properties Step inside Test Case

 

 

 

import java.io.*
import java.text.SimpleDateFormat;  
import java.util.Calendar;  
import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.ss.util.*
import groovy.xml.MarkupBuilder
import groovy.xml.StreamingMarkupBuilder

def fs = new FileInputStream("F:\\temp.xlsx")

Workbook wb = WorkbookFactory.create(fs);
def ws = wb.getSheet("Sheet1");
int r = ws.getLastRowNum();
testRunner.testCase.getTestStepByName("Properties").setPropertyValue("DataRowCount", r.toString());
for(def i = 1 ; i <= r ; i++ ){
	def row = ws.getRow(i)
	def headerRow = ws.getRow(0)
	def noOfCell = row.getLastCellNum();
	for (def j=0;j<noOfCell;j++){
		def headertext = ws.getRow(0).getCell(j).getStringCellValue()
		Cell cell = row.getCell(j)
		if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
			value = "";
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
			value = (cell.getBooleanValue());
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
			value = (cell.getNumericCellValue());
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_STRING){
			value = (cell.getStringCellValue());
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_ERROR){
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
			value = String.valueOf(cell.getStringCellValue())
		}
 		testRunner.testCase.getTestStepByName("Properties").setPropertyValue(headertext.toString()+""+i.toString(), value.toString())
	}
}

def start = '''<?xml version="1.0"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope/" soap:encodingStyle="http://www.w3.org/2003/05/soap-encoding">
	<soap:Header>
		<m:Trans xmlns:m="https://www.w3schools.com/transaction/" soap:actor="https://www.w3schools.com/code/">234
		</m:Trans>
	</soap:Header>
	<soap:Body>
	'''
def end = '''
</soap:Body>
</soap:Envelope>'''

def writer = new StringWriter()
def xml = new MarkupBuilder(writer) 

xml.CATALOG() {
	for(int i = 1 ; i <= r ; i++){		
		CD(){
			TITLE(context.expand('${Properties#Title'+i.toString()+'}'))
			ARTIST(context.expand('${Properties#Artist'+i.toString()+'}'))
			PRICE(context.expand('${Properties#Price'+i.toString()+'}'))
		}
	}
}

log.info start + writer + end

 

 

 


Click "Accept as Solution" if my answer has helped,
Remember to give "Kudos" 🙂 ↓↓↓↓↓



Thanks and Regards,
Himanshu Tayal

View solution in original post

Tags (1)
Highlighted
Community Hero

Task: Your task for today is to write a script that will read data from an Excel file and generate a request body for the SOAP TestStep based on it. The number of rows in the Excel file is dynamic.

 

This is a solution created for [TechCorner Challenge #4]

 

Script Features & Handle additional data validations and groovy way of building xml such as

- Does not require any additional test steps.

- Zero changes in the script, yet provide different output based on the input data, including file path (handled thru custom properties).

- Empty rows (shown in the picture)

- Empty cells (shown in the picture)

- User can choose the sheet name (default is Sheet1)

- User can optionally consider or ignore first row as data

- Handles errors such as File existence in the given path, Sheet name, if rows present or not before building xml. Throws error otherwise.

- Supports .xlsx (poi-3.8.0 & tested in SoapUI 5.4) format

- Uses StreamingMarkupBuilder for building xml (a native way to build xmls easily)

 

 

nmrao_1-1593497898636.png

 

 

Just add test case custom properties as mentioned below

DATA_FILE and add the file name, mandatory

SHEET_NAME and add name of the sheet in the excel file, optional

IGNORE_FIRST_ROW and add boolean value true or false; optional

 

nmrao_1-1593501400456.png

Here is the(groovified) script (and follow in-line comments)

Contents of Groovy script, i.e., CreateRequestFromDataSource test step.

 

 

 

 

import org.apache.poi.xssf.usermodel.XSSFWorkbook
import static org.apache.poi.ss.usermodel.Cell.*
import groovy.xml.StreamingMarkupBuilder
import groovy.xml.XmlUtil

//Used to create the data map from excel cells 
def columnMap = [0: 'title', 1: 'artist', 2: 'price']

//Validate if the data file exists and only supports .xlsx files as input
def isFileValid = { 	
	assert null != it, 'File path is empty'
	def xFile = new File(it)
	assert xFile.exists(), "${it} file doesn't exists"
	assert it.endsWith('.xlsx'), 'Only allows file extension .xlsx'
	true
}

// A closure that creates data (list of rows; and each row as map) from input file
// User can choose the sheet name (default is Sheet 1), throws if sheet does not exists
// And whether to ignore first row or not
// Also handles empty rows and empty cells
// Support apache-poi 3.8 and tested in SoapUI 5.4
def getData = { filepath, isIgnoreFirstRow = true, sheetName = 'Sheet1' ->
	def data = [] 
	if (isFileValid(filepath)) {
		def sheet = new XSSFWorkbook(filepath).getSheet sheetName
		assert null != sheet, "${sheetName} does not existing in the ${filepath}"		
		sheet.rowIterator().eachWithIndex { row, rowId ->
			def map = [:]
			if (!isIgnoreFirstRow || rowId != 0) {
				for (index = 0; index <row.lastCellNum;index++){
					cell = row.getCell(index, row.CREATE_NULL_AS_BLANK);
					switch (cell.cellType) {
            				case CELL_TYPE_STRING: map.put(columnMap[index], cell.stringCellValue); break
            				case CELL_TYPE_NUMERIC: map.put(columnMap[index], cell.numericCellValue); break
            				case CELL_TYPE_BOOLEAN: map.put(columnMap[index], cell.booleanCellValue); break
            				case CELL_TYPE_FORMULA: map.put(columnMap[index], cell.getCachedFormulaResultType()); break
            				default: map.put(columnMap[index], ''); break
					}
        			}
        			data << map
			}				
		}
	}	
	assert data.size(), 'No data created from file'
	data
}

//A closure that builds the xml request based on the user input
def createRequest = { cds, trans ->
	def nameSpacesMap = [
            soap: 'http://schemas.xmlsoap.org/soap/envelope/',
            m: 'https://www.w3schools.com/transaction/'
    ]
    def builder = new StreamingMarkupBuilder()
    builder.encoding ='utf-8'
    def soapRequest = builder.bind {
        namespaces << nameSpacesMap
        soap.Envelope {
            soap.Header {
            	m.Trans('soap:actor': 'https://www.w3schools.com/code/', trans)
            }
            soap.Body {
               CATALOG {
               	cds.each  { cd ->
               		CD {
               			TITLE cd.title
               			ARTIST cd.artist
               			PRICE cd.price
               		}
               	}
               }
            }
        }
    }
    XmlUtil.serialize soapRequest 
}

//Actuall flow of the script starts here
def file = context.testCase.getPropertyValue('DATA_FILE')
def sheet =  context.testCase.getPropertyValue('SHEET_NAME') ?: 'Sheet1'
def ignoreFirstRow = context.testCase.getPropertyValue('IGNORE_FIRST_ROW')?.toBoolean() ?: true

//Call to the closures
log.info createRequest(getData(file, ignoreFirstRow, sheet), 234)

 

 

 

 

ADD-ON

Realized that usually, user not only wants to create / build the SOAP request, but also like to update the request in the following SOAP Request content in the test case.

Let us assume that the test case has Request step, say MyRequest  step in which user wants to update the content dynamically from the data source file using groovy script. You can see the steps in the test case from the pictures above.

 

So in order to complete the use case, needed to append below script at the end of the above in order to achieve the same.

 

Here user can add an additional test case custom property for Request step name (default is SOAP Request), say REQUEST_STEP_NAME and value as you needed, MyRequest in this case as per picture.

 

 

 

 

//Call to the closures
def request = createRequest(getData(file, ignoreFirstRow, sheet), 234)

//Get the next test step request
def nextStepName = context.testCase.getPropertyValue('REQUEST_STEP_NAME') ?: 'SOAP Request'
//Get the next test step request
def nextStepRequest = context.testCase.testSteps[nextStepName].httpRequest
//Update the request details in the next request test step
nextStepRequest.requestContent = request

 

 

 

 

Hope this ADD-ON adds value.



Regards,
Rao.

View solution in original post

Tags (1)
3 REPLIES 3
Highlighted
Community Leader

Task: Your task for today is to write a script that will read data from an Excel file and generate a request body for the SOAP TestStep based on it. The number of rows in the Excel file is dynamic.

 

This is a solution created for [TechCorner Challenge #4]

 

Hi @sonya_m ,

 

Here is my code which will generate XML out of Data present in Excel file, not matter how many rows are there, it will append the XML according to number of Rows be dynamic.

 

Pre-requisite : Need to add Groovy Script and Properties Step inside Test Case

 

 

 

import java.io.*
import java.text.SimpleDateFormat;  
import java.util.Calendar;  
import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.ss.util.*
import groovy.xml.MarkupBuilder
import groovy.xml.StreamingMarkupBuilder

def fs = new FileInputStream("F:\\temp.xlsx")

Workbook wb = WorkbookFactory.create(fs);
def ws = wb.getSheet("Sheet1");
int r = ws.getLastRowNum();
testRunner.testCase.getTestStepByName("Properties").setPropertyValue("DataRowCount", r.toString());
for(def i = 1 ; i <= r ; i++ ){
	def row = ws.getRow(i)
	def headerRow = ws.getRow(0)
	def noOfCell = row.getLastCellNum();
	for (def j=0;j<noOfCell;j++){
		def headertext = ws.getRow(0).getCell(j).getStringCellValue()
		Cell cell = row.getCell(j)
		if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
			value = "";
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
			value = (cell.getBooleanValue());
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
			value = (cell.getNumericCellValue());
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_STRING){
			value = (cell.getStringCellValue());
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_ERROR){
		}
		else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
			value = String.valueOf(cell.getStringCellValue())
		}
 		testRunner.testCase.getTestStepByName("Properties").setPropertyValue(headertext.toString()+""+i.toString(), value.toString())
	}
}

def start = '''<?xml version="1.0"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope/" soap:encodingStyle="http://www.w3.org/2003/05/soap-encoding">
	<soap:Header>
		<m:Trans xmlns:m="https://www.w3schools.com/transaction/" soap:actor="https://www.w3schools.com/code/">234
		</m:Trans>
	</soap:Header>
	<soap:Body>
	'''
def end = '''
</soap:Body>
</soap:Envelope>'''

def writer = new StringWriter()
def xml = new MarkupBuilder(writer) 

xml.CATALOG() {
	for(int i = 1 ; i <= r ; i++){		
		CD(){
			TITLE(context.expand('${Properties#Title'+i.toString()+'}'))
			ARTIST(context.expand('${Properties#Artist'+i.toString()+'}'))
			PRICE(context.expand('${Properties#Price'+i.toString()+'}'))
		}
	}
}

log.info start + writer + end

 

 

 


Click "Accept as Solution" if my answer has helped,
Remember to give "Kudos" 🙂 ↓↓↓↓↓



Thanks and Regards,
Himanshu Tayal

View solution in original post

Tags (1)
Highlighted
Community Hero

Task: Your task for today is to write a script that will read data from an Excel file and generate a request body for the SOAP TestStep based on it. The number of rows in the Excel file is dynamic.

 

This is a solution created for [TechCorner Challenge #4]

 

Script Features & Handle additional data validations and groovy way of building xml such as

- Does not require any additional test steps.

- Zero changes in the script, yet provide different output based on the input data, including file path (handled thru custom properties).

- Empty rows (shown in the picture)

- Empty cells (shown in the picture)

- User can choose the sheet name (default is Sheet1)

- User can optionally consider or ignore first row as data

- Handles errors such as File existence in the given path, Sheet name, if rows present or not before building xml. Throws error otherwise.

- Supports .xlsx (poi-3.8.0 & tested in SoapUI 5.4) format

- Uses StreamingMarkupBuilder for building xml (a native way to build xmls easily)

 

 

nmrao_1-1593497898636.png

 

 

Just add test case custom properties as mentioned below

DATA_FILE and add the file name, mandatory

SHEET_NAME and add name of the sheet in the excel file, optional

IGNORE_FIRST_ROW and add boolean value true or false; optional

 

nmrao_1-1593501400456.png

Here is the(groovified) script (and follow in-line comments)

Contents of Groovy script, i.e., CreateRequestFromDataSource test step.

 

 

 

 

import org.apache.poi.xssf.usermodel.XSSFWorkbook
import static org.apache.poi.ss.usermodel.Cell.*
import groovy.xml.StreamingMarkupBuilder
import groovy.xml.XmlUtil

//Used to create the data map from excel cells 
def columnMap = [0: 'title', 1: 'artist', 2: 'price']

//Validate if the data file exists and only supports .xlsx files as input
def isFileValid = { 	
	assert null != it, 'File path is empty'
	def xFile = new File(it)
	assert xFile.exists(), "${it} file doesn't exists"
	assert it.endsWith('.xlsx'), 'Only allows file extension .xlsx'
	true
}

// A closure that creates data (list of rows; and each row as map) from input file
// User can choose the sheet name (default is Sheet 1), throws if sheet does not exists
// And whether to ignore first row or not
// Also handles empty rows and empty cells
// Support apache-poi 3.8 and tested in SoapUI 5.4
def getData = { filepath, isIgnoreFirstRow = true, sheetName = 'Sheet1' ->
	def data = [] 
	if (isFileValid(filepath)) {
		def sheet = new XSSFWorkbook(filepath).getSheet sheetName
		assert null != sheet, "${sheetName} does not existing in the ${filepath}"		
		sheet.rowIterator().eachWithIndex { row, rowId ->
			def map = [:]
			if (!isIgnoreFirstRow || rowId != 0) {
				for (index = 0; index <row.lastCellNum;index++){
					cell = row.getCell(index, row.CREATE_NULL_AS_BLANK);
					switch (cell.cellType) {
            				case CELL_TYPE_STRING: map.put(columnMap[index], cell.stringCellValue); break
            				case CELL_TYPE_NUMERIC: map.put(columnMap[index], cell.numericCellValue); break
            				case CELL_TYPE_BOOLEAN: map.put(columnMap[index], cell.booleanCellValue); break
            				case CELL_TYPE_FORMULA: map.put(columnMap[index], cell.getCachedFormulaResultType()); break
            				default: map.put(columnMap[index], ''); break
					}
        			}
        			data << map
			}				
		}
	}	
	assert data.size(), 'No data created from file'
	data
}

//A closure that builds the xml request based on the user input
def createRequest = { cds, trans ->
	def nameSpacesMap = [
            soap: 'http://schemas.xmlsoap.org/soap/envelope/',
            m: 'https://www.w3schools.com/transaction/'
    ]
    def builder = new StreamingMarkupBuilder()
    builder.encoding ='utf-8'
    def soapRequest = builder.bind {
        namespaces << nameSpacesMap
        soap.Envelope {
            soap.Header {
            	m.Trans('soap:actor': 'https://www.w3schools.com/code/', trans)
            }
            soap.Body {
               CATALOG {
               	cds.each  { cd ->
               		CD {
               			TITLE cd.title
               			ARTIST cd.artist
               			PRICE cd.price
               		}
               	}
               }
            }
        }
    }
    XmlUtil.serialize soapRequest 
}

//Actuall flow of the script starts here
def file = context.testCase.getPropertyValue('DATA_FILE')
def sheet =  context.testCase.getPropertyValue('SHEET_NAME') ?: 'Sheet1'
def ignoreFirstRow = context.testCase.getPropertyValue('IGNORE_FIRST_ROW')?.toBoolean() ?: true

//Call to the closures
log.info createRequest(getData(file, ignoreFirstRow, sheet), 234)

 

 

 

 

ADD-ON

Realized that usually, user not only wants to create / build the SOAP request, but also like to update the request in the following SOAP Request content in the test case.

Let us assume that the test case has Request step, say MyRequest  step in which user wants to update the content dynamically from the data source file using groovy script. You can see the steps in the test case from the pictures above.

 

So in order to complete the use case, needed to append below script at the end of the above in order to achieve the same.

 

Here user can add an additional test case custom property for Request step name (default is SOAP Request), say REQUEST_STEP_NAME and value as you needed, MyRequest in this case as per picture.

 

 

 

 

//Call to the closures
def request = createRequest(getData(file, ignoreFirstRow, sheet), 234)

//Get the next test step request
def nextStepName = context.testCase.getPropertyValue('REQUEST_STEP_NAME') ?: 'SOAP Request'
//Get the next test step request
def nextStepRequest = context.testCase.testSteps[nextStepName].httpRequest
//Update the request details in the next request test step
nextStepRequest.requestContent = request

 

 

 

 

Hope this ADD-ON adds value.



Regards,
Rao.

View solution in original post

Tags (1)
Highlighted
Community Manager

Rao, Himanshu, thank you for the job well-done!


Sonya Mihaljova
Community and Education Specialist

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today
New Here?
Join us and watch the welcome video:
Announcements
TechCorner Leaderboard
Want a fun and easy way to learn ReadyAPI and show off your skills? Try solving weekly TechCorner challenges and get into the Leaderboard!


Challenge Status

Get a full Raw JMS request

Participate!

Compare an expected JSON value and actual response in Events

Participate!

Fetch value/data from JSON response using Groovy Script

See replies!

Get data from Petstore and add it to Excel sheets

Participate!
Top Kudoed Authors