[resolved] Mapping SOAP response to Mysql Output

[Anonymous]

Hello,
I am creating a job that consumes a webservice through a tSOAP component.
I am taking the output of this component in Document format.
And now I want to dump the SOAP body content into a Mysql database table using tMysqlOutput component.
So I have created a tXMLMap component .
The soap response returned by the webservice is as follows:
<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<GetXmlDataResponse xmlns="http://smartbear.com">
<GetXmlDataResult>
<x:books xmlns:x="urn:books">
<book xmlns="" id="bk001">
<author>Writer</author>
<title>The First Book</title>
<genre>Fiction</genre>
<price>44.95</price>
<pub_date>2000-10-01</pub_date>
<review>An amazing story of nothing.</review>
</book>
<book xmlns="" id="bk002">
<author>Poet</author>
<title>The Poet's First Poem</title>
<genre>Poem</genre>
<price>24.95</price>
<review>Least poetic poems.</review>
</book>
</x:books>
</GetXmlDataResult>
</GetXmlDataResponse>
</soap:Body>
</soap:Envelope>
However when I run the job, it gets the whole response as a single row.
But it does not dump any data into the database table because the element values are retrieved as null.
See TOSJobOutput.png attached.
Can anyone tell me the issues with mapping done?
Or point me to any other post or tutorial that explains how to map a SoapResponse document to individual database columns.

[Anonymous]

Hi,
Could you use tLogrow instead of tMysqlOutput to see the actual result? Is there still error "Column bookid cannot be null" in console?
Best regards
Sabrina

[Anonymous]

Yes, I have done that. The output (Soap response in italics) is also shown in the first post.

[janhess]

You need to map the xml to your schema in the db output.

[Anonymous]

I have mapped the SOAP response to tMysqlOutput component through a tXmlMap component. See the attached image in my first post.
The problem is that it is not looping through the two book tags to get the data but treats the whole response as a single row. I am not able to figure out how to make the mapping so that each book tag is treated as a single row so that it can be inserted into the mysql db.

[janhess]

Sorry, missed the tXmlMap. You need to loop on book not envelope.

[Anonymous]

I tried that too. I looped on book yet it shows only 1 row going into the tXmlMap and 0 rows coming out of it.

[Anonymous · Accepted solution]

Hi
The return result of web service is a string with XML format, you can use tExtractXMLField component to extract the value such as The First Book from the title node after tSOAP,
tSOAP--main--tExtactXMLField--main--tMysqlOutput
Shong

[Anonymous]

Thanks shong, tExtractXmlField works like a charm!!