Geeta Geeta - 3 months ago 76
Groovy Question

how to extract database xml data in soapui

My data is saved in SQL Server 2014 in xml format, and in SOAPUI JDBC request it is coming in this format

<Results>
<ResultSet fetchSize="128">
<Row rowNumber="1">
<ConfigurationXML><![CDATA[<ROOT><Device>[{"Name":"AL","Profile":{"ID":1,"Height":240},"Index":-1}]</Device></ROOT>]]></ConfigurationXML>
</Row>
</ResultSet>
</Results>


I want to compare this data with my REST Json Response, But how can I fetch data from this CDATA

Rao Rao
Answer

The sample jdbc response provided by is little complex. Needs to be retried in multiple phases based on the nature of the data.

  • Response xml has CDATA
  • CDATA again has Xml
  • That xml has json

Please find the below groovy script to retried final json and retrieving its properties as shown below.

Script is done with appropriate comments in line.

import com.eviware.soapui.support.XmlHolder
import groovy.json.*
//Sample response
def response = '''<Results>
<ResultSet fetchSize="128">
<Row rowNumber="1">
  <ConfigurationXML><![CDATA[<ROOT><Device>[{"Name":"AL","Profile":{"ID":1,"Height":240},"Index":-1}]</Device></ROOT>]]></ConfigurationXML> 
</Row>
</ResultSet>
</Results>'''
//Create the xml holder object for the jdbc response
def holder = new XmlHolder(response)
//Get ConfigurationXML Node value using Xpath from holder object, which will retrive CDATA
def configurationXmlCdata =  holder.getNodeValue('//*:ConfigurationXML')
log.info "Configruation Xml Cdata: ${configurationXmlCdata}"
//There is again xml inside CDATA, so create xml holder object to retried json string
def cdataHolder = new XmlHolder(configurationXmlCdata)
def jsonString = cdataHolder.getNodeValue('//ROOT/Device')
log.info "Device Json data: ${jsonString}"
//Create Json slurper object if you need to access json
def jsonData = new JsonSlurper().parseText(jsonString)
log.info jsonData.Name
log.info jsonData.Index
log.info jsonData.Profile.ID

enter image description here