Extract SOAP XML attribute value from datafile to provide JDBC request with query filter value

[richie]

Hi,

Its been years since I used SoapUI Pro (I was never that good with it to begin with) and I'm now using ReadyAPI! v1.9.0 and I'm struggling.

I have 80 test data files (XML) which will be used to populate the body of a Soap message being posted to a web service.  Within these XML files is an attribute value (RecordID) I need to source to populate the where clause of a query filter in a JDBC step.  I've followed the instructions that I've found and done various google searches, however, I'm still struggling so I'm hopeful someone can help!

My hierarchy is as follows:

Datasource step (Type=File, Property = FileContent, no separator, charset=UTF-8)
Soap step (body tag value = ${DataSource#FileContent})
Property Transfer step (Property=GetRecordID, Source=Datasource, Property=FileContent, PathLanguage=XPath, value=//*:RootElement/Element/@RecordID)
JDBC step (select * from table where RecordID='value I need from XML datafile')

I apologise if I'm asking a stupid question or for wasting anyone's time.

I do appreciate any and all guidance anyone can provide!

Cheers!

[Radford]

I assume that you are using a Directory Data Source to read each files contents in turn. and that data source has a single property called "contents", you can use a Groovy test step within your data source loop and the XmlSlurper to interogate your XML.

import groovy.util.XmlSlurper

// Get XML from data source property
//def xmlText = context.expand( '${DataSource#contents}' )

//Example XML just to demo XmlSlurper
def xmlText = '''<element1>
	<element2>
		<element3 RecordID='123'>fred</element3>
	</element2>
</element1>
'''

def xml = new XmlSlurper().parseText(xmlText)

log.info(xml.element2.element3.@RecordID)

My script above just logs the record ID, you could easily set a property of a JDBC test step.

More info on the XmlSlurper can be found here:

http://groovy-lang.org/processing-xml.html

[richie]

Hey Radford

Thanks for reply - I do appreciate it!

I edited the file as implied (see below) but I'm getting an org.xml.sax.SAXParseException; Premature end of file. error at line :14 response.

I'm not a coder - I spent a couple of hours trying to work out the problem but the google help is talking about instreams/outstreams and I just haven't got a clue!  The data files in the directory are all wellformed.

import groovy.util.XmlSlurper

// Get XML from data source property
def xmlText = context.expand( '${DataSource#contents}' )

//Example XML just to demo XmlSlurper
//def xmlText = '''<element1>
//	<element2>
//		<element3 RecordID='123'>fred</element3>
//	</element2>
//</element1>
//'''

def xml = new XmlSlurper().parseText(xmlText)

log.info(xml.element2.element3.@RecordID)

I'm totally lost!

Thanks again!

[nmrao]

@richie, would you mind showing the xml that you are working with? That would be to provide some help rather than showing showing some imaginary values?

[richie]

Hey Rao,

You're doing me the favour so more than happy to supply anything you need!  

please see below - the XML contained within my test data file is as below:

<RecordInput xsi:noNamespaceSchemaLocation="file:///D:/Folder/CSMRecord_v1_0_0.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RecordProfile RecordID="D8765" RequestedDateTime="20170412163030999">
<RecordName>Record NAME 20 CHARSS</RecordName>
<LocationName>LOCATION NAME OF Record 30 CHAR</LocationName>
<BuildingName>LOCATION NAME OF Record 30 CHAR</BuildingName>
<InternalLocation>XINTERNAL LOCATION WITH A LARGE 50 CHARACTER LIMIT</InternalLocation>
<ExternalLocation>XEXTERNAL LOCATION WITH A LARGE 50 CHARACTER LIMIT</ExternalLocation>
<Address Type="V">
<CompanyName>C NAME NOT FOR Record</CompanyName>
<Line>30 CHARACTER 1ST LINE -ADDRESS</Line>
<Line>30 CHARACTER 2ND LINE -ADDRESS</Line>
<Line>30 CHARACTER 3RD LINE -ADDRESS</Line>
<Line>30 CHARACTER 4TH LINE -ADDRESS</Line>
<Postcode>AB10 1XG</Postcode>
<Latitude>57.14416516</Latitude>
<Longitude>-2.114847768</Longitude>
</Address>
<Telno>07581455199</Telno>
<MaxBedNo>99</MaxBedNo>
<NGArea>NJ</NGArea>
<InUse>Y</InUse>
<RegionCode>RC</RegionCode>
<FixSite>N</FixSite>
<SectorCode>SC</SectorCode>
</RecordProfile>
</RecordInput>

The Soap request I am trying to post to the web service is as follows:

<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:CSMRecordServicesIntf-ICSMRecordServices">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:Venue soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <i_RecordXML xsi:type="xsd:string">${DataSource#FileContent}</i_RecordXML>
         <i_Database xsi:type="xsd:string">?</i_Database>
         <i_UserID xsi:type="xsd:string">?</i_UserID>
      </urn:Venue>
   </soapenv:Body>
</soapenv:Envelope>

As you can see - I've created a FileContent property in the Datasource step which I intend to populate the i_RecordXML tag in the post to the web service.

I think I've given you too much background - anyway.....the original point which I'm stuck on is that I want to scrape the content of the XML (in the datafile) for the RecordID attribute value current as 'D8765' in my XML datafile and then use this as the query in a where clause (select * from table where RecordID='D8765';) in a subsequent JDBC step (essentially I need to query the database once the post to the webservice to confirm if the database has been updated with the new record.

Thanks again for all/any guidance/advice etc. - I apologise if I've supplied too much info - I just want to make it nice and clear!

Cheers!

richie

[nmrao]

Thank you for the details. So, in essence do you need to know how to extract the value of RecordID?

[richie]

Yes!

Sorry - I do get quite wordy don't i? 🙂

My request to the webservice (if successful) should result in a new row being added to the database.  So after submitting the post request, essentially I need to extract the value of the RecordID attribute from the source data file and pass it to the JDBC Step where I will just have a simple query (select * from table where RecordID='RecordID value sourced from datafile';) to verify the row being added to the database.

So I was thinking my object hierarchy in my test case would be something like the following:

Datasource (specifying directory to source test data XML files from, Property= FileContent)

SOAP request (posts to web service - body of Soap request is the content of the test data XML files via the ${DataSource#FileContent})

Property Transfer (to pull RecordID from XML file in directory)

JDBC Step (select * from table where RecordID='value in testdata XML file')

I feel like I should apologise - I was a Java coder 15 years ago, but I've been testing since and I just cant remember anything to do with code anymore and this was why I got the licensed/Pro version of SoapUI so I wouldn't have to rely on groovy scripting!

Thanks again for any and all help!

[richie]

Sorry - I've read all that an thought I'd re-emphasise my problem.

I need to source the RecordID attribute value from the XML datafile and then pass that value to the JDBC step to query the database after I've posted to the web service.

I hope I have been clear.

Thanks again - I really appreciate all help you are giving me!

richie

[Radford]

A couple of points:

Firstly, I've updated my code to demo with your XML:

import groovy.util.XmlSlurper

// Get XML from data source property, you MUST update the line below to point to your datasource property.
// def xmlText = context.expand( '${DataSource#contents}' )

//Example XML just to demo XmlSlurper
def xmlText = '''<RecordInput xsi:noNamespaceSchemaLocation="file:///D:/Folder/CSMRecord_v1_0_0.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<RecordProfile RecordID="D8765" RequestedDateTime="20170412163030999">
		<RecordName>Record NAME 20 CHARSS</RecordName>
		<LocationName>LOCATION NAME OF Record 30 CHAR</LocationName>
		<BuildingName>LOCATION NAME OF Record 30 CHAR</BuildingName>
		<InternalLocation>XINTERNAL LOCATION WITH A LARGE 50 CHARACTER LIMIT</InternalLocation>
		<ExternalLocation>XEXTERNAL LOCATION WITH A LARGE 50 CHARACTER LIMIT</ExternalLocation>
		<Address Type="V">
			<CompanyName>C NAME NOT FOR Record</CompanyName>
			<Line>30 CHARACTER 1ST LINE -ADDRESS</Line>
			<Line>30 CHARACTER 2ND LINE -ADDRESS</Line>
			<Line>30 CHARACTER 3RD LINE -ADDRESS</Line>
			<Line>30 CHARACTER 4TH LINE -ADDRESS</Line>
			<Postcode>AB10 1XG</Postcode>
			<Latitude>57.14416516</Latitude>
			<Longitude>-2.114847768</Longitude>
		</Address>
		<Telno>07581455199</Telno>
		<MaxBedNo>99</MaxBedNo>
		<NGArea>NJ</NGArea>
		<InUse>Y</InUse>
		<RegionCode>RC</RegionCode>
		<FixSite>N</FixSite>
		<SectorCode>SC</SectorCode>
	</RecordProfile>
</RecordInput>'''

def RecordInput = new XmlSlurper().parseText(xmlText)

log.info(RecordInput.RecordProfile.@RecordID)

Secondly, the the "org.xml.sax.SAXParseException; Premature end of file" error is probably because you did not update the line:

def xmlText = context.expand( '${DataSource#contents}' )

to point to your actual datasource property. If you are unsure of the exact syntax to use, look and the point and click GetData functionality.

[nmrao · Accepted solution]

@richie,
Ok. Thank you for the clarification. Once you lost the touch with programming, difficult to remember suddenly unless one keeps practise it daily life. Of course, you should restart again, will be able to grasp it quickly.

Anyways, looks already @Radford replied to your point to get the requested info in his solution. Please try that one.