topic [resolved] Mapping SOAP response to Mysql Output in Talend Studio

[resolved] Mapping SOAP response to Mysql Output

Anonymous — Sat, 16 Nov 2024 11:46:37 GMT

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.

Re: [resolved] Mapping SOAP response to Mysql Output

Anonymous — Thu, 23 Jan 2014 08:39:52 GMT

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

Re: [resolved] Mapping SOAP response to Mysql Output

Anonymous — Thu, 23 Jan 2014 08:43:25 GMT

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

Re: [resolved] Mapping SOAP response to Mysql Output

janhess — Thu, 23 Jan 2014 08:56:43 GMT

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

Re: [resolved] Mapping SOAP response to Mysql Output

Anonymous — Thu, 23 Jan 2014 09:01:27 GMT

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.

Re: [resolved] Mapping SOAP response to Mysql Output

janhess — Thu, 23 Jan 2014 11:50:23 GMT

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

Re: [resolved] Mapping SOAP response to Mysql Output

Anonymous — Fri, 24 Jan 2014 05:10:49 GMT

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.

Re: [resolved] Mapping SOAP response to Mysql Output

Anonymous — Thu, 06 Feb 2014 04:39:32 GMT

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

Re: [resolved] Mapping SOAP response to Mysql Output

Anonymous — Fri, 07 Feb 2014 08:48:46 GMT

Thanks shong, tExtractXmlField works like a charm!!