Dynamically compare Rest XML/JSON response and JDB

Question

In SoapUI, I have a JDBC Test Step that returns the following data:

<Results>
<ResultSet fetchSize="128">
    <Row rowNumber="1">
        <ID>1</ID>
        <NAME>TestName1</NAME>
        <DESCRIPTION/>
        <TYPE>Bool</TYPE>
        <ISPRODUCTTAG>true</ISPRODUCTTAG>
        <ISLOCATIONTAG>false</ISLOCATIONTAG>
        <SUBSECTION>Default Sub Section</SUBSECTION>
        <SECTION>Default Section</SECTION>
        <SUBGROUP>Default Sub Group</SUBGROUP>
        <GROUP>Default Group</GROUP>
    </Row>
    <Row rowNumber="2">
        <ID>2</ID>
        <NAME>TestName2</NAME>
        <DESCRIPTION/>
        <TYPE>Bool</TYPE>
        <ISPRODUCTTAG>true</ISPRODUCTTAG>
        <ISLOCATIONTAG>false</ISLOCATIONTAG>
        <SUBSECTION>Default Sub Section</SUBSECTION>
        <SECTION>Default Section</SECTION>
        <SUBGROUP>Default Sub Group</SUBGROUP>
        <GROUP>Default Group</GROUP>
    </Row>
    </Row>
</ResultSet>

I have an REST API XML Response that contains the following data:

    <ArrayOfTagInfo>
   <TagInfo id="1" name="TestName1" type="Bool" isProductTag="true" isLocationTag="false" subsection="Default Sub Section" section="Default Section" subgroup="Default Sub Group" group="Default Group"/>
   <TagInfo id="2" name="TestName2" type="Bool" isProductTag="true" isLocationTag="false" subsection="Default Sub Section" section="Default Section" subgroup="Default Sub Group" group="Default Group"/>
</ArrayOfTagInfo>

I would like to be able to compare(assert) both the Database Values and the Response Values (response can be in XML or JSON depending on the Request Accept Header) using groovy arrays if possible as the data returned from the database can be very large.

Can anyone help?

Answer 1

If you have SoapUI-Pro, you should be able to accomplish all this with no Groovy.

1. Make the REST call to retrieve all your data.
2. Start a DataSource step that parses the XML.
3. Make a JDBC call that select the correct ID of the row you want to verify. Make all the assertions in here.
4. Loop back to #2.

Answer 2

You can design your test case with the following test steps:

1. jdbc step
2. json step
3. groovy script step

The idea/sudo code here in groovy script step is that

• get the response of jdbc step
• get the response of json step
• build the data in the form of objects, so that it would be easy for comparison
• store the objects in list, one for jdbc and another one json
• sort both the lists, to make sure comparing the same data
• compare that both the list are same.

Here goes the groovy script:

/**
 * Model object for comparing
 */
@groovy.transform.Canonical
class Model {
    def id
    def name
    def type
    def isProductTag
    def isLocationTag
    def subSection
    def section
    def subGroup
    def group

    /**
     * this will acception jdbc row
     * @param row
     * @return
     */
    def buildJdbcData(row) {
        row.with {
            id = ID
            name = NAME
            type = TYPE
            isProductTag = ISPRODUCTTAG
            isLocationTag = ISLOCATIONTAG
            subSection = SUBSECTION
            section = SECTION
            subGroup = SUBGROUP
            group = GROUP
        }
    }

    /**
     * this will accept the json TagInfo
     * @param tagInfo
     * @return
     */
    def buildJsonData(tagInfo){
        id = tagInfo.@id
        name = tagInfo.@name
        type = tagInfo.@type
        isProductTag = tagInfo.@isProductTag
        isLocationTag = tagInfo.@isLocationTag
        subSection = tagInfo.@subsection
        section = tagInfo.@section
        subGroup = tagInfo.@subgroup
        group = tagInfo.@group
    }
}

/**
 * Creating the jdbcResponse from the response received, using fixed value for testing
 * If you want, you can assign the response received directly using below instead of current and make sure you replace the step name correctly
 * def jdbcResponse = context.expand('${JdbcStepName#Response}')
 */
