Forum Discussion

jayrdi's avatar
jayrdi
Occasional Contributor
11 years ago

DataLoop for DataSink

I know that there is a DataLoop that can be used to iterate over elements in a DataSource, but how do I iterate over elements that I am extracting into a DataSink?

I have a SOAP Response containing 100 records and I need to extract different elements from each record and store the data in an Excel worksheet. Currently I am extracting the data with a Property Transfer and using a DataSink to save it to a spreadsheet. Below is an example of the code that takes the first author name in each record and transfers it to the DataSink (this is within the Source section of the Property Transfer window):

declare namespace ns2='http://woksearch.v3.wokmws.thomsonreuters.com';
declare namespace ns1='http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord';

<full_name>
{
for $id in //ns1:name[1]//ns1:full_name[1]
return string($id)
}
</full_name>


However, this takes ALL the names from every record as a list and stores them in a single cell in the Excel worksheet (similarly for the other data fields I'm extracting). How can I do the Property Transfer for the 1st record into the DataSink, then iterate back to do the Property Transfer for the 2nd record, and so on until I have iterated over all 100 records?

Thank you for your help!

John
  • SmartBear_Suppo's avatar
    SmartBear_Suppo
    SmartBear Alumni (Retired)
    Hello,

    Instead of using property transfer, use property expansion. In the datasink step create 1 property for each field that you want to copy from the Response. the Right click on the value field of each property, click on get data and point to the testStep>then>Response> and then on the data you want to move over the the dataSink. Do this for each property and you should be ok. Let me know if you have further questions

    dataSink article that explains the above:
    http://www.soapui.org/Data-Driven-Testi ... tstep.html

    More info on property expansion:
    http://www.soapui.org/Scripting-Propert ... nsion.html

    Regards,
    Temil
  • jayrdi's avatar
    jayrdi
    Occasional Contributor
    I have tried this and when you select the data using the 'Get Data' command in the Property field you can only select a field in one of the records. There are 100 records in the XML Response file and within each record lies data I want to extract (<title>, <full_name> etc.) but if I use the property expansion method you mentioned it only takes property fields from the last record and not the other 99. I am supposing that it takes the property from the DataSource but overwrites it each time which is why when completed there is only one value which is from the last record?
  • SmartBear_Suppo's avatar
    SmartBear_Suppo
    SmartBear Alumni (Retired)
    Can you please send me you full response, and let me know what fields you want to push into the datasink, so that I can further trouble shoot. Thanks.

    Regards,
    Temil
  • jayrdi's avatar
    jayrdi
    Occasional Contributor
    Hi Temil,

    Thanks for your help with this, can't figure out why it's not working. It would be a lot easier to explain if I could post screenshots but it always comes up with the error message 'Invalid'. I was trying to post a JPEG and then a PNG file for the screenshot but it wouldn't let me; do you know why this might be?

    Anyway, the XML document that makes up the Response from the SOAP Request looks like this (I've edited some parts out because otherwise it is extremely long) :

    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    <soap:Body>
    <ns2:retrieveByIdResponse xmlns:ns2="http://woksearch.v3.wokmws.thomsonreuters.com">
    <return>
    <queryId>2</queryId>
    <recordsFound>100</recordsFound>
    <recordsSearched>50601246</recordsSearched>
    <records><records xmlns="http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord">
    <REC r_id_disclaimer="ResearcherID data provided by Thomson Reuters"> <UID>WOS:000266862400001</UID><static_data><summary><EWUID><WUID coll_id="WOS"></WUID><edition value="WOS.SCI"></edition><names count="3"><name seq_no="1" addr_no="1" role="author" reprint="Y"><display_name>Buijs, Arjen E.</display_name><full_name>Buijs, Arjen E.</full_name> ... etc ... </REC>
    <REC> ... etc ... </REC>
    <REC> ... etc ... </REC>
    </records></records>
    </return>
    </ns2:retrieveByIdResponse>
    </soap:Body>
    </soap:Envelope>


    All the data I am extracting lies within these <REC> tags, so I am trying to extract fields like <full_name> from each <REC> and then pass it into an Excel spreadsheet.

    I have managed to display the data exactly as I want it by using a DataSource step just after this SOAP Request/Response which extracts all the necessary fields by using the DataSource as XML and then referencing the XPath of each in the Row and Column sections. When I click the green test button in this window, the Data Log displays all 100 records with a column for each field type. When I try to pass all this data into a DataSink however, it only copies over the first row 100 times into the Excel worksheet.

    For example, for the field <full_name> I have made a property called 'Author 1' in the DataSource, set to XML, Source Step is the SOAP Request and Source Property is the Response (the data listed in the code block above). In the Row XPath I then have:

    declare namespace ns2='http://woksearch.v3.wokmws.thomsonreuters.com';
    declare namespace ns1='http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord';
    //ns2:retrieveByIdResponse[1]/return[1]/records[1]/ns1:records[1]/ns1:REC


    In the Column XPaths I have:

    declare namespace ns1='http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord'; ns1:static_data/ns1:summary/ns1:names/ns1:name[1]/ns1:full_name/text()


    When I run the test in this window, this displays a column with the header 'Author 1' and then lists 100 different names. When I transfer this to a DataSink however, it only lists the first row 100 times in the worksheet.

    Thanks for your help!

    John
  • jayrdi's avatar
    jayrdi
    Occasional Contributor
    Hi Temil,

    I have fixed this now but thanks for your help. I tried the Property Expansion method earlier and it wouldn't work but I think that perhaps another Step may have been interfering as it is working now. I think I set up the Property Expansion but didn't disable the Property Transfer.

    Anwyay, it's functioning as it should now so thanks for your help!

    John