def jdbcResponse = '''<Results>
<ResultSet fetchSize="128">
    <Row rowNumber="1">
        <ID>1</ID>
        <NAME>TestName1</NAME>
        <DESCRIPTION/>
        <TYPE>Bool</TYPE>
        <ISPRODUCTTAG>true</ISPRODUCTTAG>
        <ISLOCATIONTAG>false</ISLOCATIONTAG>
        <SUBSECTION>Default Sub Section</SUBSECTION>
        <SECTION>Default Section</SECTION>
        <SUBGROUP>Default Sub Group</SUBGROUP>
        <GROUP>Default Group</GROUP>
    </Row>
    <Row rowNumber="2">
        <ID>2</ID>
        <NAME>TestName2</NAME>
        <DESCRIPTION/>
        <TYPE>Bool</TYPE>
        <ISPRODUCTTAG>true</ISPRODUCTTAG>
        <ISLOCATIONTAG>false</ISLOCATIONTAG>
        <SUBSECTION>Default Sub Section</SUBSECTION>
        <SECTION>Default Section</SECTION>
        <SUBGROUP>Default Sub Group</SUBGROUP>
        <GROUP>Default Group</GROUP>
    </Row>
    <!--added 3rd row for testing the failure -->
    <Row rowNumber="3">
        <ID>3</ID>
        <NAME>TestName3</NAME>
        <DESCRIPTION/>
        <TYPE>Bool</TYPE>
        <ISPRODUCTTAG>false</ISPRODUCTTAG>
        <ISLOCATIONTAG>false</ISLOCATIONTAG>
        <SUBSECTION>Default Sub Section3</SUBSECTION>
        <SECTION>Default Section3</SECTION>
        <SUBGROUP>Default Sub Group3</SUBGROUP>
        <GROUP>Default Group3</GROUP>
    </Row>
</ResultSet>
</Results>'''

/**
 * Creating the jsonResponse from the response received, using fixed value for testing
 * If you want, you can assign the response received directly using below instead of current and make sure you replace the step name correctly
 * def jsonResponse = context.expand('${JsonStepName#Response}')

 */
def restResponse = '''
<ArrayOfTagInfo>
   <TagInfo id="1" name="TestName1" type="Bool" isProductTag="true" isLocationTag="false" subsection="Default Sub Section" section="Default Section" subgroup="Default Sub Group" group="Default Group"/>
   <TagInfo id="2" name="TestName2" type="Bool" isProductTag="true" isLocationTag="false" subsection="Default Sub Section" section="Default Section" subgroup="Default Sub Group" group="Default Group"/>
  <!--added 3rd row for testing the failure -->
   <TagInfo id="3" name="TestName3" type="Bool" isProductTag="true" isLocationTag="false" subsection="Default Sub Section" section="Default Section" subgroup="Default Sub Group" group="Default Group"/>
</ArrayOfTagInfo>'''

//Parsing the jdbc and build the jdbc model object list
def results = new XmlSlurper().parseText(jdbcResponse)
def jdbcDataObjects = []
results.ResultSet.Row.each { row ->
    jdbcDataObjects.add(new Model().buildJdbcData(row))
}

//Parsing the json and build the json model object list
def arrayOfTagInfo = new XmlSlurper().parseText(restResponse)
def jsonDataObjects = []
arrayOfTagInfo.TagInfo.each { tagInfo ->
    jsonDataObjects.add(new Model().buildJsonData(tagInfo))
}

//sorting the Data before checking for equality
jdbcDataObjects.sort()
jsonDataObjects.sort()

if (jdbcDataObjects.size() != jsonDataObjects.size()) {
    System.err.println("Jdbc resultset size is : ${jdbcDataObjects.size()} and Json result size is : ${jsonDataObjects.size()}")
}
assert jdbcDataObjects == jsonDataObjects, "Comparison of Jdbc and Json data is failed"

In the above, 3rd row is not matching, so assert will throw the following error:

Caught: java.lang.AssertionError: Comparison Failed. Expression: (jdbcDataObjects == jsonDataObjects). Values: jdbcDataObjects = [Default Group3, Default Group, Default Group], jsonDataObjects = [Default Group, Default Group, Default Group] java.lang.AssertionError: Comparison Failed. Expression: (jdbcDataObjects == jsonDataObjects). Values: jdbcDataObjects = [Default Group3, Default Group, Default Group], jsonDataObjects = [Default Group, Default Group, Default Group] at So31472381.run(So31472381.groovy:104)
Process finished with exit code 1

If you remove 3rd (from both responses), then you do not see any error, which is indication of successful comparison of jdbc and json responses.

Note that groovy script is available in both free and pro version of SoapUI. So this solution works for both the editions of SoapUI.